copying down a formula only where there is data

[childofthe1980s]

Hello:

I created a macro that calculates, within a column, percentages based on
data in the columns to the immediate left.

The only issue with this macro is that the column is only copied "down" 604
rows in the spreadsheet. I can modify the macro in VBA to copy straight down
to the last row possible in Excel. But, I would prefer to have it copied
down to the last row that contains data to the immediate left of the column.
(This column is column L and is the last column in my spreadsheet.)

Here is the code that does this copying:

Selection.AutoFill Destination:=Range("L2:L604")
Range("L2:L604").Select

How can I (a) modify the code to just copy down to that last row of data and
(b) save this modfication in VBA so that I can re-use the .xlt file?

Thanks!

childofthe1980s

 

[Jim Thomlinson]

Selection.AutoFill Destination:=Range(range("L2"), cells(rows.count,
"L").end(xlup))
Range(range("L2"), cells(rows.count, "L").end(xlup)).select

 

[childofthe1980s]

Hmm....I got a debugger error on that first line when I used that
programming...please help.

Here's the full program including those two lines that you suggested:

ActiveWindow.LargeScroll ToRight:=1
ActiveWindow.SmallScroll ToRight:=-5
Range("L2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]<RC[-5], RC[-2]/RC[-5], 0)"
Range("L2").Select
Selection.AutoFill Destination:=Range(Range("L2"), Cells(Rows.Count,
"L").End(xlUp))
Range(Range("L2"), Cells(Rows.Count, "L").End(xlUp)).Select
Columns("L:L").Select
Selection.NumberFormat = "0%"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range(Range("L2"), Cells(Rows.Count, "L").End(xlUp)).Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "% Below Min"
Range("L3").Select
Columns("L:L").EntireColumn.AutoFit
ActiveWindow.LargeScroll ToRight:=-1
Range("A1").Select
End Sub

 

[childofthe1980s]

Actually, here is the solution:

ActiveWindow.LargeScroll ToRight:=1
ActiveWindow.SmallScroll ToRight:=-5
Range("L2:L" & Cells(Cells.Rows.Count, "J").End(xlUp).Row).FormulaR1C1 =
"=IF(RC[-2]<RC[-5], RC[-2]/RC[-5], 0)"
Columns("L:L").NumberFormat = "0%"
Range("L2:L" & Cells(Cells.Rows.Count, "J").End(xlUp).Row).Value =
Range("L2:L" & Cells(Cells.Rows.Count, "J").End(xlUp).Row).Value
Range("L1").Value = "% Below Min"
Application.CutCopyMode = False
Columns("L:L").EntireColumn.AutoFit
ActiveWindow.LargeScroll ToRight:=-1
Range("A1").Select