Hide rows based on cell colors

Question

Hello,

I have this simple marco which hides rows in my selection based on specific cell colors.

Sub HideRows()

Dim r As Range

For Each r In Selection

If r.Interior.Color = RGB(0, 0, 0) Or r.Interior.Color = RGB(0, 51, 0) Or r.Interior.Color = RGB(17, 17, 17) Then

r.EntireRow.Hidden = True

End If

Next

End Sub

It works like a charm but when I select a whole column, it takes quite a while to perform the task.

To avoid checking the whole column, I would like to stop the macro when finding 10 consecutive blank cells.

Thanks in advance for your help.

Answer 1

Hi,

That's what you asked for. On encountering 10 blank cells the macro should terminate. A coloured cell that has nothing in it is blank as far as excel is concerned This will now terminate if it encounters 10 non coloured cells that also have nothing in them.

Sub HideRows()

   Dim r As Range, Blankcount As Long

   Dim s As String, v As Variant

   Blankcount = 0

   s = "Apple,Banana,Pineapple"

 v = Split(s, ",")

   For Each r In Selection

   If r.Interior.Color = 16777215 And Len(r) = 0 Then

      Blankcount = Blankcount + 1

  Else

      Blankcount = 0

  End If

   If IsError(Application.Match(r.Value, v, 0)) And _

   r.Interior.Color = RGB(0, 0, 0) Or r.Interior.Color = RGB(0, 51, 0) Or r.Interior.Color = RGB(17, 17, 17) Then

   r.EntireRow.Hidden = True

   End If

   If Blankcount = 10 Then Exit Sub

   Next

   End Sub

Answer 2

Hi,

Try this. If the code encounters 10 consecutive blank cells it will terminate.

Sub HideRows()

 Dim r As Range, Blankcount As Long

 Blankcount = 0

 For Each r In Selection

 If Len(r) = 0 Then

    Blankcount = Blankcount + 1

Else

    Blankcount = 0

End If

 If r.Interior.Color = RGB(0, 0, 0) Or r.Interior.Color = RGB(0, 51, 0) Or r.Interior.Color = RGB(17, 17, 17) Then

 r.EntireRow.Hidden = True

 End If

 If Blankcount = 10 Then Exit Sub

 Next

 End Sub

Answer 3

Hi,

Try this. You can add/change things in the bold string but be careful to keep the syntax.

Sub HideRows()

  Dim r As Range, Blankcount As Long

  Dim s As String, v As Variant

  Blankcount = 0

  s = "Apple,Banana,Pineapple"

v = Split(s, ",")

  For Each r In Selection

  If Len(r) = 0 Then

     Blankcount = Blankcount + 1

 Else

     Blankcount = 0

 End If

  If r.Interior.Color = RGB(0, 0, 0) Or r.Interior.Color = RGB(0, 51, 0) Or r.Interior.Color = RGB(17, 17, 17) And _

  IsError(Application.Match(r.Value, v, 0)) Then

  r.EntireRow.Hidden = True

  End If

  If Blankcount = 10 Then Exit Sub

  Next

  End Sub

Answer 4

Thanks Mike,

That solved the problem!

I forgot I would also like to make it ignore specific cells, say the ones matching the following content:

apple

banana

pineapple

Thanks again

Answer 5

Hello,

Instead of working on the whole column (selection), you could use code to find the last row with a value in the selection and then only work the loop until that last row. 

Sub test()

Dim r As Range

Dim mycol As Long, lrow As Long

mycol = Selection.Column

lrow = Cells(Selection.Rows.Count, mycol).End(xlUp).Row

For Each r In Range(Cells(1, mycol), Cells(lrow, mycol))

    ' this will show you the cells in the range in the immediate window

    ' comment it out if you don't need it anymore

    Debug.Print r.Address

    'your code to hide goes here

Next r

End Sub