VBA Selection.autofill

[Tinhare]

I'm stuck on this. I have multiple sheets selected and when running VBA a copy and paste only works on the first sheet, if I remember correctly.
So another way for me to get around this is to use selection.autofill and fill down the number of rows I require.
When recording the Autofill I end up with
Rows("330:330").Select
Selection.AutoFill Destination:=Rows "330:362"), Type:=xlFillDefault
Rows("330:362").Select
How do I make the rows variable as I know which row is the start but the last row would change. For example startrow + 5 rows.
Sure I can add 5 to the start row but then the answer has to be text with a colon between the start and end row. I have tried :-
FillRows = CStr(RowOffSet2 + 6) & ":" & CStr(RowOffSet2 + 7)
Selection.AutoFill Destination:=Rows(FillRows), Type:=xlFillDefault
Where I needed a variable start and finish row, which works, but there must be a better way.

Any help would appreciated,

Thanks,
Alan

[ This Message was edited by: Tinhare on 2002-05-13 20:01 ]

[Colo]

Hi Tinhare,

Please try this. And check the "Resize" on help.

Sub test()

  With Rows("330:330")

    .AutoFill Destination:=.Resize(5)

  End With

End Sub

[Tinhare]

Hi Colo,

Ah yes, resize. I was reading about it the other day. It resizes the selected range and returns a range object of the resized range.

What about when I don't know where the first row is. I mean I have counted down a column of data and when a condition is met I then would fill down that row. I can see that I would use resize to expand the fill down.
Oh I think I just answered my own question. If I select the row and get the range address of the selection I should be able to use that.
Open to sugestions though.

Thanks,
Alan.

[Tinhare]

This seems to work.

Rows(Rowoffset).Select
With Rows(Selection.Address)
.AutoFill Destination:=.Resize(5)
End With

Thanks for your help.

Alan