Macro optimization for speed

**mlegge04** · 12-04-2012, 11:59 AM

Hi below I have a working but non-elegant macro by any means. I have a feeling the speed issues are related to having the ranges set all the way to the end of the workbook as opposed to the last row of data. Any improvements would be greatly appreciated. I have highlighted what I believe to be problematic areas.

Thanks

Please Login or Register  to view this content.

**TMS** · 12-04-2012, 12:09 PM

I use:

Please Login or Register  to view this content.

to determine the last row of data. Use a column that will always have entries in every row, for example a key/ID field.

Then replace all references to 1048575 with lLR.

For example

Please Login or Register  to view this content.

Regards, TMS

**JOHN H. DAVIS** · 12-04-2012, 12:18 PM

You're probably right. Did you try to modify them:

ActiveSheet.Range("$A$2:$BM$" & Lastrow).RemoveDuplicates Columns:=3, Header:= _
xlYes

ActiveCell.FormulaR1C1 = "=COUNTIF(R[1]C:R[" & Lastrow & "]C,"">0"")"

ActiveSheet.Range("$AK$1:$AK$" & Lastrow).AutoFilter Field:=1, Criteria1:= _
"<>*fill*", Operator:=xlAnd

And so on.

**mlegge04** · 12-04-2012, 01:07 PM

Hi John,

I applied your method it did work, shaved a little bit off the run-time not as much as i had been hoping for though unfortunately.

Thank you though
Much appreciated!

**Norie** · 12-04-2012, 01:11 PM

Try changing the range for RemoveDuplicates.

Also, get rid of Select.

**JOHN H. DAVIS** · 12-04-2012, 01:58 PM

You're welcome. Hope you take some of the other advice as well.

**TMS** · 12-07-2012, 10:20 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save