autofill a column alongside a range - how ?

I have a range established in columns G, H , I and J - a variable number of
rows.

I want to autofill columns A - F with a formula, but only for as many rows
as exist at that time in the Range described above.

Currnetly I have the formulas in the header row for columns A - F, so I can
manually drag them down the corect number of rows each time. but can I
automatate this in a VBA macro ?

Suggestions Welcome.

Sub autodrag()
lastrow = ActiveSheet.Columns("G:J").Find(What:="*", _
After:=Range("G1"), _
LookAt:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Range("A1:F1").AutoFill Destination:=Range("A1:F" & lastrow),
Type:=xlFillDefault
End Sub

Regards,
Stefi


„tonto57” ezt �*rta:

> I have a range established in columns G, H , I and J - a variable number of
> rows.
>
> I want to autofill columns A - F with a formula, but only for as many rows
> as exist at that time in the Range described above.
>
> Currnetly I have the formulas in the header row for columns A - F, so I can
> manually drag them down the corect number of rows each time. but can I
> automatate this in a VBA macro ?
>
> Suggestions Welcome.

Thans Stefi, before I try that though - is there not a brackets mismatch on
the lastrow assignment line ?

"Stefi" wrote:

> Sub autodrag()
> lastrow = ActiveSheet.Columns("G:J").Find(What:="*", _
> After:=Range("G1"), _
> LookAt:=xlPart, _
> LookIn:=xlValues, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlPrevious, _
> MatchCase:=False).Row
> Range("A1:F1").AutoFill Destination:=Range("A1:F" & lastrow),
> Type:=xlFillDefault
> End Sub
>
> Regards,
> Stefi
>
>
> „tonto57” ezt �*rta:
>
> > I have a range established in columns G, H , I and J - a variable number of
> > rows.
> >
> > I want to autofill columns A - F with a formula, but only for as many rows
> > as exist at that time in the Range described above.
> >
> > Currnetly I have the formulas in the header row for columns A - F, so I can
> > manually drag them down the corect number of rows each time. but can I
> > automatate this in a VBA macro ?
> >
> > Suggestions Welcome.

apologies - I see the bracket now

"Stefi" wrote:

> Sub autodrag()
> lastrow = ActiveSheet.Columns("G:J").Find(What:="*", _
> After:=Range("G1"), _
> LookAt:=xlPart, _
> LookIn:=xlValues, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlPrevious, _
> MatchCase:=False).Row
> Range("A1:F1").AutoFill Destination:=Range("A1:F" & lastrow),
> Type:=xlFillDefault
> End Sub
>
> Regards,
> Stefi
>
>
> „tonto57” ezt �*rta:
>
> > I have a range established in columns G, H , I and J - a variable number of
> > rows.
> >
> > I want to autofill columns A - F with a formula, but only for as many rows
> > as exist at that time in the Range described above.
> >
> > Currnetly I have the formulas in the header row for columns A - F, so I can
> > manually drag them down the corect number of rows each time. but can I
> > automatate this in a VBA macro ?
> >
> > Suggestions Welcome.

Spot On ! thank you Stefi.

"Stefi" wrote:

> Sub autodrag()
> lastrow = ActiveSheet.Columns("G:J").Find(What:="*", _
> After:=Range("G1"), _
> LookAt:=xlPart, _
> LookIn:=xlValues, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlPrevious, _
> MatchCase:=False).Row
> Range("A1:F1").AutoFill Destination:=Range("A1:F" & lastrow),
> Type:=xlFillDefault
> End Sub
>
> Regards,
> Stefi
>
>
> „tonto57” ezt �*rta:
>
> > I have a range established in columns G, H , I and J - a variable number of
> > rows.
> >
> > I want to autofill columns A - F with a formula, but only for as many rows
> > as exist at that time in the Range described above.
> >
> > Currnetly I have the formulas in the header row for columns A - F, so I can
> > manually drag them down the corect number of rows each time. but can I
> > automatate this in a VBA macro ?
> >
> > Suggestions Welcome.