Sorry, but another question about slow macro's. I've read a lot of threads about it but cannot figure out what I'm doing wrong here.
This macro will check a large cost estimate for empty line items. If no quantity is calculated, the line item is hidden. Also if a heading has no line items at all, the heading (incl. subtotal) is hidden as well. So far, this works. However if a cost estimate has several hundred line items and a couple of dozen objects (worksheets) it takes a very long time.
Maybe somebody has some advice to improve this macro? Maybe the 'case' statement instead of 'if end if'?
Sub HideEmptyRows()
Application.ScreenUpdating = False
'For Sht = 4 To Worksheets.Count - 1
LastRow = Worksheets("Object 1").Range("Obj_BKbdk").row 'end of this sheet
Range("11:" & LastRow).EntireRow.Hidden = False 'first everything visible
For Rw = 12 To LastRow - 1
If Range("C" & Rw).Font.Bold = False Then 'first bold cell is heading
If Range("B" & Rw).Value Like "?????0" = True And Range("S" & Rw).Value = 0 Then 'column S = total to search for (empty or not)
Range(Rw & ":" & Rw).EntireRow.Hidden = True
End If
Else
If Range("B" & Rw).Value Like "" = True And Range("L" & Rw).Value = 0 Then 'second bold cell contains subtotal in column L. If subtotal zero then also hide heading
Range(Rw & ":" & Rw).EntireRow.Hidden = True
'offset up and down to hide heading and empty row underneath
Range("B" & Rw).End(xlUp).EntireRow.Hidden = True
Range("B" & Rw + 1).EntireRow.Hidden = True
End If
End If
Next Rw
'Next Sht
Application.ScreenUpdating = True
End Sub
Sub UnhideRows()
Application.ScreenUpdating = False
'For Sht = 4 To Worksheets.Count - 1
LastRow = Worksheets("Object 1").Range("Obj_BKbdk").row 'end of this sheet
Range("11:" & LastRow).EntireRow.Hidden = False 'first everything visible
'Next Sht
Application.ScreenUpdating = True
End Sub
The code with ?????0 knows what a line item is. They always have 6 digits and a zero at the end.
Thank you in advance.
Erik
Bookmarks