Macro--Do While Loop--Autofill

[hanzsolo]

I'm trying to do a 'do-while' loop that only works as long as there is a line with data. I know I can write the macro to do the formula into the cell and do continuous loops for each cell. My problem is the report I get daily is 20,000-30,000 lines long, and length is different each day.

Right now I wrote the macro to have an autofill for 40,000 lines because I know the report will never exceed that length. I run a pivot table off of this report, then do a vlookup to a different file where I track all the data weekly. I just discovered that my pivot table records the number of "blank" cells, which is 40,000 lines minus the length of the report. I am trying to eliminate this blank count so that my grand total won't count that blank data.

My macro adds 10 columns to the end of the report, runs conditional statements and my pivot tables record the totals of the conditions I set. If I were to write the do while loop for each cell I would have to write 40,000*10= 400,000 lines of code, obviously I don't want to do that.

Is there a way to run a do-while loop that will only fill in my conditional statements as long as there is data in the cell? Here is the autofill code i have:

Range("AU2").Select
Selection.AutoFill Destination:=Range("AU2:AU40000")

This code runs through column BD

 

[VoG]

Try like this

LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Range("AU2").AutoFill Destination:=Range("AU2:AU" & LR)

 

[hanzsolo]

Unbelievable--works!!

This message board is my new favorite thing