Huge favor/help required with complicated Macros

minette

Board Regular
Joined
Jul 8, 2005
Messages
237
Hi everyone, I have a huge favor to ask. I have a macro which is running extremely slow (for days) and then timing out. It is processing 40000 plus rows of data, taken from 7+ files. Part of the process is to look at several different cells in each row, and then doing something with them. It goes row, by row.... I don't want to post the code here, because it is over 1850 rows long (and also very ugly). I am a complete novice, hoping to learn as much as possible.

If anyone is able to have a look for me, then please let me know and I will PM the code and files for you to have a look.

This is causing me all sorts of problems, as I need it for work, and just cannot get it to run properly.

Your help would really be very much appreciated.
Minette
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
My first question would be: Are there lots of “Select” or “Activate” commands in the code? These are the biggest culprits of causing code to run slowly.

As an example, the following:
Code:
Range(“A1”).Select
Selection.Copy
Range(“A2”).Select
Selection.PasteSpecial

Can be reduced to:
Code:
Range(“A1”).Copy Destination:= Range(“A2”)

This will run much faster and reduces 4 lines of code to 1.
 

ADAMC

Well-known Member
Joined
Mar 20, 2007
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
Can you post a sample of your code so we can get an idea of how 'ugly' it actually is, perhaps you could post the code in blocks?
 

minette

Board Regular
Joined
Jul 8, 2005
Messages
237
Hi ADAMC

I have PM'd it to you. See what you think.


Lewiy - I do have a lot of "select" etc, as most of it was done using "record macro" and then manipulated afterwards. I'll try and clean it up and see what it does.
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Lewiy - I do have a lot of "select" etc, as most of it was done using "record macro" and then manipulated afterwards. I'll try and clean it up and see what it does.

Holy cr@p, 1850 lines of macro recorder code???!!! I think that it's probably going to need some serious cleaning. Even without looking at what you have, I imagine you could probably bring it down to a quarter the size if not smaller with just some some simple streamlining.

Good luck ADAMC!!
 

ADAMC

Well-known Member
Joined
Mar 20, 2007
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
Eidt**

LOL Lewiy, i need all the luck and help i can get!**


Well....................

I know you said alot of code but :biggrin: this is alot!


As Lewiy stated firstly you have an awful lot of 'selecting' which can be scrapped.

These pieces of code concern me the most:
Code:
Else 
If LastRowToBook > 54001 And LastRowToBook < 56000 Then 
Workbooks("Book222.xls").Activate 
.Copy Worksheets("VALIDATION").Range("A17:AI2000") 
.Copy Worksheets("VALIDATION").Range("A2001:AI4000") 
.Copy Worksheets("VALIDATION").Range("A4001:AI6000") 
.Copy Worksheets("VALIDATION").Range("A6001:AI8000") 
.Copy Worksheets("VALIDATION").Range("A8001:AI10000") 
.Copy Worksheets("VALIDATION").Range("A10001:AI12000") 
.Copy Worksheets("VALIDATION").Range("A12001:AI14000") 
.Copy Worksheets("VALIDATION").Range("A14001:AI16000") 
.Copy Worksheets("VALIDATION").Range("A16001:AI18000") 
.Copy Worksheets("VALIDATION").Range("A18001:AI20000") 
.Copy Worksheets("VALIDATION").Range("A20001:AI22000") 
.Copy Worksheets("VALIDATION").Range("A22001:AI24000") 
.Copy Worksheets("VALIDATION").Range("A24001:AI26000") 
.Copy Worksheets("VALIDATION").Range("A26001:AI28000") 
.Copy Worksheets("VALIDATION").Range("A28001:AI30000") 
.Copy Worksheets("VALIDATION").Range("A30001:AI32000") 
.Copy Worksheets("VALIDATION").Range("A32001:AI34000") 
.Copy Worksheets("VALIDATION").Range("A34001:AI36000") 
.Copy Worksheets("VALIDATION").Range("A36001:AI38000") 
.Copy Worksheets("VALIDATION").Range("A38001:AI40000") 
.Copy Worksheets("VALIDATION").Range("A40001:AI42000") 
.Copy Worksheets("VALIDATION").Range("A42001:AI44000") 
.Copy Worksheets("VALIDATION").Range("A44001:AI46000") 
.Copy Worksheets("VALIDATION").Range("A46001:AI48000") 
.Copy Worksheets("VALIDATION").Range("A48001:AI50000") 
.Copy Worksheets("VALIDATION").Range("A50001:AI52000") 
.Copy Worksheets("VALIDATION").Range("A52001:AI54000") 
.Copy Worksheets("VALIDATION").Range("A54001:AI" & LastRowToBook + 17) 
Else


There are masses of them, i know you said you get an error message of 'section is to large but even so, the amount of data you have in this format is scary :)

I am not sure if there any any limits on the amount of data which can be copied so i have posted the code you sent me so one of the gurus may help, i am certain this can be cut down greatly!

Also is the macro run on the active worksheet:
Code:
.Copy Worksheets("VALIDATION").Range("A4001:AI6000")

could be
Code:
Range("A4001:AI6000").copy

There are many things like this which need to be changed...
 

ADAMC

Well-known Member
Joined
Mar 20, 2007
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
Heres an example of what i mean:

Why cant:

Code:
.Copy Worksheets("VALIDATION").Range("A17:AI2000") 
.Copy Worksheets("VALIDATION").Range("A2001:AI4000") 
.Copy Worksheets("VALIDATION").Range("A4001:AI6000") 
.Copy Worksheets("VALIDATION").Range("A6001:AI8000") 
.Copy Worksheets("VALIDATION").Range("A8001:AI10000") 
.Copy Worksheets("VALIDATION").Range("A10001:AI12000") 
.Copy Worksheets("VALIDATION").Range("A12001:AI14000") 
.Copy Worksheets("VALIDATION").Range("A14001:AI16000") 
.Copy Worksheets("VALIDATION").Range("A16001:AI18000") 
.Copy Worksheets("VALIDATION").Range("A18001:AI20000") 
.Copy Worksheets("VALIDATION").Range("A20001:AI22000") 
.Copy Worksheets("VALIDATION").Range("A22001:AI24000") 
.Copy Worksheets("VALIDATION").Range("A24001:AI26000") 
.Copy Worksheets("VALIDATION").Range("A26001:AI28000") 
.Copy Worksheets("VALIDATION").Range("A28001:AI30000") 
.Copy Worksheets("VALIDATION").Range("A30001:AI32000") 
.Copy Worksheets("VALIDATION").Range("A32001:AI34000") 
.Copy Worksheets("VALIDATION").Range("A34001:AI36000") 
.Copy Worksheets("VALIDATION").Range("A36001:AI38000") 
.Copy Worksheets("VALIDATION").Range("A38001:AI40000") 
.Copy Worksheets("VALIDATION").Range("A40001:AI42000") 
.Copy Worksheets("VALIDATION").Range("A42001:AI44000") 
.Copy Worksheets("VALIDATION").Range("A44001:AI46000") 
.Copy Worksheets("VALIDATION").Range("A46001:AI48000") 
.Copy Worksheets("VALIDATION").Range("A48001:AI50000") 
.Copy Worksheets("VALIDATION").Range("A50001:AI52000")

Just be

Code:
Range("A17:AI52000").Copy
 

minette

Board Regular
Joined
Jul 8, 2005
Messages
237
Hi ADAMC

Thanks for having a look. I get the "selection is too large" if I use
Code:
Range("A17:AI52000").Copy

This is why I had to break it down in the first place, as I did have it as just a few lines in the beginning.
 

ADAMC

Well-known Member
Joined
Mar 20, 2007
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
Strange, as it worked fine for me.......Try putting the code
Range("A17:AI52000").Copy
in a sub on its own and see what happens...?

Fird thing i would do is go through the code and half them again...

if
Code:
Range("A17:AI52000").Copy

doesnt work, try:
Code:
Range("A17:AI26000").Copy

then repeat for the rest of the selection. If we can shorten the code down
a little we can start to give it a major overhaul :)
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
minette

If you want help with this code it really would be a good idea to post at least a sample of it here.

I don't see why you need to use PMing, if you post on the forum then you'll get input from more people.:)
 

Forum statistics

Threads
1,181,404
Messages
5,929,750
Members
436,687
Latest member
Glass of Gin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top