Don't hide row with at least one cell with color

tqv1969 · Mar 20th 2006

Dear Gurus,

I tried the VBA to hide the row if the range of cells e.g: C2:E2 are blank and the macro hide all rows if they find the conta of cells = 0 . But the issue that I want to keep those row that is blank but have at least one cell with color in back groud + comment.
1- Can I do that in VBA?
2- Can I check the empty range instead of counta cells in above condition for hide the rows?

Best regards,
TQV

Joebbshop · Mar 20th 2006

Re: Don't hide row with at least one cell with color

Code

Sub AutoHideBlankRows()


    Dim j As Long
    Dim LastRow As Long
    Dim i As Integer
    Dim LastColumn As Integer
    
    
    LastRow = Cells(1, 1).End(xlDown).Row
    LastColumn = Cells(1, 1).End(xlToRight).Column
    
    For j = 2 To LastRow
        'Cells(j, 2).Select
        If Cells(j, 2).Value = 0 Then
            For i = 3 To LastColumn
                'Cells(j, i).Select
                'MsgBox Cells(j, i).Interior.ColorIndex
                If Cells(j, i).Interior.ColorIndex <> -4142 Then
                    GoTo NextRowTag
                End If
            Next i
            Cells(j, 2).EntireRow.Hidden = True
        End If
NextRowTag:
    Next j
End Sub

Display More

Only hides if your COUNTA cell reports 0 and if there are no colored cells in the cells to the right.

Note that I changed the variable definations... i refers to a column, j refers to a row.

Experts: how do you do this using a range rather than using the Cells(j,i) method?

tqv1969 · Mar 21st 2006

Re: Don't hide row with at least one cell with color

Dear Joebbshop ,

It work well. Thanks you. And Yes can we manage this by range?

Best regards,
TQV

Don't hide row with at least one cell with color

Participate now!

Share