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

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

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,

"Tony P." wrote:

> 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
>
>

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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Tony P." <Tony [email protected]> wrote in message news:[email protected]...
> 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
>
>

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

"David" wrote:

> 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,
>
> "Tony P." wrote:
>
> > 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
> >
> >

wow, this is what i was searching for. can you please tell me how to do to it for multiple columns. i have to copy formula in several columns in the same way

thanks arlu. I'm starting a new thread