Excel 2010 Macro For Each Loop
The For Each...Loop uses an object variable rather than a Counter variable. The following code loops through all the cells in Column A. The code uses the .CurrentRegion property to define the current region and then uses the .Resize property to limit the selected range to a single column. The object variable is called Cell. Any name could be used for the object variable, but Cell seems more appropriate than Bob.
For Each cell in Range(“A1”).CurrentRegion.Resize(, 1)
If cell.Value = “Total” Then
cell.resize(1,8).Font.Bold = True
End If
Next cell
This code sample searches all open workbooks, looking for a workbook where the first worksheet is called Menu:
For Each wb in Workbooks
If wb.Worksheets(1).Name = “Menu” Then
WBFound = True
WBName = wb.Name
Exit For
End If
Next wb
In this code sample, all shapes on the current worksheet are deleted:
For Each Sh in ActiveSheet.Shapes
Sh.Delete
Next Sh
This code sample deletes all pivot tables on the current sheet:
For Each pt in ActiveSheet.PivotTables
pt.TableRange2.Clear
Next pt