running a macro only in first row

[Budapest]

Hello all,

I created a simple macro with many columns, and it's used by different people with different number of rows. When there's only one data row filled, the macro cannot run, I get the debug error message. I assume it is because the macro pulls down all functions to every row as long as the data lasts, but when only one row is used, it doesn't work (I mean one header line and a data line).
Please help.

Thanks,
Peter

 

[Joe4]

Welcome to the Board!

Please post your code so we can see it, and explain what you want to happen in the case where there is only one row of data filled.

 

[Budapest]

Hi, thanks for the guidance.

Here is one example from the file.

Range("U1").Select
ActiveCell.FormulaR1C1 = "BB Price Min/unit"

Range("U2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=""Before C8"",RC[-9]*0.5,RC[-9]*0.2)"

LR = Range("A666666").End(xlUp).Row
Range("U2").AutoFill Destination:=Range("U2:U" & LR)

Auto fill is not possible in case only one row is used, as it tries to autofill the lines, but I want my macro to be able to handle one line and many lines as well.

Thanks.

 

[Joe4]

Well, you are already calculating the last row number (LR), so just check to see if that is greater than 2, i.e.

[I]Range("U1").[I]FormulaR1C1 = "BB Price Min/unit"

[I]Range("U2").[I]FormulaR1C1 = _
[I]"=IF(RC[-1]=""Before C8"",RC[-9]*0.5,RC[-9]*0.2)"

[I]LR = Cells(Rows.Count,"A").End(xlUp).Row
IF LR > 2 Then
[I]     Range("U2").AutoFill Destination:=Range("U2:U" & LR)
End If[/I][/I][/I][/I][/I][/I][/I]

Note: I also cleaned up your code a little. You do not need to select the cells to enter formulas into them. You can reference them directly. Reducing Select statements will speed up your code.