Inefficient code taking too long to run

[Dokat]

Hi,

I have 18 macro codes similar to the one below with different cell ranges . I created a macro that runs all of them in sequence. However it's taking too long to run and very inefficient. Is there a way to speed the run time on this code.

Sub MoveRangeWBC()
Worksheets("WBC").Range("E25:E60").Copy Destination:=Worksheets("Summary").Range("E4")
Worksheets("WBC").Range("W25:W60").Copy Destination:=Worksheets("Summary").Range("F4")
Worksheets("WBC").Range("E25:E60").Copy Destination:=Worksheets("Summary").Range("E40")
Worksheets("WBC").Range("AB25:AB60").Copy Destination:=Worksheets("Summary").Range("F40")

Dim iCntr
Dim rng As Range
Set rng = Worksheets("Summary").Range("A4:F1111")

For iCntr = rng.Row + rng.Rows.Count - 1 To rng.Row Step -1

If Application.WorksheetFunction.CountA(Rows(iCntr)) = 0 Then Rows(iCntr).EntireRow.Delete

Next
With Worksheets("WBC")
.Range("W22").Copy Destination:=Worksheets("Summary").Range("A4:A34")
.Range("AB22").Copy Destination:=Worksheets("Summary").Range("A35:A65")
.Range("O18").Copy Destination:=Worksheets("Summary").Range("D4:D65")


End With

End Sub

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). It would also be helpful if you listed the other ranges.

 

[mackc557]

I'm sure it will be mentioned but have you considered starting with

Application.Screenupdating = False

and ending with

Application.Screenupdating = True

 

[Dokat]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). It would also be helpful if you listed the other ranges.

I'm sure it will be mentioned but have you considered using

Application.Screenupdating = False

Should i include that after the sub?

 

[MARK858]

Should i include that after the END sub?

Before

 

[mackc557]

Should i include that after the sub?

If you have a macro that calls those 18ish other macros, I would say put the FALSE just after the sub of the caller macro, and the TRUE just before the end sub of the caller macro.

 

[mumps]

Please note that by using one macro instead of 18, the code could possibly be designed to be more efficient. Please respond to my suggestion in Post #2.

 

[Dokat]

If you have a macro that calls those 18ish other macros, I would say put the FALSE just after the sub of the caller macro, and the TRUE just before the end sub of the caller macro.

Thanks i will give that a try

 

[Dokat]

Please note that by using one macro instead of 18, the code could possibly be designed to be more efficient. Please respond to my suggestion in Post #2.

Yes i am trying to figure out how install the add in, there is confidential data i can't share the file but iw ill see if i can make a copy and send

 

[Dokat]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). It would also be helpful if you listed the other ranges.

Hi,

I removed the confidential data and upload it to dropbox. Please see below link. Essentially first 18 macross copy and paste columns from the 18 sheets and pasting in order to Summary sheet. Let me know if you have any questions.

Dropbox - File Deleted - Simplify your life

www.dropbox.com