How To Use UNION and Ranges To Speed Up Deleting Many Columns?

[eryksd]

Trying to use Union and ranges to speed up deleting empty columns, but going wrong somewhere with the code.

I read here that deleting columns individually slows down performance significantly. Script speed can be improved by defining a "master" range to include all the ranges (columns) to be deleted (by using Union), and then simply deleting the "master" range.

My old (slow) script that works, but takes about about 3 hours to run across ~30 sheets and deleting ~100 columns each sheet. Using Union is supposed to make the process run in seconds instead of hours, was hoping someone could help me figure out what I'm doing wrong with my code. When I run it, nothing happens... not sure what's going on.

Any help would be greatly appreciated!

Please Login or Register  to view this content.

[AlphaFrog]

I think you're just missing a couple of S's in .Columns()

Please Login or Register  to view this content.

[Olly]

Errors corrected (in red)

Please Login or Register  to view this content.

Better?

Oh, and a hint for fault finding - disable your error handling and step through the code line by line (press F8 in the VBE) to see where it fails...

[Trebor76]

You say there's 30 sheets yet your code is specifically dealing with a tab called Ball Shaker.

Try this where I've added some comments:

Please Login or Register  to view this content.

I hope this helps!!

Regards,

Robert

[eryksd]

I think you're just missing a couple of S's in .Columns()

Thank you, totally missed that!!

Errors corrected (in red)

Better?

Oh, and a hint for fault finding - disable your error handling and step through the code line by line (press F8 in the VBE) to see where it fails...

Thanks m8t! Totally missed the 's' in Columns. - I'll start using the F8 technique as well, didn't think about that

You say there's 30 sheets yet your code is specifically dealing with a tab called Ball Shaker.

Try this where I've added some comments:

Please Login or Register  to view this content.

I hope this helps!!

Regards,

Robert

Thank you Robert! Is there any advantage to using h = wsMySheet.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
over h = wsMySheet.Range("E1").End(xlToRight).Column ?

-----------------------

Thank you everyone for the replies! I've used your suggestions to fix the errors in the code and have it run across multiple sheets (except "AA" and "Word Frequency"). It runs!... though I'm still new to VBA and pretty much macguyvered this code, including some statements to ignore errors... hope I'm not setting myself up for any bugs anywhere.


Working code (across many sheets. Runs in ~6 min vs. hours using old method):

Please Login or Register  to view this content.

[Trebor76]

Thank you Robert! Is there any advantage to using h = wsMySheet.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
over h = wsMySheet.Range("E1").End(xlToRight).Column ?

Yes - say you have data in columns E, F and G then no data across columns H, I and J but then some more data in column K your initial code will set column G as the last column where mine will set column K as the last column.

Glad to hear the processing time has been dramatically reduced

Regards,

Robert

[eryksd]

Yes - say you have data in columns E, F and G then no data across columns H, I and J but then some more data in column K your initial code will set column G as the last column where mine will set column K as the last column.

Glad to hear the processing time has been dramatically reduced

Regards,

Robert

Ahh I see, thank you Robert! That code will definitely come in handy in the future