Hide Columns Macro Executes Slowly

ringmaker

Board Regular
Joined
Oct 26, 2003
Messages
75
Hello - I have a simple macro that looks at a named range which is one row of the spreadsheet from columns 1 to 100. If an "x" is found, that column should be hidden. This code executes extremely slowly. I am evaluating a range with 100 columns but I could do this faster manually. Is there a way to speed this up? Thanks!!!

'Hide Columns with an X
For Each HideRange In Me.Range("HideRange")
If HideRange = "x" Then
HideRange.Columns.Hidden = True
End If
Next

I also tried this another way with the same speed issue....

For Each HideRange In Me.Range("HideRange")
If HideRange = "x" Then
hc = HideRange.Column
Columns(hc).Hidden = True
End If
Next
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Tony Miall

Active Member
Joined
Oct 16, 2007
Messages
304
the first thing you could do is

"Application.ScreenUpdating = False" at the start of the macro

"Application.ScreenUpdating = True" at the end at the end of the macro
 

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
Same recommendation as above.

But your code shouldn't take very long to run with or without screen updating turned on.

You don't happen to have subtotal or some other volatile function across all those columns?

Maybe add:
Application.Calculation = xlCalculationManual
' code
Application.Calculation = xlCalculationAutomatic
 

ringmaker

Board Regular
Joined
Oct 26, 2003
Messages
75
Thanks to both of you. I actually do have a line of code to set ScreenUpdating to False and calculation is set to manual (i just gave you the section of code that was slow). Any other thoughts? Thanks again!!!!
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,687
Office Version
2013
Platform
Windows

ADVERTISEMENT

What's the range reference for HideRange?
Custom views aren't used much I guess, but you might try it (if the hidden columns are always the same). Don't need vba either for that :)
 

ringmaker

Board Regular
Joined
Oct 26, 2003
Messages
75
Hi Xenou - Thanks for your idea. Unfortunately the columns that need hiding are different depending on the current date. Let me know if anything else comes to mind. Thanks so much! k
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,687
Office Version
2013
Platform
Windows
My only experience with problems hiding (rows, usually) is because of re-calculation - i.e., when I apply a filter or remove one. So PA HS Teacher's idea is worth a try (turning off calculation while hiding the rows). There's nothing per se about your code that should take a long time but the devil is in the details.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,198
Messages
5,509,762
Members
408,752
Latest member
KrisF

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top