Help speeding/cleaning my lengthy macro

I am very new to the InStr and this was what I was able to create, but it is slow. Is there something else I should have used? How do I speed it up?

Please Login or Register  to view this content.

Part two

Please Login or Register  to view this content.

I wondered about that, but since each search goes has a different destination, i didn't know where to begin.

OK, if then Else is new to me, so I'll explore that, but can I put the False at the start at the beginning of my very first macro and then at the end of the very last, or do I need to do it in each macro.

Originally Posted by scottiex

1) do all the work inside an array
2) find the lastrow so you don't accidentally get 1 million rows

Please Login or Register  to view this content.

Hey Scottie, where would that go?

Bakerman2,

i attached a workbook that shows what I start with and then what I end up with. The number of items listed under each person in the pivot table may change so an exact position isn't possible.

Please Login or Register  to view this content.

Looping cells in a range is the absolute slowest way to evaluate contents. Sometimes its the best way regardless, because its easy to implement and isnt prone to issues.

You could speed things up by using range.find instead, and if need be in a loop range.findnext. Basically find returns a range object, you set that to a variable and then do a range.value = range.value to transfer its value to another cell. Ironically I see now you use this in the next sub.

Depending on your data set you may also benefit from filter instead (either auto or advanced). You can filter on partial strings and then take the results and move them together (assuming you need more than 1 match for each term you seek).

Also, For loops are good for when you need to do something x number of times regardless of the result in each iteration. If you need to loop until something is found or while something is true/false, instead of using exit for to leave the loop, just use a Do while or Do until loop. This will allow the loop to only iterate as many times as required.

Changing references for ranges to be only the required range instead of full columns will help too, especially when using a for loop. In multiple places I see H:H instead of H2:H100 for example. If your looping every cell in H:H thats a million cells to check instead of 100, 1000, etc. Do this 20 times and its 20 millions cells to loop instead of 20,000. Also using constants can speed up code. Set a constant string for your range and then use the constant instead of quoted text strings manually entered multiple times. Use constants for text strings you repeat elsewhere in the code too.

copy/paste is slow and sometimes buggy. Instead do range.value = range.value. You can combine that with copy formatting or just code to format the destination.

Use variables for objects like sheets and workbook instead of repeatedly doing worksheets("")... do a worksheet object and assign it to a variable.

Turning off screenupdating will improve speed and so will turning off automatic calculation until your macros complete.

Overall, my guess is the biggest issue is the combination of whole column references combined with for loops checking each cell in those columns. Fix that and your code should run much faster.