Excel – Hide multiple rows in excel VBA

microsoft excelvba

I am trying to hide all rows containing the value "xx" in column A and not hide the rows containing "a" in column A. The range is from A8:A556. This macro needs to be triggered by a change in cell C4.

Any idea why it is not working? Here's the current attempt:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address(True, True) = "$C$4" Then

    If Range("A8:A555").Value = "xx" Then
        Rows("8:555").EntireRow.Hidden = True
    ElseIf Range("A8:A555").Value = "a" Then
        Rows("8:555").EntireRow.Hidden = False

     End If
   End If

End Sub

Thanks!

EDIT:

Hey Fixer1234, Here is the detail:

"xx" and "a" may not exist together if the macro works correctly.
Only "xx" rows must be hidden
"xx" values are not in combination with values that cannot be hidden.
"a" values are not in combination with values that must be hidden.
there are other rows that do not contain "xx or "a" that don't need to be hidden (should I add an "a" to these columns or completely remove the IfElse?).
"xx" row contain other formulas, a blank result from the formula means the row must be hidden.
"a" rows have the same formula but it produces a result and must not be hidden.

Run-time error '13': type mismatch
and then the debug goes to the first
If Range("A8:A555").Value = "xx" Then

Best Answer

You can't test all the values in a range using Range("A8:A555").Value = "xx". It will throw a Type Mismatch error.

You need to loop through each cell in the range and test individually if its Value equals "xx".

To make the code run faster, don't hide rows as you detect them but build up a Range of Ranges to be hidden then hide them all at once.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cel As Range
Dim RangeToHide As Range

    'Test if Target is C4
    If Not Intersect(Target, [C4]) Is Nothing Then
        'Loop through each cell in range and test if it's "xx"
        For Each cel In Range("A8:A555")
            If cel = "xx" Then
                If RangeToHide Is Nothing Then
                    'Initialise RangeToHide because Union requires at least 2 ranges
                    Set RangeToHide = cel
                Else
                    'Add cel to RangeToHide
                    Set RangeToHide = Union(RangeToHide, cel)
                End If
            End If
        Next
        RangeToHide.EntireRow.Hidden = True
    End If
End Sub

Related Solutions

Mac – How to create a macro to show/hide rows based on a dropdown list

The first line in the code below handles the Worksheet_Change. It makes sure only one cell was changed and that the cell was D13.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("D13")) Is Nothing Or Target.Cells.Count > 1 Then
        Exit Sub

    ElseIf Range("D13").Value = "Select One" Then
        Rows("77:82").EntireRow.Hidden = False

    ElseIf Range("D13").Value = "Limited" Then
        Rows("77").EntireRow.Hidden = False
        Rows("78:82").EntireRow.Hidden = True    

    ElseIf Range("D13").Value = "Unlimited" Then
        Rows("77").EntireRow.Hidden = True
        Rows("78:82").EntireRow.Hidden = False   

    End If

End Sub

Note that the code above must be copied into the Worksheet object for whatever Sheet your dropdown is on.

I hope this helps, and good luck.

Excel – How to hide rows based on a cell value

This is similar to another answer but testing shows performance is nearly 1.5 orders of magnitude (70x) faster by doing all the property changes in two transactions; one each for hideRange.EntireRow.Hidden = True and unhideRange.EntireRow.Hidden = False.

There are three constants at the beginning of the file used to specify the column, starting row, ending row and value to hide on. The OP values have been plugged in.

Copy the following code into the specific vba WorkSheet module. This code will not work from a regular module. Press Alt-F11 to open the Visual Basic Editor. Press Ctrl-R to focus/open the Project Explorer pane. Navigate to VBAProject(<file name>), Microsoft Excel Objects and open the Sheet#(<sheet name>) where the hidden rows reside.

Private Sub Worksheet_Calculate()

  ' Hide Rows if row value in watch_Column is hide_On_Value.
  ' watch_Column must include start_on row number (e.g. A1 or C3)

  ' Hidden rows, beyond the range of cells with values, may not
  ' unhide. For speed, only process rows being used <= end_of_watch.
    Const watchColumn = "B45" ' Beginning Cell (row and column) to watch.
    Const endOfWatch = "135" ' Last row. if "", rest of rows in use.
    Const hideOnValue = 0

    Dim hideRange As Range
    Dim unhideRange As Range
    Dim r As Range
    Dim seeRow As Boolean
    Dim watchStart() As String
    Dim lastRow As String
    Dim tmpEnableEvents As Boolean

    Set r = Me.UsedRange ' call and discard to reset LastCell
    With Me.UsedRange
        lastRow = .Row + .Rows.Count - 1
    End With
    If endOfWatch <> "" Then
       If Val(lastRow) > Val(endOfWatch) Then lastRow = endOfWatch
    End If
    watchStart = Split(Me.Range(watchColumn).Address(True, False), "$")
    If Val(watchStart(1)) > Val(lastRow) Then Exit Sub
    tmpEnableEvents = Application.EnableEvents
    Application.EnableEvents = False
    For Each r In Me.Range(watchColumn & ":" & watchStart(0) & lastRow)
        seeRow = True
        If IsEmpty(r) Then
        ElseIf CStr(r.Value2) = vbNullString Then
        ElseIf r = hideOnValue Then
            seeRow = False
            If Not r.EntireRow.Hidden Then
                If hideRange Is Nothing Then
                    Set hideRange = r
                Else
                    Set hideRange = Union(hideRange, r)
                End If
            End If
        End If
        If seeRow And r.EntireRow.Hidden Then
            If unhideRange Is Nothing Then
                Set unhideRange = r
            Else
                Set unhideRange = Union(unhideRange, r)
            End If
        End If
    Next r
    If Not unhideRange Is Nothing Then
        unhideRange.EntireRow.Hidden = False
    End If
    If Not hideRange Is Nothing Then
        hideRange.EntireRow.Hidden = True
    End If
    Application.EnableEvents = tmpEnableEvents
End Sub

Regular Module Changes

Change the Sub name.
Change all the Me objects to a specific sheet reference.
- Worksheets("Worksheet Name") criteria for one worksheet.

Best Answer

Related Solutions

Mac – How to create a macro to show/hide rows based on a dropdown list

Excel – How to hide rows based on a cell value

Regular Module Changes

Related Question