VBA Shortcut issue

[CelestialMind7]

Hi guys,

I've searched for an answer to my question but don't seem to have any luck since this is somewhat of a unique problem.

I have a very extensive vba program that I've designed for my company. I figured that before I'd start adding even more to it that it would be a good idea to simplify what I've already built by replacing some of the code with what I've read online "should speed up the total vba run. To use one instance for example, I've replaced:

Columns("L:L").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

with

Columns("L:L").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove


I've also done this for several copy/paste commands and many other. My problem is - that instead of these one liners speeding up the program, its actually running SLOWER. I've tested a smaller scale run up until the point to which I've made the changes to and original ran in 11:20 seconds and the "improved" code ran in 15:40. That's a huge difference and completely deceives the purpose of these so called "shortcuts". Does anyone have any thoughts as to why this could be happening?

Thanks,

Chris

 

[RoryA]

Yes, I actually read online that any "Application." functions slow it down too so I created 3 new modules and merged 12 into 3 to cut down on that time as well. You think that has anything to do with it? Maybe I should redo/recopy the merges into fresher modules? Thoughts?

What exactly does all this mean?

 

[CelestialMind7]

can't explain the difference, other than that every time you run it your code makes your file larger by adding cloumns
I assume you already put this in your code:
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

it's the same amount of data and shouldn't be filtering out more than what the original code did

Also, yes I turned off screen updating but not calculation because at some point in the past I believe I was sacrificing the integrity of the data when I tried that.

What exactly does all this mean?

things such as Application.GoTo slow it down. I have merged a code that span across 12 modules into 3 to eliminate my main module calling for other modules as it runs.

 

[Repush]

if calculation is on automatic, inserting columns is slowing you down heavily
no problem turning it off, only important to recalculate when you change cell values in your code
in that case you can insert Application.Calculate, CalculateFull, CalculateFullRebuild or CalculateUntilAsyncQueriesDone