How to Use For Each-Next Loops with Collections in VBA Excel 2016
VBA supports yet another type of looping in Excel 2016: looping through each object in a collection of objects. A collection, as you may know, consists of a number of objects of the same type. For example, Excel has a collection of all open workbooks (the Workbooks collection), and each workbook has a collection of worksheets (the Worksheets collection).
When you need to loop through each object in a collection, use the For Each-Next structure. The following example loops through each worksheet in the active workbook and deletes the worksheet if it’s empty:
Sub DeleteEmptySheets() Dim WkSht As Worksheet Application.DisplayAlerts = False For Each WkSht In ActiveWorkbook.Worksheets If WorksheetFunction.CountA(WkSht.Cells) = 0 Then WkSht.Delete End If Next WkSht Application.DisplayAlerts = True End Sub
In this example, the variable WkSht is an object variable that represents each worksheet in the workbook. Nothing is special about the variable name WkSht; you can use any variable name that you like.
The code loops through each worksheet and determines an empty sheet by counting the nonblank cells. If that count is zero, the sheet is empty, and it’s deleted. Notice that the DisplayAlerts setting is turned off while the loop is doing its thing. Without that statement, Excel pops up a warning every time a sheet is about to be deleted.
If all of the worksheets in the workbook are empty, you get an error when Excel attempts to delete the only sheet. Normally, you would write code to handle that situation.
Here’s another For Each-Next example. This procedure uses a loop to hide all worksheets in the active workbook except the active sheet.
Sub HideSheets() Dim Sht As Worksheet For Each Sht In ActiveWorkbook.Worksheets If Sht.Name <> ActiveSheet.Name Then Sht.Visible = xlSheetHidden End If Next Sht End Sub
The HideSheets procedure checks the sheet name. If it’s not the same as the active sheet’s name, the sheet is hidden. Notice that the Visible property isn’t Boolean. This property can actually take on any of three values, and Excel provides three built-in constants. If you’re curious about the third possibility (xlVeryHidden), check the Help system.
What gets hidden must eventually get unhidden, so here’s a macro that unhides all worksheets in the active workbook:
Sub UnhideSheets() Dim Sht As Worksheet For Each Sht In ActiveWorkbook.Worksheets Sht.Visible = xlSheetVisible Next Sht End Sub
Not surprisingly, you can create nested For Each-Next loops. The CountBold procedure loops through every cell in the used range on each worksheet in every open workbook and displays a count of the number of cells that are formatted as bold:
Sub CountBold() Dim WBook As Workbook Dim WSheet As Worksheet Dim Cell As Range Dim Cnt As Long For Each WBook In Workbooks For Each WSheet In WBook.Worksheets For Each Cell In WSheet.UsedRange If Cell.Font.Bold = True Then Cnt = Cnt + 1 Next Cell Next WSheet Next WBook MsgBox Cnt & “ bold cells found” End Sub