Help With Autofill
 

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.

Help With Autofill
 

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

--
Regards,
Tom Ogilvy



"Jason" wrote in message
...
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.

Help With Autofill
 

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

"Jason" wrote in message
...
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.

Help With Autofill
 

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



"Ken Loomis" wrote in message
...
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

"Jason" wrote in message
...
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.

Help With Autofill
 

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


On Mon, 4 Oct 2004 19:14:06 -0700, "Ken Loomis" wrote:

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

"Jason" wrote in message
...
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.

Help With Autofill
 

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

That helps me a lot.

Ken Loomis


"Myrna Larson" wrote in message
...
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


On Mon, 4 Oct 2004 19:14:06 -0700, "Ken Loomis"
wrote:

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

"Jason" wrote in message
...
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.

Help With Autofill
 

Excellent! Thanks for all the help

"Ken Loomis" wrote:

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

That helps me a lot.

Ken Loomis


"Myrna Larson" wrote in message
...
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


On Mon, 4 Oct 2004 19:14:06 -0700, "Ken Loomis"
wrote:

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

"Jason" wrote in message
...
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.