Extend Fill Down Range to last row

[sccardais]

I am new to VBA.

The last lines of the recorded macro shown below show an absolute reference for a Fill Down operation (C448). I would like to make the reference dynamic - filling to the last row in the sheet rather than the fixed reference.

How should I modify this macro to do this?

Thank you ...

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000 ; background-color: #ffffff }p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #008f00 ; background-color: #ffffff }p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff ; min-height: 13.0px}p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993 ; background-color: #ffffff }span.s1 {color: #011993 }span.s2 {color: #000000 }</style>Sub Macro3()
'
' Macro3 Macro
' Prepares Daily Activity Report for import into FMP. Creates copy of original data, deletes unnecessary columns, adds calc for unique ID.
'


'
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Original Data"
Sheets("Original Data").Select
Sheets("Original Data").Copy Before:=Sheets(1)
Sheets("Original Data (2)").Select
Sheets("Original Data (2)").Name = "Edit for Import"
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("C1").Select
ActiveCell.FormulaR1C1 = "ID Activity"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]&""-""&RC[-1]"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C448")
Range("C2:C448").Select
End Sub

 

[JackDanIce]

Edit in red, try:

Sub Macro3()


    Dim x       As Long
    Dim arr()   As Variant
    
    Application.ScreenUpdating = False
    
    With Sheets("Sheet1")
        .Name = "Original Data"
        .Copy before:=Sheets(1)
    End With
    
    With Sheets("Original Data (2)")
        .Name = "Edit for Import"
        .Cells(1, 1).EntireColumn.Delete shift:=xlToLeft
        .Cells(1, 3).EntireColumn.Insert shift:=xlToRight
        .Cells(1, 3).Value = "ID Activity"
        
        x = .Cells(.Rows.Count, 1).End(xlUp).row
        arr = .Cells(1, 1).Resize(x, 3).Value
        arr(1, 3) = "ID Activity"
        For x = LBound(arr, 1) + 1 To UBound(arr, 1)
            arr(x, 3) = CStr(arr(x, 1)) & "-" & CLng(arr(x, 2))
        Next x
        Cells(1, 1).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
        Erase arr
    End With
    
    Application.ScreenUpdating = True
        
End Sub

 

[sccardais]

Yes. That works.

Thank you for your help.

 

[JackDanIce]

You're welcome.