Excel VBA - Excessive Time to Complete

Question

I created a simple spreadsheet with a bit of VBA code to hide/unhide based upon the color of the column header. If the column header is red, and I want it hidden, then I click the button "Hide". Alternatively, if I want those hidden columns to reappear, I click "Unhide". It works. Yay!

The actual task of unhiding/hiding works, the problem is that it takes entirely too long.

snip of project

Here's the code for hiding:

Sub HideColumnIfRed()
Dim c As Range
For Each c In Range("A:AT")

If c.Interior.Color = vbRed Then
c.EntireColumn.Hidden = True

End If

Next c

End Sub

Here's the code for unhiding:

Sub HideColumnIfRed()
Dim c As Range
For Each c In Range("A:AT")

If c.Interior.Color = vbRed Then
c.EntireColumn.Hidden = False

End If

Next c

End Sub

What can I do to optimize this task?

Thank you in advance.

You're looping over 46*1048576 cells. Only loop over the cells you actually care about. — BigBen, May 10 '23 at 16:49
Additionally to the previous hint you could turn off [Screenupdating](https://stackoverflow.com/a/47092175/6600940) etc. — Storax, May 10 '23 at 16:54
Look into [Used Range](https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.usedrange) — cybernetic.nomad, May 10 '23 at 17:01
I thought I had restricted the range to A:AT? Is this wrong? — Keegan, May 10 '23 at 17:16
Too long is about 5 minutes for only 10-15 columns in the range of A:AT. — Keegan, May 10 '23 at 17:17
I know zero about ScreenUpdating as this is my first time using VBA...but I'm looking at it. Thanks. — Keegan, May 10 '23 at 17:18
`? Range("A:AT").CountLarge` in Immediate pane gives `48234496` so yes that's a lot of cells to loop over — Tim Williams, May 10 '23 at 17:21

score 1 · Answer 1 · answered May 10 '23 at 17:01

As a quick (maybe dirty) fix you could intersect your columns you are after with the used range like that

For Each c In Intersect(Range("A:AT"), ActiveSheet.UsedRange)

But be careful as ActiveSheet might not always the sheet you expect it to be. On the other hand you already use Range("A:AT") without reference which already refers to ActiveSheet anyway.

score 1 · Answer 2 · edited May 11 '23 at 00:41

1

I got it. I restricted the cells as suggested in @BigBen’s comment.

From this:

For Each c In Range("A:AT")

To this:

For Each c In Range("A2:AT2")

I then deleted the corresponding buttons, and recreated them. All works beautifully now.

edited May 11 '23 at 00:41

Jeremy Caney

7,102
69
48
77

answered May 10 '23 at 18:18

Keegan

21
2

Excel VBA - Excessive Time to Complete

2 Answers2