To copy a range in Excel VBA through the last populated row, find that row with End(xlUp), build a range ending there, and copy it to a destination. For example, if column A identifies every data row, use wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row to get the last row, then copy A1:C through that row. The key is choosing a column or method that reflects what “last row” means in your data.
A basic macro to copy through the last row
This example assumes the source sheet has headers in row 1, data begins in row 2, and every data record has a value in column A. It copies columns A through C, including the header, to cell A1 on a destination sheet named Archive.
Sub CopyThroughLastRow()
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim lastRow As Long
Dim sourceRange As Range
' Use explicit worksheets rather than relying on the active sheet.
Set wsSource = ThisWorkbook.Worksheets("Data")
Set wsDest = ThisWorkbook.Worksheets("Archive")
' Find the final nonblank cell in the key column.
lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
' Stop if there are no data rows below the header.
If lastRow < 2 Then Exit Sub
' Include the header and all three data columns.
Set sourceRange = wsSource.Range("A1:C" & lastRow)
sourceRange.Copy Destination:=wsDest.Range("A1")
End Sub
Change the worksheet names, starting cell, and column letters to fit the workbook. If your data begins in row 1 without a header, adjust the empty-data check accordingly. The source range is copied as a block, so blank cells inside the block do not stop the operation.
How End(xlUp) finds the last row
Excel worksheets have many rows, so VBA starts at the bottom of the chosen column and moves upward to the first nonblank cell. The Rows.Count expression supplies the bottom row number for that worksheet, while End(xlUp) behaves like pressing Ctrl+Up from the bottom of the column. This is usually much faster and clearer than checking every row in a loop.
The chosen column matters. If column A contains an ID on every record, it is a good anchor. If some records have a blank A cell but contain values in B or C, the code above will stop too early. Use a column that is reliably populated for all records, or use a method that searches across the sheet.
- Use a key column when every relevant row has a value in that column.
- Use a broader search when records can have gaps in the key column.
- Decide whether formulas returning an empty string count as occupied; that affects which last-row method is appropriate.
Find the last used row anywhere on the sheet
When no single column is guaranteed to be filled, Range.Find can search the worksheet by rows and return the last cell containing a formula or a constant. The following version searches formulas too, including formulas whose displayed result is an empty string. It then copies the rectangular range from A1 through column C on that final row.
Sub CopyUsingLastUsedRow()
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim lastCell As Range
Dim lastRow As Long
Set wsSource = ThisWorkbook.Worksheets("Data")
Set wsDest = ThisWorkbook.Worksheets("Archive")
' Search by rows for the last cell containing a formula or value.
Set lastCell = wsSource.Cells.Find(What:="*", _
After:=wsSource.Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
' Find returns Nothing when the sheet has no matching content.
If lastCell Is Nothing Then Exit Sub
lastRow = lastCell.Row
wsSource.Range("A1:C" & lastRow).Copy _
Destination:=wsDest.Range("A1")
End Sub
Passing search options explicitly is useful because Excel remembers several Find settings from earlier searches or dialogs. This approach considers formulas and cell contents, not merely formatting. If stray content far to the right or below the intended table exists, the last row may extend farther than expected; narrow the search to the relevant columns when necessary.
Copy to the next empty row instead of overwriting
Copying to A1 on the destination sheet replaces existing cells in the area being copied. To append records beneath existing destination data, calculate the next available row there as well. In this example, column A identifies both source and destination records, and the destination includes a header only when it is empty.
Sub AppendThroughLastRow()
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim lastSourceRow As Long
Dim lastDestRow As Long
Dim firstSourceRow As Long
Dim nextDestRow As Long
Set wsSource = ThisWorkbook.Worksheets("Data")
Set wsDest = ThisWorkbook.Worksheets("Archive")
firstSourceRow = 2
lastSourceRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
If lastSourceRow < firstSourceRow Then Exit Sub
lastDestRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row
If Application.WorksheetFunction.CountA(wsDest.Cells) = 0 Then
nextDestRow = 1
' Add the source header to a completely blank destination.
wsSource.Range("A1:C1").Copy Destination:=wsDest.Range("A1")
nextDestRow = 2
Else
nextDestRow = lastDestRow + 1
End If
wsSource.Range("A" & firstSourceRow & ":C" & lastSourceRow).Copy _
Destination:=wsDest.Cells(nextDestRow, 1)
End Sub
This example appends data rows but does not repeat the header on a destination that already contains content. If column A can be blank in valid records, use a different dependable destination column to find its last row. Also consider duplicate records: appending does not check whether the same rows already exist.
Copy everything, or copy values only?
The Copy Destination:= form copies cell content and much of its presentation, including formulas, formatting, and number formats. Relative references in formulas may adjust as they do during a normal Excel copy. That can be useful for a faithful transfer, but it may not be what you want when the destination should contain fixed results rather than formulas.
For values only, assign the source range’s values to a same-sized destination range. This avoids the clipboard and does not transfer formatting. The example assumes the source range has already been set and that lastRow is at least 1.
Sub CopyValuesOnly()
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim lastRow As Long
Dim sourceRange As Range
Dim destinationRange As Range
Set wsSource = ThisWorkbook.Worksheets("Data")
Set wsDest = ThisWorkbook.Worksheets("Archive")
lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
If lastRow < 1 Then Exit Sub
Set sourceRange = wsSource.Range("A1:C" & lastRow)
Set destinationRange = wsDest.Range("A1").Resize( _
sourceRange.Rows.Count, sourceRange.Columns.Count)
' Transfer results without formulas or source formatting.
destinationRange.Value = sourceRange.Value
End Sub
Use a normal copy when formulas and formatting should travel with the records. Use a value assignment for snapshots, exports, or archives where formulas should not continue recalculating. If you need values and number formats but not formulas, that requires a more specific copy-and-paste approach; decide which properties matter before choosing the method.
Fill a formula down to the last data row
Sometimes “copy until last row” means copying a formula down a calculated column, not copying a data block to another sheet. Put the formula in the first cell, determine the last record row from a dependable key column, then use AutoFill. Here the formula is already in D2 and should be filled through D’s row corresponding to the final record in column A.
Sub FillFormulaToLastRow()
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets("Data")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' AutoFill needs a destination containing at least the seed cell.
If lastRow < 2 Then Exit Sub
ws.Range("D2").AutoFill Destination:=ws.Range("D2:D" & lastRow)
End Sub
Replace D2 with the cell containing your starting formula, and change the destination column if needed. AutoFill copies the formula pattern and adjusts relative references row by row. If you want the formula copied unchanged, check whether its references should be absolute, such as $B$1, before running the macro.
Choosing a method and avoiding common errors
For a well-structured list, End(xlUp) on a required field is generally the simplest option. For irregularly populated sheets, use Find and define whether formulas count. If the records are an Excel Table, consider using the table’s data body range rather than guessing a last row; tables expand as rows are added and make structured data boundaries easier to manage.
- Wrong worksheet: qualify every range with a worksheet variable. Unqualified
RangeandCellsrefer to the active sheet, which may not be the intended source. - Blank key cells: choose a column that is populated in every record or search more broadly.
- Unexpected formulas: decide whether to transfer formulas or only their current values.
- Destination overwrite: confirm the destination start cell, or calculate the append row before copying.
- Formatting mistaken for data: a sheet can retain formatting well below its real records. Formatting alone should not define the business data boundary.
- Header mistakes: make clear whether the destination needs the header and whether the source range starts at row 1 or row 2.
A common misconception is that Excel’s UsedRange always identifies the last meaningful record. It reports a worksheet area Excel considers used, and formatting or previously edited cells can make that area larger than the actual data. It can be useful for some tasks, but a known key column or a deliberate Find search is usually more predictable for copying records.
Run the macro safely on Windows and Mac
Before running any macro that copies or overwrites cells, save a separate copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so a mistaken destination or range may require restoring the saved copy. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain the code.
On Excel for Microsoft 365 on Windows, open the VBA editor with Alt+F11, insert a standard module through Insert > Module, and paste the procedure there. Run it from the editor or from Excel’s Developer tab. On Mac, Excel desktop supports VBA, but keyboard shortcuts can depend on the keyboard and macOS settings; Option+F11 or Fn+Option+F11 may open the editor. Excel for the web can open a workbook containing macros but does not run VBA, so use the desktop application.
Only enable macros in files you trust. A macro can change workbook contents, and VBA code can perform actions beyond copying cells. Test a new procedure on a duplicate workbook with a small sample, then confirm the first and last copied rows and the destination before using it on important data.
Frequently Asked Questions
How do I copy an entire row until the last row in VBA?
Find the last row in a reliable column, then copy from the first required row through that row. For example, use a range such as Rows("1:" & lastRow) if you truly need every worksheet column; for ordinary tables, copying only the relevant columns is usually safer and faster.
How do I find the last row when column A has blanks?
Use Find with SearchOrder:=xlByRows and SearchDirection:=xlPrevious to locate the last cell containing a value or formula. Alternatively, use another column that is filled for every record. Choose based on whether formulas returning empty text should count.
Does Copy Destination copy formulas and formatting?
Yes, it performs a normal range copy, so formulas and formatting are transferred along with cell contents. Assigning destinationRange.Value = sourceRange.Value transfers calculated values only and leaves the destination’s existing formatting in place.
Can I copy until the last row without using the clipboard?
Yes. For values, assign the source range’s Value property to an equally sized destination range. For formulas, assign Formula when preserving formulas is appropriate. A normal Copy Destination:= call is convenient when formatting and other copied cell properties are also wanted.
Why does my macro stop before the visible last row?
The anchor column may contain a blank near the bottom, or the code may be checking a different worksheet than the one you are viewing. Qualify the worksheet explicitly and use a column populated on every record, or switch to a Find-based search if the data is irregular.
Can VBA copy the last row in Excel for the web?
No. Excel for the web does not execute VBA macros. Open the workbook in Excel desktop for Windows or Mac to run VBA, or use a non-VBA option such as Office Scripts where it is available in your Microsoft 365 environment.
Related
- Excel Shuffle List of Names: 10 Easy Methods
- Excel: Find Where a Cell Is Used Across Sheets
- Excel Custom Number Format for Millions
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Active Workbook: Meaning and ExamplesIn Excel VBA, ActiveWorkbook refers to the workbook currently active in Excel’s window. Use it when a macro…
- Excel UserForm Initialize: How It Works and How to Use ItIn Excel VBA, UserForm_Initialize is the event procedure that runs when a UserForm instance is loaded, before the…
- VBA Dictionary With Multiple Values: Practical MethodsYes. A VBA Scripting.Dictionary can hold multiple values for one key, but each key still maps to a…
- Access VBA MsgBox: How to Show Messages and Get AnswersIn Access VBA, use the MsgBox function to show a message or ask a simple question. For example,…
- Excel VBA: Find the Last Row with Data in a ColumnTo find the last populated row in column A with Excel VBA, use ws.Cells(ws.Rows.Count, "A").End(xlUp).Row, where ws is…
- Excel VBA Find Value in Range: Examples and TipsTo find a value in an Excel range with VBA, use the range’s Find method, then check whether…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.