Find/Count Cell until first empty cell

[penbeacho]

I have a spreadsheet with data columns formatted as follows:

Title
1
2
3
4
5
(Blank Cell)
Verbage

I used excel recorder and generated the following code:

'Enter Formulas into CarAllow Sheet
Range("E2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(C[-2],C[3]:C[4],2,0)"
Range("E2").Select 'CHECK RANGE
Selection.AutoFill Destination:=Range("E2:E74") 'CHECK RANGE VLOOKUP
Range("E2:E74").Select 'CHECK RANGE VLOOKUP
Range("F2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F74") 'CHECK RANGE
Range("F2:F74").Select 'CHECK RANGE
Range("J2").Select
ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-2],R2C3:R7000C6,4,FALSE),0)"
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J855") 'CHECK RANGE IFERROR, VLOOKUP
Range("J2:J855").Select 'CHECK RANGE IFERROR, VLOOKUP
Range("K2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=0,RC[-2],RC[-1])"
Range("K2").Select
Selection.AutoFill Destination:=Range("K2:K855") 'CHECK RANGE IF STATMENT
Range("K2:K855").Select 'CHECK RANGE IF STAMENT

This code works well for my specific first run of data, yet the data is merged from an ever changing database. Therefore the number of data sets will change through time. The lower range limits are correct, yet the upper ones should be variable depending on the imported data size.
For example:

Selection.AutoFill Destination:=Range("E2:E74") 'CHECK RANGE VLOOKUP
Range("E2:E74").Select 'CHECK RANGE VLOOKUP

For this code E74 should be the last cell before the blank and not the fixed 74 value. Would dropping the 74 part do this? Or is there a way to count the column data before the first blank space and insert as "E"&N, where N=last cell before blank.

I hope this is clear.

[TMS]

Try:

Please Login or Register  to view this content.

Regards, TMS

[penbeacho]

In this formula you are replacing the 1LR variable and applying it to the second range dimension. Would you keep them like that or maybe name the second 1LR a diferent thing? Im trying to understand the logic in the code.

[TMS]

Dohhhh ....

In your example, the first two ranges went down to 74. the second two ranges went down to 855. The lLR variable is only needed long enough to set up the formulae, so I used it for the first two ranges and then updated/replaced the value for the second two ranges.

In fact, I'm wrong, in that I should have been using a column that is populated, not the cells that will receive the formula ... I confused myself by looking at the two numbers .

So, in fact, it should probably be something like this:

Please Login or Register  to view this content.

Hope that makes more sense.

Regards, TMS