A More Effficient Way to Iterate through and Show/Hide Columns?

[swatkinsjr]

Hi,

I have some VBA I wrote that will iterate through a range of columns and, based on the value in a given row, will show or hide that column. The problem is, the number of columns I'm going through is quite large, and running the macro takes forever, and shuts down my system. It's literally faster to manually identify the columns to show and hide and do it myself.

So, my question is: is there a more efficient way to do this?

Here's the code I have now (ws is Worksheet object referencing the current tab):

Please Login or Register  to view this content.

As you can see from above, the columns are all dated, and each day I'm hiding the columns that are not for today and showing only the current day's columns.

Any help on this would be appreciated!

Thanks,

Stephen

[JosephP]

are there usually more columns to hide or show? have you turned off screenupdating while running the code?

[swatkinsjr]

are there usually more columns to hide or show? have you turned off screenupdating while running the code?

No, the number of columns usually stays pretty static. The only update, really, is the calculation for which ones are today's date.

No I did not turn off screenupdating. I didn't know to do that. You think that'll speed up the code?

[JosephP]

I meant do you normally hide more of the data columns, or show more of them? it may be quicker to hide them all to start with then only show today's ones

Please Login or Register  to view this content.

[swatkinsjr]

Ah. Well, yes, most of the columns are hidden. Only a handful should show for any given day.

Thanks! I'll make those changes and report back the results.

[JOHN H. DAVIS]

Maybe?

Please Login or Register  to view this content.

[Norie]

Are the columns to show grouped together?

Is it always the same no of columns?

Is there a way to identify the first column to hide, eg a header?

[snb]

Please Login or Register  to view this content.

[swatkinsjr]

Awesome. I think turning off Screenupdates was the thing. It works like a dream, now, and takes less than a couple seconds.

Thanks!