Results 1 to 6 of 6

Very slow macro

Threaded View

  1. #1
    Registered User
    Join Date
    01-01-2010
    Location
    The Netherlands
    MS-Off Ver
    365
    Posts
    34

    Very slow macro

    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
    Attached Images Attached Images
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1