To stop pasting the formula if the last line in Column A ends-CodePudding

I am working on a vba small code which extract date from Column A into Column C. the current code puts the formulas to extract date from Cell C2 to C2500, However if the data in Column A ends at line A600 it still goes down till C2500. Is it possible if we amend the code to stop pasting the formula exactly at the last line of Column A. so that i do not need to manually delete those cells "#Value". e.g. see print shot.

Sub Formula_property()
   
    Dim wb As Workbook
    Dim ws As Worksheet
    Set wb = ActiveWorkbook
    Set ws = Sheets("Sheet3")
    wb.Activate
    ws.Select
    
Range("C2:C2500").Formula = "=extractDate(A2:A2)"

End Sub

CodePudding user response：

Assuming that all columns have the same number of rows (except column B which is empty) - we can use CurrentRegion to get the size of the target "for free"

Sub formula()

Dim ws As Worksheet
Set ws = ActiveWorkbook.Sheets("Sheet3")

Dim rg As Range

Set rg = ws.Range("A1").CurrentRegion
Set rg = rg.Columns(1)

With rg
    .Offset(1, 1).Resize(.Rows.Count - 1).formula = "=extractDate(A2:A2)"
End With
End Sub

BTW: activate/select is not necessary - I recommend reading How to avoid using select.