Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Slow Excel row/column delete (Excel2003 VBA)

    I'm using this code:
    <pre> For Each c In [Range_hor]
    If c.Value = "xxx" Then
    If c.EntireColumn.Hidden <> True Then c.EntireColumn.Hidden = True
    Else
    If c.EntireColumn.Hidden <> False Then c.EntireColumn.Hidden = False
    End If
    Next
    </pre>

    To hide columns (later I do the same for rows). The extra "IF" is an attempt to make things quicker: do not hide if it is already hidden and v.v.

    Still this code is very (!) slow in my sheet... FYI ScreenUpdating is False and Calculation is set to manual.

    Any suggestions? Would e.g. leaving the IF's out make it quicker?

    Erik Jan

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Slow Excel row/column delete (Excel2003 VBA)

    Is that supposed to hide the columns if any cell in that column of the range is "xxx"? Is the range just one row or more than one? (if it's more than one, then it may not do what you want)
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Slow Excel row/column delete (Excel2003 VBA)

    What is c declared as?
    What is a representative value of Range_hor?
    Regards
    Don

  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Slow Excel row/column delete (Excel2003 VBA)

    [Range_Hor] is a range with dimensions 1 by 255 (so it is one row high), The loop therefore scans these (header) cells only and hides each column if that header cell (in the top row) is having "xxx" in it

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Slow Excel row/column delete (Excel2003 VBA)

    You might try this:
    <pre>Dim c As Range
    For Each c In Range("Range_hor").Columns
    If Application.CountIf(c, "xxx") > 0 Then c.EntireColumn.Hidden = True
    Next
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Slow Excel row/column delete (Excel2003 VBA)

    for [Range_hor] see my previous answer. The c variable is declared without type (so I guess it's a Variant): Dim c

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Slow Excel row/column delete (Excel2003 VBA)

    You might also try setting <code>ActiveSheet.DisplayPageBreaks = False</code>
    if you have page breaks displayed, and declare c as a Range.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Slow Excel row/column delete (Excel2003 VBA)

    Revised version:
    <pre>Dim c As Range
    For Each c In Range("Range_hor")
    c.EntireColumn.Hidden = (c.Value = "xxx")
    Next
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Slow Excel row/column delete (Excel2003 VBA)

    Can you post a sample workbook that demonstrates the problem? Hiding columns is instantaneous when I try it (there are only 256 columns in Excel 2003, after all)

  10. #10
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Slow Excel row/column delete (Excel2003 VBA)

    No real speed improvement (display pagebreaks line added to, I wasn't displaying any so this also didn't help)

  11. #11
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Slow Excel row/column delete (Excel2003 VBA)

    Regretfully that will be very difficult due to the type of info I have in the sheet. There are a lot of (long) cell-comments in other cells. Typically however, columns (and rows) that have headers with "xxx" are normally fully empty (that is, there are no comments, no contents but some formatting (borders)).

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Slow Excel row/column delete (Excel2003 VBA)

    Strange - like Hans, it is pretty much instantaneous for me. (presumably you are not in Page Break Preview mode then!) Might be useful to see a workbook.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Slow Excel row/column delete (Excel2003 VBA)

    Sorry, but I am confused. I would expect Range_hor to look something like "A27:IV27". Can you provide a specific example?
    Regards
    Don

  14. #14
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Slow Excel row/column delete (Excel2003 VBA)

    OK, it's not 255 cols but a bit less (I believe 248); here's range_hor: $F$7:$IK$7

  15. #15
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Slow Excel row/column delete (Excel2003 VBA)

    I just stepped through the code in debug mode (with F8) and noticed that the actual "c.EntireColumn.Hidden = True" is the statement that takes time. All the rest is quick (which is why the code improvements didn't do much).

    I just place an "Enableevents=false" before it but also that did not help

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
  •