Fill Formula - Macro
 

I am creating a Macro which sorts and adds a formula to the right of a
column. I want to be able to fill down the data, without creating a range
because the number of rows can change. I want the formula to fill down to
the last active cell in the column on the left of the formula. Is there a
shortcut key I can use when recording the macro?

Thanks.
Cathy U

Cathy,

Programmatically you can obtain the last active cell in a column with the
VBA command

sheet1.UsedRange.Columns(1).cells.count

Replace sheet1 with the sheet object you are referencing and the number 1
embedded in parentheses with the column number you are referencing. This
will give you the total number of used rows in that column.

http://HelpExcel.com

"Cathy U" wrote:

I am creating a Macro which sorts and adds a formula to the right of a
column. I want to be able to fill down the data, without creating a range
because the number of rows can change. I want the formula to fill down to
the last active cell in the column on the left of the formula. Is there a
shortcut key I can use when recording the macro?

Thanks.
Cathy U

Cathy

Adjust to suit.

Sub Auto_Fill()
Dim lrow As Long
With ActiveSheet
lrow = Range("B" & Rows.Count).End(xlUp).Row
Range("C1:C" & lrow).FillDown
End With
End Sub


Gord Dibben Excel MVP

On Tue, 22 Feb 2005 07:25:10 -0800, "Cathy U" <Cathy
wrote:

I am creating a Macro which sorts and adds a formula to the right of a
column. I want to be able to fill down the data, without creating a range
because the number of rows can change. I want the formula to fill down to
the last active cell in the column on the left of the formula. Is there a
shortcut key I can use when recording the macro?

Thanks.
Cathy U