Help With Autofill

[Guest]

I need help with the autofill file. I am using some code to autofill to copy
down formulas to the last empty row. The problem I have is that the last row
is dynamic and may change, which I do not know how to account for. My code
is as follows:

Range("B3:C3").Select
Selection.AutoFill Destination:=Range("B3:C12")

C12 will not always be the last row. I want the code to look to column A to
where the last cell with data is, and filldown B3:C3 down to this point. How
can I adjust the "Destination" part of the code to allow this. Thanks.

 

[Tom Ogilvy]

Sub BBCCDD()
Dim rng As Range
Set rng = Cells(Rows.Count, 1).End(xlUp)

Range("B3:C3").AutoFill Destination:= _
Range(Range("B3"), rng.Offset(0, 2))

End Sub

 

[Ken Loomis]

I am pretty new at this stuff, but I was able to do that with something like
this:

totalrows = ActiveSheet.UsedRange.Rows.count
Range("B3").Select
Range(ActiveCell, ActiveCell.Offset(totalrows - 2,
1).Range("A1")).Select
Selection.FillDown

I have to admit I do not understand why this was needed Range("A1") in that
3rd line. But it works great no matter how many rows are in the sheet.

But I do know that the Offset(totalrows - 2, 1).tells VBA to select
everything from the current selection down to the last row and over one
column.

Hope this helps.

Ken Loomis

 

[Ken Loomis]

That should actually read:

totalrows = ActiveSheet.UsedRange.Rows.count
Range("B3").Select
Range(ActiveCell, ActiveCell.Offset(totalrows - 2,
1).Range("A1")).Select
Selection.FillDown

Copy and paste didn't work too wll.

Sorry.

Ken Loomis

 

[Myrna Larson]

You should be able to accomplish the same thing without selecting things,
which slows the macro down:

TotalRows = ActiveSheet.UsedRange.Rows.Count
Range("B3",Range("B3").Offset(TotalRows - 2,1)).FillDown

or, if I have the math right

TotalRows = ActiveSheet.UsedRange.Rows.Count
Range("B3").Resize(TotalRows - 2, 2).FillDown

 

[Ken Loomis]

Thanks for the "no need to select" tip, Myrna.

That helps me a lot.

Ken Loomis

 

[Guest]

Excellent! Thanks for all the help

Thanks for the "no need to select" tip, Myrna.

That helps me a lot.

Ken Loomis