How to Use For Each-Next Loops with Collections in VBA Excel 2016 - dummies

How to Use For Each-Next Loops with Collections in VBA Excel 2016

By John Walkenbach

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
    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