lastRow & lastRow2 to dynamically add formulas into a table with a macro in Excel

Question

I have following VBA Code in an Excel Macro:

Sub calcular_datos()

' ************************************************************************************************
' Aplicamos fórmulas a las celdas de Datos
' ************************************************************************************************

    With Sheets("Datos")
        lastRow = .Cells(.Rows.Count, "AC").End(xlUp).Row
        lastRow2 = .Cells(.Rows.Count, "AD").End(xlUp).Row + 1
        .Range("AD" & lastRow2 & ":AD" & lastRow).Formula = _
            "=IF([@[ga:visits]]=0,0,1)"
    End With
With Excel.Application
        .ScreenUpdating = True
        .Calculation = Excel.xlCalculationAutomatic
        .EnableEvents = True
End With

End Sub

The purpose of the code is to dynamicaly integrate a formula to the new rows added into a table. Because the file will be updated regularly and will have a lot of rows and calculated cells, I don't want to let the formula in the table itself (which will automatically integrate the formula), but to add it every time I run the macro and then copy & paste values.

lastRow determines how many rows are there in the last text column (the last one before the calculated columns begin).

lastRow2 determines where the first blank cell is, and should tell Excel where it should begin adding formulas.

For example: if lastRow returns 14922 and lastRow2 12352, the macro should add the formula into cells AD12352:AD14922.

Excel doesn't report an error, so I figure that the code is not bad at all. But any formula is added.

Is there something special with Tables?

I just figured that the macro is inserting the formula at the end of the table. So I believe, that the lastRow2 calculation is taking into consideration the whole table. I think that I have to find the next blank cell, and not the last row itself. — agustin

C Jones C Jones · Accepted Answer · 2014-02-19T02:56:53

Your Comment is right, to find the first blank cell you need the line:

Range("AD1").End(xlDown).Offset(1, 0).Select

This would make your macro:

Sub calcular_datos()

' ************************************************************************************************
' Aplicamos fórmulas a las celdas de Datos
' ************************************************************************************************

    With Sheets("Datos")
        lastRow = .Cells(.Rows.Count, "AC").End(xlUp).Row
        lastRow2 = Range("AD1").End(xlDown).Offset(2, 0).Select 
        .Range("AD" & lastRow2 & ":AD" & lastRow).Formula = _
            "=IF([@[ga:visits]]=0,0,1)"
    End With
With Excel.Application
        .ScreenUpdating = True
        .Calculation = Excel.xlCalculationAutomatic
        .EnableEvents = True
End With

End Sub

The Offset(2,0) Will select teh cell underdeath the blank cell. I'm assuming you want this from your +1. To select the blank cell use Offset(1,0)

Hope it does what its ment to!

lastRow & lastRow2 to dynamically add formulas into a table with a macro in Excel

1 Answers