Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Excel VBA performance problem (Excel 2002 / 2003)

    I have a Macro that works fine on Excel 2002 but has a serious performance problem on Excel 2003.

    I have identified the problem line as...
    <font face="Georgia"> shtNext.Rows(i).EntireRow.Hidden = False </font face=georgia>
    which is in the middle of a nested loop that hides many rows in many sheets. It chooses which rows to hide based on tests it does on various cells in the row.

    On Excel 2002 the loop takes 1 or 2 seconds to hide about 50 rows
    On Excel 2003 this single statement takes 2 to 3 seconds to hide one row

    Has anyone come across anything similar, or have any suggestions for faster ways to achieve the same effect.

    StuartR

  2. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA performance problem (Excel 2002 / 2003)

    Use

    With shtNext

    End With

    To surround the loop and use the following instead of what you are currently using.

    .Rows(i).EntireRow.Hidden = False

    Also, make sure the statement is onlyin the For i loop and not nested in an inner loop.

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Excel VBA performance problem (Excel 2002 / 2003)

    If your object is to hide certain rows based on criteria

    > shtNext.Rows(i).EntireRow.Hidden = False

    is there any chance that kicking off the macro by unhiding all rows with

    shtNext.Cells.EntireRow.Hidden = False

    followed by the selection of rows to hide, will work better?

    >based on tests it does on various cells in the row

    Stuart, if you can explain the tests there might be a SpecialCells, Autofilter or other approach than looping. I also wonder if setting

    Application.ErrorCheckingOptions.BackgroundCheckin g = False

    at the beginning will help.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Excel VBA performance problem (Excel 2002 / 2003)

    John,

    The selection is fairly simple.

    Cell M12 contains a text string which matches one of cells M13 to R13. This selects a column.
    I have one range of rows., withg a known starting and ending row, that may need to be hidden
    In each row I check the text string in the selected column to see if it matches a string constant and if it matches I hide the entire row.

    On the next sheet I will again compare M12 to M13:R13, and get a (probably different) match which selects a column to check.
    (The cells may not be M13:R13, but I know where they are for any particular sheet and I store the row and column numbers in variables)

    StuartR

    e.g.

    For a particular sheet
    iStartRow = 16
    iEndRow = 50
    txtHidden is a defined string constant

    M12 = "Text2"
    M13:R13 = "Text1", "Text4", "Text2", "Text5", "Text3", "Text6"
    So the selected column is O
    If Cell O16 = txtHidden then I hide row 16
    If Cell O17 = txtHidden then I hide row 17
    ...
    To row 50
    Then I go do another sheet

    StuartR

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel VBA performance problem (Excel 2002 / 2003)

    Try something like<pre> Dim c As Range
    For Each c In Selection
    If c.Text = txtHidden Then c.EntireRow.Hidden = True
    Next</pre>


    It should be faster than a For Next loop.

    Andrew

  6. #6
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Excel VBA performance problem (Excel 2002 / 2003)

    The delay isn't coming in the For...Next loop, which has a number of other lines that run at reasonable speeds. It is the specific line that Hides the row that runs more than 100 times slower on Excel 2003.

    StuartR

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel VBA performance problem (Excel 2002 / 2003)

    Stuart,

    Does it help if you put Application.Calculation = xlCalculationManual before your code, and Application.Calculation = xlCalculationAutomatic after it?

  8. #8
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Excel VBA performance problem (Excel 2002 / 2003)

    > Does it help if you put Application.Calculation = xlCalculationManual before your code

    Yes it does.
    That was one of the things that I had already done to get the best possible performance on Excel 2002.

    My problem here is that this isn't just a bit slower on Excel 2003, it is hundreds of times slower. Code that used to take less than a minute to run (over a workbook with 30 sheets and 100 rows in each) now takes over an hour.

    StuartR

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel VBA performance problem (Excel 2002 / 2003)

    Yes, this is mentioned in the newsgroups too, but there the suggestion was that Excel 2003 recalculates everything each time you hide or unhide cells/rows/columns, so turning off automatic recalculation should avoid the problem. Apparently, there is something more fundamentally wrong.

    The following might help a bit too, since the instruction that sets Hidden = True is executed only once for a worksheet, but with the speed difference you experience it won't be enough. <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

    <img src=/w3timages/blueline.gif width=33% height=2>

    Sub Test()
    Const txtHidden = "Secret"
    Dim iStartRow As Long
    Dim iEndRow As Long
    Dim i As Long
    Dim rng As Range
    Dim sht As Worksheet

    Set sht = Worksheets("MySheet")
    iStartRow = 16
    iEndRow = 50
    ' Must start with non-empty range
    Set rng = sht.Range("A1")
    ' Loop through cells
    For i = iStartRow To iEndRow
    If sht.Cells(i, 15).Value = txtHidden Then
    Set rng = Union(rng, sht.Cells(i, 1))
    End If
    Next i
    ' Remove first cell
    Set rng = Intersect(rng, sht.Range("A" & iStartRow & ":A" & iEndRow))
    If Not rng Is Nothing Then
    rng.EntireRow.Hidden = True
    End If

    Set rng = Nothing
    Set sht = Nothing
    End Sub

    <img src=/w3timages/blueline.gif width=33% height=2>

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Excel VBA performance problem (Excel 2002 / 2003)

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20> But, Hans, isn't it the -unhide row- method that's Stuart's problem?

    cell.EntireRow.Hidden = False
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel VBA performance problem (Excel 2002 / 2003)

    Both your and my code avoid hiding rows one by one.

  12. #12
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Excel VBA performance problem (Excel 2002 / 2003)

    I know nothing about Excel, but thinking from a Word perspective, I wonder whether you could set the row height to zero (or back to the default value) instead of hiding (and unhiding)?

  13. #13
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Excel VBA performance problem (Excel 2002 / 2003)

    I just rechecked the code and It turns out that I lied about the calculation state. The code path that leads to the problem had Application.Calculation reneabled in my last release because I need to recalculate the cell that determines whether I hide the row - I had been getting some very strange results otherwise.

    I will be trying out many of these suggestions next week (I don't have a copy of Office 2003 installed at home) and I will let you know what happens.

    regards,

    StuartR

  14. #14
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Excel VBA performance problem (Excel 2002 / 2003)

    I will almost certainly incorporate one of these two ideas in the next release. It makes a lot of sense to create a range and then take an action once.

    It will take some work as there is other conditional testing which determines whether I hide or delete the rows (and a maintenance mode option that just unhides everything), so I need to be careful not to lose one of the many special conditions.

    Thanks for all the help and advice.

    StuartR

  15. #15
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Excel VBA performance problem (Excel 2002 / 2003)

    Stuart, this sample is crude and you'll have to mould it into your overall code, but this method may prove faster than looping:

    Sub HideMacro()
    Dim rngMatch As Range
    Dim strCol As String, strHideText As String
    ' you'll probably replace 'Activesheet' with 'shtNext'
    ActiveSheet.AutoFilterMode = False
    ActiveSheet.Cells.EntireRow.Hidden = False
    strCol = "D" ' or otherwise obtained
    strHideText = "filtertext" ' or otherwise obtained
    Columns(strCol).AutoFilter Field:=1, Criteria1:=strHideText
    Set rngMatch = Columns(strCol).SpecialCells(xlCellTypeVisible)
    ActiveSheet.AutoFilterMode = False
    rngMatch.EntireRow.Hidden = True
    End Sub

    Let us know.
    -John ... I float in liquid gardens
    UTC -7ąDS

Page 1 of 2 12 LastLast

Posting Permissions

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