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

 

[MrTeeny]

To find the last row with data you can use

[COLOR=#333333]Sub Macro3()[/COLOR]
Dim FinalRow As Integer

etc etc
  
FinalRow = Cells(Rows.Count, "C").End(xlUp).Row

If you want to find the last row in column A rather than C use Cells(Rows.Count, "A").End(xlUp).Row instead

and then just amend your lines

[COLOR=#333333]Selection.AutoFill Destination:=Range("C2:C" & [/COLOR]FinalRow [COLOR=#333333])[/COLOR]
[COLOR=#333333]Range("C2:C" & [/COLOR]FinalRow [COLOR=#333333]).Select[/COLOR]

 

[CodeNinja]

Sccardais,
For the future, when using the macro recorder, you can switch to relative or absolute references in the developer tab by clicking on the "Use Relative References" button. To see the developer tab, go to File -> Options -> Customize Ribbon and check the "Developer" checkbox.

I believe for now, what you are looking for is something like:

    Selection.AutoFill Destination:=Range("C2:C" & Range("C2").End(xlDown).Row)
    Range("C2:C" & Range("C2").End(xlDown).Row).Select

Good luck,

CN.

 

[sccardais]

Thank you, CN. This code extends the selection range to the entire column, not just the final row with data.

I think the suggestion from MrTeeny above will limit the range to the last row with data.

But, being new to VBA, I don't know where to insert his suggested code. I'm going to reply to him directly but wanted to thank you for your reply.

Sub Macro3()
Dim FinalRow As Integer

etc etc

FinalRow = Cells(Rows.Count, "C").End(xlUp).Row

 

[JackDanIce]

Welcome to the board. Instead of using a formula to create the ID, you can create it and print it as a value (reducing sheet calculation time for each refresh). 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) = arr(x, 1) & "-" & 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

NB, variables declared as Integers convert to Long data types when the macro is complied. As such, they're almost redundant in VBA so better practise to declare as Long when initiating.

 

[sccardais]

Thanks. I know this is probably the ultimate beginner question but I don't know where to insert your suggested code.

Is it like this?

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.
'


'
Dim FinalRow As Integer
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
FinalRow = Cells(Rows.Count, "C"). End(x1Up).Row
Selection.AutoFill Destination:=Range("C2:C" & FinalRow)
Range ("C2:C" & FinalRow ).Select
End Sub​

Reply Reply With Quote ​

 

[JackDanIce]

You can replace all of the code you have with the suggested code in thread #5

 

[sccardais]

Thank you, Jack. This limited the range to the last row with data but it changed the result of the calculation to show the date as mm/dd/yyyy rather than the serial number value of the date.

I read your code but don't see where to change it to concatenate the serial value of the date.

ScottC

 

[JackDanIce]

Edits 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)) & "-" & CStr(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]

That change didn't change the format. The calc for "ID Activity" results in this "515945-8/28/17". I'm looking for "515945-42975" (serial value of 8/28/17)