autofill a column alongside a range - how ?

[tonto57]

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.

 

[Stefi]

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:

 

[tonto57]

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

 

[tonto57]

apologies - I see the bracket now

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:

 

[tonto57]

Spot On ! thank you Stefi.

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: