How to find last row in column, copy, then FillDown

[cvincent]

I have a spreadsheet that fills down each column. The problem with columns E & F is that they contain numbers, and each row increases by 1, i.e. 123 Adam St, 124 Adam St, 125 Adam St, etc. I need VBA to find the last row of data in that column, copy it, then fill down without the number increasing? They are address and zip code fields.


Thank you.

 

[RickXL]

The usual trick is to hold down the Ctrl key at the same time. (I am assuming a Windows PC here.)

If the cell increments and you don't want it to, hold down Ctrl when filling down.

Also, if the cell does not increment and you would like it to, again try Ctrl.

I hope this helps.

 

[cvincent]

Thank you Rick XL. What I need however is VBA to copy the last populated cell in that column down to the last row that has data, which is variable. Example, a name from another worksheet has been populated into the last cell in column C, then copied down to the last row of data. But when same is done with the address from the worksheet into column D, it copies down incrementing.

 

[RickXL]

I just tried the macro recorder and found this:

    Range("A1").Select
    Selection.AutoFill Destination:=Range("A1:A17"), Type:=xlFillCopy
    Range("A1:A17").Select

    Range("B1").Select
    ActiveCell.FormulaR1C1 = "1"
    Selection.AutoFill Destination:=Range("B1:B17"), Type:=xlFillSeries
    Range("B1:B17").Select

So it looks like
Type:=xlFillCopy
and
Type:=xlFillSeries

options are what you need.

 

[cvincent]

Here is my code that included the Type:=xlFillCopy, but it is still adding 1 to each row:

'Address
Windows("Worksheet.xlsm").Activate
Sheets(CurrentWorksheet).Select
Range("C58").Copy
Windows("Approval Spreadsheet.xlsx").Activate
Sheets("Approval Spreadsheet").Range("E" & ApprovalFinalRow).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
Sheets("Approval Spreadsheet").Select
Range("E" & ApprovalFinalRow).Select
If ApprovalFinalRow <> ApprovalNewFinalRow Then
Selection.AutoFill Destination:=Range("E" & ApprovalFinalRow & ":E" & ApprovalNewFinalRow), Type:=xlFillCopy
End If

 

[cvincent]

Rick XL - Thank you for your help and pointing this out. The problem was that even though I copied my VBA over, instead of Type:=xlFillCopy, I actually had Type:=xlFillDefault, which is why it wasn't working correctly.

 

[RickXL]

I don't know what to say. I just tried the last part of your code and, for me, it works as expected.

Sub z()
    Dim ApprovalFinalRow As Long
    Dim ApprovalNewFinalRow As Long
    
    ApprovalFinalRow = 10
    ApprovalNewFinalRow = 20
    Range("E" & ApprovalFinalRow) = "X1"
    
    Range("E" & ApprovalFinalRow).Select
    If ApprovalFinalRow <> ApprovalNewFinalRow Then
        Selection.AutoFill Destination:=Range("E" & ApprovalFinalRow & ":E" & ApprovalNewFinalRow), Type:=xlFillCopy
    End If
End Sub

"Type:=xlFillSeries" increments the numbers and "Type:=xlFillCopy" does not.

 

[cvincent]

You are correct RickXL. I had entered it incorrectly. Thank you.

 

[RickXL]

No problem and thanks for the feedback.

I am glad to hear that it is working now.