VBA: Fill Columns until Blank Cell in Another Column

[larinda4]

Hi guys,

I have my code below. I need to "auto fill" my formulas in cells H2:P2 down until it hits the first blank row. Unfortunately, the code I'm using will auto fill until the last row used in the sheet. I have inserted a blank row between data where I would need the code to stop. I have it referencing column G currently.

Any tips?

Here is my code. I have 2 that I've been playing around with:
Dim lastrow As Long
lastrow = Range("G" & rows.Count).End(xlUp).Row
Range("H2:P2").AutoFill Destination:=Range("H2:P" & lastrow), Type:=xlFillDefault

and the other one:
With Sheets("Code")
.Range("H2:P2").AutoFill .Range("H2:P" & .Cells(.rows.Count, "G").End(xlUp).Row)
End With

 

[Fluff]

How about

Range("H2:P2").AutoFill Destination:=Range("H2:P2").End(xlDown), Type:=xlFillDefault

 

[larinda4]

How about

Range("H2:P2").AutoFill Destination:=Range("H2:P2").End(xlDown), Type:=xlFillDefault

I'm getting a runtime error 1004.

 

[Fluff]

Oops, it should be

Range("H2:P2").AutoFill Destination:=Range("H2:P2", Range("H2:P2").End(xlDown)), Type:=xlFillDefault

 

[larinda4]

Oops, it should be

Range("H2:P2").AutoFill Destination:=Range("H2:P2", Range("H2:P2").End(xlDown)), Type:=xlFillDefault

Thanks for your help! It's still filling the formulas all the way to the bottom of the sheet and not stopping at the blank row.

 

[Fluff]

Is there any data in H3?

 

[larinda4]

Is there any data in H3?

There's no data in H3. That's why I was trying to use column G as a reference for when to stop pulling the formulas down the column (H:P)

 

[Fluff]

If you want to fill the formula down to the last used row in col G, then your code should do that.

 

[Fluff]

If you are trying to fill the formula down until the 1st blank cell in col G then try

lastrow = Range("G2").End(xlDown).Row

 

[larinda4]

If you are trying to fill the formula down until the 1st blank cell in col G then try

lastrow = Range("G2").End(xlDown).Row

You're the best!! It works perfect!

So the problem was is that I was referencing Column G and not specifically G2? Referencing the entire column would put me to the last row in the sheet, where as G2 would bring me down until the blank line?