Hi,
Fairly new to Excel, but managed to create (with some help) a macro that hides all rows without any colour filled cells in it. I tried to transfer the macro to a new worksheet and it wont work any more.
Can you please help me understand and fix what's wrong?
Here is the macro, also I've attached the doc.
Sub Hide_Rows()
Dim ws As Worksheet: Set ws = Sheets("PA Yearly Servicing Schedule")
Dim LR As Long, LC As Long, lcell As Long
Dim myRange As Range, rCell As Range
Dim bHide As Boolean
Application.ScreenUpdating = False
LC = ws.Cells(5, Columns.Count).End(xlToLeft).Column
LR = ws.Range("D" & Rows.Count).End(xlUp).Row
For lcell = 6 To LR
bHide = True
Set myRange = ws.Range(ws.Cells(lcell, 5), ws.Cells(lcell, LC))
For Each rCell In myRange
If rCell.Interior.ColorIndex <> -4142 Then 'has color
bHide = False
Exit For
End If
Next rCell
If bHide = True Then
ws.Range("A" & lcell).EntireRow.Hidden = True
End If
Next lcell
Application.ScreenUpdating = True
End Sub
PA Servicing schedule 12-13 kellys USE THIS.xlsm
Bookmarks