How to speed up this VBA Code?

[OpieWinston]

The below code took around 25 minutes to get to 10% complete on the first cycle, which if I did my math right, means I'm looking at more than 4 hours run time for it to get through it's first of two cycles. There are approximately 275,800 rows in this particular dataset, but the goal is to be able to use this code on both larger and smaller datasets.

How can I speed this up?

Please Login or Register  to view this content.

[6StringJazzer]

Please explain what you want this code to do.

[OpieWinston]

Identify medical claims that were denied under one treating provider, then later paid with a different treating provider listed.

[Joske920]

would it be posible to setup a sample workbook with non personal information
so we can do some testing?

[OpieWinston]

Not real sure how I'll accomplish that on such a large scale, but I'll see what I can do

[Joske920]

no need to make a lot of data
just copy 5 diferent lines
remove personal info
change name to name1 and pull down for as manny rows as you like

what is in the "A1" rang in your sheet
the macro point to that cell in every loop but its is not being updated so if that would be empty it would not run at all and if something is written in it it would endless loop
i must be missing something

[OpieWinston]

Ah shoot - only 5 or so lines? That I can do.

The macro starts in A2, then goes to the next row down for every loop until there is no value in column A (first empty row).

I'll be back with that sheet shortly

[6StringJazzer]

Using ActiveCell is not especially efficient, although it's probably not your main problem. You have disabled screen updates so that helps.

Please Login or Register  to view this content.

I would iterate using the row number and then refer directly to the row number, rather than moving ActiveCell then extracting its row number.

Please Login or Register  to view this content.

Also, why do you make two complete passes through the data, rather than doing everything in one pass?

[OpieWinston]

*FACEPALM*

While putting together the sample sheet to upload, I realized my data was formatted as a table. Converted to range, ran the code again, and flew through the code in no time at all. A couple hours lost because I didn't convert to range...ugh...

Thanks for your time and willingness to assist!

[Joske920]

well i learned something today...
didn't know that ActiveCell(2, 1).Select would change the selected cell

i interpreted is as a Cells(2, 1).Select

okay..

[6StringJazzer]

I didn't know that ActiveCell(2, 1).Select would change the selected cell

ActiveCell(2, 1) interprets ActiveCell as the upper-left cell of a range, and then allows offset addressing from it, even if it's outside the range. So if A1 is active, ActiveCell(2, 1) refers to A2. It is a kind of "tricky" programming similar to deliberately overflowing a buffer in C.