CODEWITHSUNDEEP
excelvba
Marcin Jędrzejczyk
Marcin Jędrzejczyk

Reputation: 53

How to hide specific rows using VBA?

I'm trying to write code to hide rows between cells with exact color provided, and as far everything worked great I get an 1004 application-defined or object-defined error here:
Set c = c.Offset(1, 0) 'next row

In a screenshot I post how does my spreadsheet look. This code wasn't written by me but was taken from my another question, I just modified it a bit to a new idea. The code:

 Sub showfour()
    ToggleRows RGB(218, 238, 243), RGB(231, 238, 243), False
End Sub
Sub hidefour
    ToggleRows RGB(218, 238, 243), RGB(231, 238, 243), True
End Sub

'Show or hide rows, beginning with a cell in ColB with fill color `clr`
'  and ending with the first cell filled yellow or with no fill
'  `HideRows` = True will hide, False will unhide.
Sub ToggleRows(clr As Long, lastcol As Long, HideRows As Boolean) 'clr - cell that marks the start, lastcol - ending cell
    'every variable needs a type, unless you want a Variant
    Dim v As Long, c As Range, ws As Worksheet

    Set ws = Worksheets(1)
    For v = 1 To ws.Cells(Rows.Count, "B").End(xlUp).Row
        If ws.Cells(v, "B").Interior.Color = clr Then        'has the color of interest
            Set c = ws.Cells(v, "B").Offset(1, 0)            'next cell down
            
            Do While c.Interior.Color <> lastcol
                Set c = c.Offset(1, 0) 'next row
            Loop
            v = v + 1
            
            ws.Rows(v).Resize(c.Row - v - 1).EntireRow.Hidden = HideRows
            MsgBox v
        End If
    Next v
    
    MsgBox c
    
End Sub

The spreadsheet: sheet As you can see from a to c it works great but the d one is broken.

Upvotes: 1

Views: 158

Answers (1)

Tim Williams
Tim Williams

Reputation: 166306

Try this:

Sub showfour()
    ToggleRows RGB(218, 238, 243), RGB(231, 238, 243), False
End Sub
Sub hidefour()
    ToggleRows RGB(218, 238, 243), RGB(231, 238, 243), True
End Sub

'clr - cell that marks the start, lastclr - ending cell
Sub ToggleRows(clrStart As Long, clrEnd As Long, HideRows As Boolean)
    Dim v As Long, cS As Range, cE As Range, ws As Worksheet, lr As Long

    Set ws = Worksheets(1)
    lr = ws.Cells(Rows.Count, "B").End(xlUp).Row 'last cell with content in Col B
    Set cS = ws.Range("B1")
    Do While cS.Row < lr
        If cS.Interior.Color = clrStart Then        'has the color of interest
            Set cE = cS.Offset(1)
            Do While cE.Interior.Color <> clrEnd
                If cE.Row = lr Then
                    MsgBox "No matching 'end' color for start cell " & cS.Address, vbCritical
                    Exit Sub
                Else
                    Set cE = cE.Offset(1, 0)
                End If
            Loop
            ws.Range(cS, cE).EntireRow.Hidden = HideRows
            Set cS = cE
        End If
        Set cS = cS.Offset(1)
    Loop
End Sub

Upvotes: 1

Related Questions