How to hide specific rows using VBA?

Question

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: As you can see from a to c it works great but the d one is broken.

At the first glance, ToggleRows calls are wrongly done, not supplying the second parameter (lastcol). Last row is extracted fom RGB(231, 238, 243)... Then, the called sub calculated the filled cells only based on B:B column. I cannot understand from the linked picture what range you try processing... Firstly, please add Option Explicit on top of the module. Then, a better way should be to build a Union range and hide all the rows at once, at the end. — FaneDuru
– FaneDuru, Commented Apr 6, 2022 at 14:40
Set c = c.Offset(1, 0) - you've run off the bottom of the sheet. — Tim Williams
– Tim Williams, Commented Apr 6, 2022 at 16:57
@FaneDuru The point is that I only want to hide rows from a certain color to another certain color, sub calculates cells based on B:B because this is the only column in which the cell with specific color is present. I know that my communication skills here aren't good, but I don't know how to explain it otherwise. — Marcin Jędrzejczyk
– Marcin Jędrzejczyk, Commented Apr 7, 2022 at 5:44
@Tim Williams So that means that sub didn't find cell with such color ? — Marcin Jędrzejczyk
– Marcin Jędrzejczyk, Commented Apr 7, 2022 at 5:46
Which is that "another certain color" and where your code uses it? I am afraid you do not understand much of the code you posted... What lastcol should mean and where your code use it? — FaneDuru
– FaneDuru, Commented Apr 7, 2022 at 9:25

Tim Williams · Accepted Answer · 2022-04-07 16:12:41Z

1

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

answered Apr 7, 2022 at 16:12

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mayukh Bhattacharya Over a year ago

Sir, if its possible could you share me the working file.

Tim Williams Over a year ago

Here you go - docs.google.com/spreadsheets/d/…

Collectives™ on Stack Overflow

How to hide specific rows using VBA?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related