Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    looping through cells too slow (2003)

    Hi, I want to share my experience with excel 2003
    I have the function in a checkbox

    Private Sub CheckBox2_Click()
    Set rowsProtypoCount = Worksheets(1).Range(

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: looping through cells too slow (2003)

    WHat about this variation of your code:

    Sub test()
    Dim oRng As Range
    Dim ocell As Range
    Set oRng = ActiveSheet.Range(ActiveSheet.Range("A10"), ActiveSheet.Range("A10").End(xlDown)).Offset(, 2)
    For Each ocell In oRng
    ocell.EntireRow.Hidden = (ocell.Value = 0)
    Next
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    New Lounger
    Join Date
    Jan 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: looping through cells too slow (2003)

    thanks, jan
    I've tried your code. It runs a little more quickly now, but it still has big differences between the two versions (The h/w is exactly the same in the two machines were I've tried this code).
    Your code of course is more efficient (I am a plsql programmer, but I had to do sth in Excel).
    Thanks again,
    Paris

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: looping through cells too slow (2003)

    maybe it helps to turn off calculation before the hiding and turn back on after?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: looping through cells too slow (2003)

    Try this modification of Jan Karel's code:

    <pre>Sub test()
    Dim oRng As Range
    Dim ocell As Range
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    Set oRng = ActiveSheet.Range(ActiveSheet.Range("A10"), ActiveSheet.Range("A10").End(xlDown)).Offset(, 2)
    For Each ocell In oRng
    ocell.EntireRow.Hidden = (ocell.Value = 0)
    Next
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.Calculate
    End Sub
    </pre>

    Legare Coleman

  6. #6
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: looping through cells too slow (2003)

    I would suggest adding an error handler to any sub that sets ScreenUpdating = False, Calculation = xlManual. If an error occurs during the code execution the handler is written to set everything back to normal.

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

    Re: looping through cells too slow (2003)

    FWIW I prefer to get the existing setting and then reset to it, like this:

    Dim intCalcSet As Integer
    ' Save the present setting for xlCalculation
    intCalcSet = Application.Calculation
    Application.Calculation = xlCalculationManual
    ' run code here
    ' When done reset and recalc
    Application.Calculation = intCalcSet
    Application.Calculate
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    New Lounger
    Join Date
    Jan 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: looping through cells too slow (2003)

    Thank you all, very much.
    It finally worked, with all the instructions you gave me it finally worked fine and quickly.


    Thank you all.

Posting Permissions

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