Macro take 40 secound to run

**cyee** · 02-08-2011, 11:05 PM

Hi All,

Sorry for a super long coding. As I record manually without any editing.
Therefore, I look forward for your help.
This coding take about 40 secound to run. Its really take too long.
Is it able to edit it to be more faster?

For your info, I will using the same coding to edit into difference customer.

Due to the code more then 10,000 characters long, therefore I paste it into txt file.

Best Regards

**nimrod** · 02-08-2011, 11:21 PM

Hello Cyee:
I don't think to many people are going to read all that code ... however if you start a post that tells people eactly what your goal is I'm would think you will get help on creating the code.

**Marcol** · 02-08-2011, 11:29 PM

Hmm?

You can get rid of all the lines like

ActiveWindow.ScrollRow = ##

Then get rid of Select
eg

    Columns("B:E").Select
    Selection.Delete Shift:=xlToLeft

becomes

    Columns("B:E").Delete Shift:=xlToLeft

Similarly with .Activate

You should record your macros in smaller chunks then add them together

Select really drags code down and is very seldom needed.

By working in smaller chunks you could possibly aviod scrolling altogether.

Remember that the Macro Recorder cannot predict what you want next so it records nearly everything you do, including selecting cells until you decide what next to do.

Your code will take a fair bit of time to clean out.
Can you supply a workbook to work with?

It should not contain any sensitive data

**MarvinP** · 02-08-2011, 11:30 PM

Hi cyee,

I've trimmed out a lot of useless stuff from your text file. Here is what I suggest.
Do your process a few times by hand so you knew exactly the keystrokes and things you need. After you have it to a minimum, then record your macro. This alone should make yoiur macro about half as long.

For example you have Selected columns C and then deleted it. You did it again to column D. You should have selected BOTH columns and then deleted them. This would make for a smaller and better macro. The same is true for AutoFit for columns. Instead of doing them separaely, select all of them and do it all at once.

Next....

Whenever you see stuff like

Columns("J:J").Select
Selection.Cut

You can make this a single line with:

Columns("J:J").Cut

Also - you need to learn to SET BREAKPOINTS and STEP through your macro to see what is useless steps and delete them. Read - http://www.cpearson.com/excel/Debug.htm

Find the attached with a lot of useless lines gone.

**cyee** · 02-09-2011, 07:11 AM

Thanks all for the help.

I able to sort a bit faster now :p

BTW, I hereby attached the result in excel file. There is alot of blank rows.
May I know how to auto delete all the blank rows?

**MarvinP** · 02-09-2011, 07:36 AM

Hi,
You should start a new question / thread with this last problem.

**cyee** · 02-09-2011, 09:08 PM

I hereby attached the result in excel file. There is alot of blank rows.
May I know how to auto delete all the blank rows?

**MarvinP** · 02-10-2011, 01:01 AM

Hi Charlene,

This code will delete those blank rows on your sheet. Set a breakpoint in it and step through it to see what it is doing.

Sub DeleteBlankRows()

    Dim RowAboveGrandTot As Double
    Dim LastRowOfData As Double
    
    RowAboveGrandTot = Cells(Rows.Count, "A").End(xlUp).Row - 1
    LastRowOfData = Cells(RowAboveGrandTot, "A").End(xlUp).Row + 1
    Rows(LastRowOfData & ":" & RowAboveGrandTot).Delete shift:=xlUp
    
End Sub