VBA Takes Over 1 Hour To Run

[Nanogirl21]

I have 1 macro that calls several other macros. I am calling instead of running each code separately because I’d like to run everything with the push of 1 button. I have the call set up like this:

  Sub RUN_ALL()

   

      OptimizeCode_Begin

      01_IMPORT

      02_FIND_DATE

      03_ROLE

      04_NEW

      05_ADD

      06_STATUS_FIX

      07_LEVEL

      08_ FORMATTING

      09_E2

      10_E2_UPDATES

      11_ FORMATTING2

      12_LAST

      13_HIDE

      Save_EXCEL_Files

      OptimizeCode_End

      Complete_Message


  End Sub

When running this macro the time to complete is almost 1 hour + 30 minutes. During this time my computer freezes and becomes very slow. However, if I run each macro 1 by 1 OR run this single call macro by using F8 (step into) the entire process takes no longer than 10 minutes (still a long time, but a whole lot better than 90 minutes).

Any idea on why pne way or running the macros is faster than the other?

Any code that I can add to make things faster?

I have to run this process 3 times a day so having this move faster is very important.


I am using an OptimizeCode_Begin and OptimizeCode_End, but it doesn’t seem to help at all.

  Sub OptimizeCode_Begin()
      

      Application.ScreenUpdating = False

      Application.ErrorCheckingOptions.BackgroundChecking = False

      
      EventState = Application.EnableEvents
      Application.EnableEvents = False
      
      PageBreakState = ActiveSheet.DisplayPageBreaks
      ActiveSheet.DisplayPageBreaks = False
      
  End Sub

  Sub OptimizeCode_End()
      
      ActiveSheet.DisplayPageBreaks = PageBreakState
      Application.EnableEvents = EventState
      Application.ScreenUpdating = True
      Range("A1").Select
  End Sub

 

[Johnny C]

Hi Nanogirl

If you hit ctrl-end it takes you to what Excel thinks is the last used cell in the sheet. if it's not, say someone copied 100k rows and then deleted the data, Excel still thinks they're used. So if you're sorting a column and it thinks there's data to the last possible row it can take a long time.

To fix, go to the lowest cell with data in that you can find. Then ctrl-shift down arrow to select to the bottom of the sheet. Delete the rows. Do the same with columns if it's a problem. Then select cell A1 and save. this forces XL to reset the last known cell. I think in newer versions selecting A1 may not be needed but I'm not sure.

 

[Nanogirl21]

How are you removing duplicates? the fastest way to do that is mark each duplicated row in a spare column at end, then sort on this column so they are all at the bottom then delete the entire range in one go. If you are deleting line by line that will be sloooow.

    ActiveSheet.Range("$A:$Z").RemoveDuplicates Columns:=1, Header:=xlYes

 

[offthelip]

That will be slow, because every time a row is deleted EXCEL has to move every row below that up one row and it does that for every duplicate, I suggest filtering on duplicates and doing as i suggested above

 

[JLGWhiz]

That will be slow, because every time a row is deleted EXCEL has to move every row below that up one row and it does that for every duplicate, I suggest filtering on duplicates and doing as i suggested above

I don't believe that the 'RemoveDuplicates' function loops row by row. That function is pretty fast.