Speed up a macro

Attached is a workbook that takes an imported text file, inserts rows with text.
It is SLOW, you can watch each line being inserted with the text, I would think with such a small sample dataset it would be much much quicker.

Information: See attached xls file.
On sheet1 is the imported data, sheet2 is a copy of the imported data so one can copy and paste to sheet1 as needed with out re-importing for test purposes and only for this query. (Sheets2 thru 3 are not used otherwise).

There is a command button on sheet one which will run macro "aaa". If you run this you will see how slow it is and exactly what it is doing.

I am simply looking for a way to speed this up, I have some files that are 10 times the size of the sample data and they take 10 or more minutes to run.

Be aware! I am not a professional VBA programmer, but I did stay at a Holiday Inn once.

Any hints, tips or examples are welcome.

Edit: It appears I did not link the commandbutton to macro "aaa", please run macro "aaa" manually instead.

You've commented out the piece of code at the start that turns screen updating off. This can make a big difference speed wise when running code as Excel is not having to redraw the screen all the time.

Dom

I did that on purpose and it doesn't help at all.

Thank you for looking.

p.s.
I borrowed your sig

I'm surprised it makes no difference. I'm leaving the office now but will have a look at the rest of your code later if I can drag myself away from the pub.

Dom

Originally Posted by Rick_Stanich

I did that [commented out ScreenUpdating = False] on purpose and it doesn't help at all.

Rick, disabling repaint always makes a difference... the very reason you can "see the rows being inserted" is because it is enabled to start with.

Insertion / Removal of rows is Volatile action and triggers events / calculation etc ... on that basis it's always a good idea to toggle these settings also, eg:

Please Login or Register  to view this content.

I will let Dom go through the code once he's had a few jars but the above would be my quick 2cents...

(EDIT: having just had a gander - avoid Selecting objects - this will slow your code also...see: http://blogs.msdn.com/excel/archive/...practices.aspx)

Damn brilliant!
Simply removing ".EntireRow.Select" made a significant difference in speed. Significant indeed.
I will also use your snippets of code in my projects.

Domski, Donkeyote
Thanks!

Edit:
Went from 17 seconds to a few seconds for this sample dataset!

hi,

Edit: I should refresh my screen more regularly before posting!
Anyway, even though DO's beaten me to the punch, & you've got an initial improvement, I'll see if I can do even more shortly...
/end edit

I'm as surprised as Dom that turning off the screen updating makes no difference?
Anyway, to save Dom tearing himself away from the pub...

My first thought was that you could probably get a reasonable speed improvement if you change to using a variant array approach to take the data into memory, work through it, make any changes, then write back to the sheet (here's an example: http://www.excelforum.com/2229367-post4.html). BUT, from glancing at your code, I see that you are inserting rows if some conditions are met - these mean I need to think about how to implement a variant array approach... which I am now, I'll repost if I can make something work.

With your code as it currently is I suggest:
- changing all ".value" sections to ".value2" (see http://www.decisionmodels.com/Calcse...tm#performance)
- change code to prevent activation/selection where possible eg

Please Login or Register  to view this content.

- perhaps use a select case rather than multiple If statements for the testing
- can the loops (or at least 2 of them) be merged?
- Could you use the autofilter & SpecialCells(xlCellTypeVisible) to remove the need for looping?
(try recording a macro of your actions & then modifying it for the different strings)

hth
Rob

broro183
I will give your suggestions/examples a try.
Thanks for the info!!

All but one area could be worked out via your example.

Please Login or Register  to view this content.

I tried a couple variants of your example which would not place the text "(QUA_1)", it would insert the new row, just no text.

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

With the new changes, the code is even quicker!

hi Rick,

Yes, I had a few problems too - I haven't merged any of the loops yet but this seems to work so far...
- I may have gone slightly overboard with my use of With statements as well ;-)

Please Login or Register  to view this content.

hth
Rob