Excel VB-Copy formula down until adjacent cell (left) is blank?

[Tony P.]

Here is exactly what I am trying to do through VB in Excel:

Weekly data pull fills colums A:G. Row count is always different. I am
modifying the data pull through VB, and I have a VLOOKUP formula in cell H2.
What I want VB to do is copy that formula down column H to the last row (with
data) each week. I guess I want it to be dynamic so that as rows
decrease/increase the formula is only copied down to the final row/record.

I know someone out of this smart group will know how to do this!

Thanks in advance!

Tony

 

[David]

Hi,
Try this, hope it works for you.
Sub Macro1()
Range("G1").Select
Selection.End(xlDown).Select
LastRow = ActiveCell.Row
Range("H2").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1:A" & (LastRow - 2)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(-1, 0).Range("A1").Select
End Sub

Thanks,

 

[Ron de Bruin]

Hy Tony

You can try this macro

Sub test()
Dim LastRow As Long
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("H2").AutoFill Destination:=.Range("H2:H" & LastRow) _
, Type:=xlFillDefault
End With
End Sub

It will fill the formula in H2 to H last cell in column A

 

[Tony P.]

Worked like a charm, David! Thanks!!!!! Have another one for you when you
get a second. Subject will be "Delete Entire Row"