Macro to fill down automatically the number of rows in a spreadsheet

[lou]

I have a download file in excel that varies in size each week. When I
run the macro I have inserted new columns that have formulas. I know
how to insert the formulas into the cells on row 2 and know how to
make it fill down the sheet by pre-selecting a range but I end up with
many extra rows of data after I fill the formula down. How to I count
the number of rows and fill down that amount. Column E is the field
that is populated all the time so I would want to use that to be the
counter. I have formulas is Column A, B, C, J, K, L, M that I would
like to copy down from row 2.

Any suggestions? Thanks for the help.

Lou

 

[Gord Dibben]

One method involves a macro and a UDF

Sub Auto_Fill()
Dim lRow As Long
Dim rng As Range
Dim ocell As Range
Set rng = Range("a2,b2,c2,j2,k2,l2,m2")
lRow = Range("e" & Rows.Count).End(xlUp).Row
For Each ocell In rng
Range(ocell.Address & ":" & GetColLet(ocell.Column) & lRow).FillDown
Next ocell
End Sub


Function GetColLet(ColNumber As Integer) As String
GetColLet = Left(Cells(1, ColNumber).Address(False, False), _
1 - (ColNumber > 26))
End Function


Gord Dibben MS Excel MVP