excel vba automatic formulas according to my tables sizes-CodePudding

I need to know if the is anyway to automate the range formulas used according to the columns. This was my original code:

    Sub Formulas_new_sheet()
' Cambiar los rangos acorde al numero de filas usadas

    ' Formula Costo unitario
    Range("D2").Select
    ActiveCell.Formula = "=C2/B2"
    'ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
    Range("D2").Select
    Selection.AutoFill Destination:=Range("D2:D7"), Type:=xlFillDefault
    Range("D2:D7").Select
    '________________________
    ' Formula de extra / ganancia
    Range("E2").Select
    ' Variar según el porcentaje de ganancia que se busca
     ' recordando .55 = 55% , .30 = 30%
    ActiveCell.Formula = "=D2 (D2*0.55)"
    Selection.AutoFill Destination:=Range("E2:E7"), Type:=xlFillDefault
    Range("E2:E7").Select
    'Formula para precio de venta c/u
    Range("G2").Select
    ActiveCell.Formula = "=E2 F2"
    Range("G2").Select
    Selection.AutoFill Destination:=Range("G2:G7"), Type:=xlFillDefault
    Range("G2:G7").Select
End Sub

Then i was help in a forum and i got this:

    Sub Macro3()
TableLastRow = 15
Range("D2:D" & TableLastRow).FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("E2:E" & TableLastRow).FormulaR1C1 = "=RC[-1] (RC[-1]*0.55)"
Range("G2:G" & TableLastRow).FormulaR1C1 = "=RC[-2] RC[-1]"
End Sub

But now i want know if is there anyway to automate even more. Not to change TableLastRow every time i do a different table size number of rows Thanks

CodePudding user response：

Write Column Formulas

Since you have no error handling, one could conclude that column B has always numbers so you could use it to calculate the 'last row', the row of the last non-empty cell...
```
ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
```
... or just determine the last non-empty cell...
```
ws.Cells(ws.Rows.Count, "B").End(xlUp)
```
... as illustrated in the following code:

Sub WriteFormulas()
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    With ws.Range("B2", ws.Cells(ws.Rows.Count, "B").End(xlUp))
        .Offset(, 2).FormulaR1C1 = "=RC[-1]/RC[-2]"
        .Offset(, 3).FormulaR1C1 = "=RC[-1] (RC[-1]*0.55)"
        .Offset(, 5).FormulaR1C1 = "=RC[-2] RC[-1]"
    End With

End Sub