Macro to hide a series of rows that have no 'color' formatting

[AJHughes]

I am a casual Excel user and have dabbled in macro writing 10 years ago. I have a current problem where I am identifying certain data by adding different colors to a range of cells. I need a macro that will 'hide' all rows that do not contain any colored cells. Anyone have any suggestions?

[rvasquez]

Hello there,

The below code should work for you:

Dim LR As String, i As Long  'declare variables
LR = Range("A6555").End(xlUp).Row  ' Set Lr equal to the last row in column A that contains a value

For i = 1 To LR   'i is equal to numbers 1 through the last row number (loop through i)
    If Rows(i & ":" & i).Interior.ColorIndex = xlNone Then   'if the current row number in the loop contains no colored cells then
        Rows(i & ":" & i).RowHeight = 0   'hide the row
    End If
Next i

[AJHughes]

I applied the code as follows and it only hid the first row with no color, not all of the rows. I am searching rows 4 to 89.


Dim LR As String, i As Long 'declare variables
LR = Range("B89").End(xlUp).Row ' Set Lr equal to the last row in column A that contains a value

For i = 1 To LR 'i is equal to numbers 1 through the last row number (loop through i)
If Rows(i & ":" & i).Interior.ColorIndex = xlNone Then 'if the current row number in the loop contains no colored cells then
Rows(i & ":" & i).RowHeight = 0 'hide the row
End If
Next i
End Sub

[AJHughes]

I figured it out! I added 'For i = 4 To 89' and it worked to hide all rows with no colored cells!

[rvasquez]

Hello there,

That will work as long as your data remains no more than 89 rows. If you want it to be able to be used in the instance you add more rows

Dim LR As String, i As Long 'declare variables
LR = Range("B6555").End(xlUp).Row ' Set Lr equal to the last row in column A that contains a value

For i = 1 To LR 'i is equal to numbers 1 through the last row number (loop through i)
If Rows(i & ":" & i).Interior.ColorIndex = xlNone Then 'if the current row number in the loop contains no colored cells then
Rows(i & ":" & i).RowHeight = 0 'hide the row
End If
Next i
End Sub

You code would look like above, I've highlighted the change in the code in red. Just for future reference you use B6555 as a started because the End(xlup) code basically says to start at Row 6555 and find the first cell before row 6555 in column B that has data in it and return that row number.

Please don't forget this thread solved and maybe give a little star tap if I've helped.

Thanks!