Add Hiding rows to current VBA code

Question

I have written VBA code to hide specific ranges in 4 different tabs in my workbook. I am looking to add another range to the code that would hide rows based on cell value in the same range on each of the 4 different tabs. If the cell in range B9-B13 is blank, then hide that specific row on each tab.

Here is my current code:

 Sub  HideRowsSummary()
     Dim wsMySheet As Worksheet
     Dim lngMyRow  As Long
     Application.ScreenUpdating = False
     For Each wsMySheet In ThisWorkbook.Sheets
     Select Case wsMySheet.Name
        Case Is = "Summary 1", "Summary (2)",  "Summary (3)", "Summary (4)"
                For lngMyRow = 73 To 24 Step  -1 'Need to work backwards through the rows when hiding or deleting
                    If  Len(wsMySheet.Range("A" & lngMyRow)) = 0 Then
                         wsMySheet.Range("A" & lngMyRow).EntireRow.Hidden = True
                    Else
                         wsMySheet.Range("A" & lngMyRow).EntireRow.Hidden = False
                    End If
                Next lngMyRow
         End Select
     Next wsMySheet
     Application.ScreenUpdating = True
End Sub

Thank you for looking!

You don't even loop through the range you mention. Shouldn't you loop through B9-B13 ? Or is that what you intend to add after? — QHarr, Aug 27 '18 at 17:02
You don't need to work backwards to hide rows but you do to insert or delete rows. — , Aug 27 '18 at 17:13

QHarr · Accepted Answer · 2018-08-27T18:51:18.190

Such as the following straight after your prior loop? It is more efficient to use Union to gather qualifying rows (if all in same sheet) together and hide in one go by the way. In that way you could collect the qualifying rows from both loops (you could also merge the loops into) and hide all in one go.

For lngMyRow = 13 To 9 Step -1  'Need to work backwards through the rows when hiding or deleting
    wsMySheet.Range("B" & lngMyRow).EntireRow.Hidden = (wsMySheet.Range("B" & lngMyRow) = vbNullString)
Next lngMyRow

Thanks to @ScottCraner for pointing out the very nice shorthand there.

Two loop with union of ranges might look like (not tested):

Option Explicit
Public Sub HideRowsSummary()
    Dim wsMySheet As Worksheet
    Dim lngMyRow  As Long, unionRng As Range
    Application.ScreenUpdating = False
    For Each wsMySheet In ThisWorkbook.Sheets
        Select Case wsMySheet.NAME
        Case Is = "Summary 1", "Summary (2)", "Summary (3)", "Summary (4)"
            With wsMySheet
                .Range("A9:A13, A24:A74").EntireRow.Hidden = False
                For lngMyRow = 9 To 13
                    If .Range("B" & lngMyRow) = vbNullString Then
                        If Not unionRng Is Nothing Then
                            Set unionRng = Union(unionRng, .Range("B" & lngMyRow))
                        Else
                            Set unionRng = .Range("B" & lngMyRow)
                        End If
                    End If
                Next lngMyRow
                For lngMyRow = 24 To 73
                    If Len(.Range("A" & lngMyRow)) = 0 Then
                        If Not unionRng Is Nothing Then
                            Set unionRng = Union(unionRng, .Range("A" & lngMyRow))
                        Else
                            Set unionRng = .Range("A" & lngMyRow)
                        End If
                    End If
                Next lngMyRow
            End With
        End Select
        If Not unionRng Is Nothing Then unionRng.EntireRow.Hidden = True
        Set unionRng = Nothing
    Next wsMySheet
    Application.ScreenUpdating = True
End Sub

How would the total code look based on your recommendations? I am open to improving efficiency. — Hery0502, Aug 27 '18 at 17:13
1. only need to work backwards with adding or deleting rows. 2. you can remove the `If` with: `wsMySheet.Range("B" & lngMyRow).EntireRow.Hidden = IsEmpty(wsMySheet.Range("B" & lngMyRow))` — Scott Craner, Aug 27 '18 at 17:16
@QHarr - The range in B9:B13 is populated with formulas and that is my mistake for not stating that before. The code works perfectly, except it wont hide range B9:B13 because it is occupied with a formula. How does that change the code? — Hery0502, Aug 27 '18 at 18:42
my apologies, the range is updated in my comment of B9:B13. The formula produces "" if there is nothing to show and I would like those rows to be hidden if "". — Hery0502, Aug 27 '18 at 18:49
@QHarr - works perfectly, thank you for the help and updated efficiency all! — Hery0502, Aug 27 '18 at 18:54
@QHarr - I would love your thoughts on my next question, your way of writing code was efficient. https://stackoverflow.com/questions/52062375/hide-multiple-rows-based-on-multiple-ranges-cell-value-vba — Hery0502, Aug 28 '18 at 16:22

Add Hiding rows to current VBA code

1 Answers1