Copy values in a column based on last row in another column

Question

I have an Excel sheet with a four-column setup.

I'm trying to copy the content of the cells in column B to column C as long as there are active rows in any cells of these 4 columns. By that, I mean including the value a11.

This is supposed to work in an Excel template by an autorun macro to fill out a form. I have tried many ways I found on different sites but nothing works. They all seem to stop at the first blank cell in any row.

Sub Auto_Open()
    With Range("B1", Range("B1").End(xlDown))
        Sheets("Upload").Range("C" & Rows.Count).End(xlUp).Offset(0).Resize(.Count) = .Value
    End With
End Sub

The output of my last code (posted here) ends at first empty cell.

Possible duplicate of Error in finding last used cell in Excel with VBA. See this question for a robust way to find the last cell in a column. — BigBen

Mikku Mikku · Accepted Answer · 2019-06-17T14:06:15

This Should Work:

This function finds the last used row in the Entire Sheet.

Sub Auto_Open()

Sheets("Upload").Range("C1:C" & LastRow(ActiveSheet)).Value = Sheets("Upload").Range("B1:B" & LastRow(ActiveSheet)).Value

End Sub


Function LastRow(Sh As Worksheet)
    On Error Resume Next
    LastRow = Sh.Cells.Find(What:="*", _
                            After:=Sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0
End Function

Copy values in a column based on last row in another column

1 Answers