Macro very slow while generating output

[sarat]

Hello Everyone..
Taking the help of forum and net, one macro has been created and I am getting exact output (in Overview sheet) by capturing data from sheet ('Data'). But when more than 20000 records are there, the macro is taking much more time to generate output. Can anyone check and change wherever required so that it will run much more faster.
I am deleting last record in output as it is not matching with the count. How to solve if possible

Your help will be highly appreciated

[fredlo2010]

Hi Sarat,

You have a huge code there. I am not a VBA Expert but I will give you some advice that will make your VBA Experience a lot better.

1. Read some information about speeding up macros online. It will give you an idea on how to improve your macros. This is my favorite, its easy to read, clean and very simple to understand http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm

2. Always try to modularize. You have a spread sheet with about 320 lines in it. This makes it extremely hard to read, understand, debug. For example if you have a piece of code that copies the data, then out it in a module and then call it when you need it. If you have another one that will create the headers and the special formatting do the same. Talking from experience I can tell you got the code from tons of sources (own experience) you probabb;y can separate them into different modules. You can research about this online. But you get the idea right?

3. Use F8 key to step through your code line by line. This will allow you to see whats going on. For example this technique made me discover where the bottle neck was in your code.

Sp the issue was with the formulas used in the overview sheet. You were using the whole column as a range, this is a big no. Always use the ranges you need. So I just modified the formula with "lastrow" variable you declared before and that's it.

Here is the piece of code modified

[VBA].Range("G1").Value = "COUNT"
.Range("G2").Resize(lastrow - 1).FormulaR1C1 = "=SUMPRODUCT(--(RC[-6]=R2C[-6]:R" & lastrow & _
"C[-6]),--(RC[-5]=R2C[-5]:R" & lastrow & _
"C[-5]),--(RC[-4]=R2C[-4]:R" & lastrow & _
"C[-4]),--(RC[-2]=R2C[-2]:R" & lastrow & _
"C[-2]),--(RC[-1]=R2C[-1]:R" & lastrow & _
"C[-1]))"

.Columns("G").Value = .Columns("G").Value

.Range("H2").Resize(lastrow - 1).FormulaR1C1 = "=SUMPRODUCT(--(RC[-7]=R2C[-7]:RC[-7])," & _
"--(RC[-6]=R2C[-6]:RC[-6])," & _
"--(RC[-5]=R2C[-5]:RC[-5])," & _
"--(RC[-3]=R2C[-3]:RC[-3])," & _
"--(RC[-2]=R2C[-2]:RC[-2]))"

.Rows("1:1").Insert Shift:=xlDown
.Range("H1").Value = "Temp"
.Range("H2").Value = 1
Set rng = .Range("H1").Resize(lastrow + 1)
rng.AutoFilter Field:=1, Criteria1:="<>1"[/VBA]

[sarat]

Thanks for your valuable feedback

[Bob Phillips]

It is hard to say as the example you post is very quick, but you need to dissect it.

Break the code down into functional units and add timers, then displayb te elapsed time and find the bottlenecks. Then get to work on them, maybe you can design things differently, code differently, or whatever.

[jolivanes]

Also

Change this

[vba]
Range("h4:h65000").Select '<---- Is H65000 required?
Selection.Cut
Range("i4").Select
ActiveSheet.Paste
[/vba]

to

[vba]
Range("H4:H65000").Cut Destination:=Range("I4")
[/vba]

or this

[vba]
[H4:H65000].Cut Destination:=[I4]
[/vba]

Get rid of all the Selects where possible and change to similar like above.

like here

[vba]
Range("i14:i4000").Select
Selection.Clear
[/vba]