VBA & Macro Problems

Excel VBA: Find the Last Row and Copy Columns G and H

Aamir Naveed AkramAamir Naveed Akram Nov 20, 2026 869 views

Question details

The user needs an Excel VBA procedure that finds the last populated row and copies variable-length data from columns G and H to another worksheet.

Find the Last Row and Copy Columns G and H with Excel VBA
Product
Microsoft Excel VBA
Device & OS
not provided
Scenario
A macro must copy columns G and H from a source worksheet to a destination worksheet when the number of rows varies.
Observed behavior
The existing range expression does not use the calculated last row correctly.
Before you start

Check the source and destination worksheet names and decide where the copied data should begin. Make sure the destination range can be overwritten; the example starts at cell A1.

Solution 1Recommended

Calculate the Last Row and Copy the Range Directly

Use this macro when the final record may appear in either column G or column H.

The macro checks both columns and uses the larger row number to build the range. It qualifies the source and destination worksheets, so it does not depend on which sheet is active.

1
Open the VBA editor

In Excel, open Developer > Visual Basic. On Windows, you can also press Alt+F11.

2
Insert the macro

In the VBA editor, select Insert > Module and paste this code: Sub CopyColumnsGandH() Dim sourceSheet As Worksheet Dim destinationSheet As Worksheet Dim lastRowG As Long Dim lastRowH As Long Dim lastRow As Long Set sourceSheet = ThisWorkbook.Worksheets("Download data") Set destinationSheet = ThisWorkbook.Worksheets("Group") With sourceSheet If Application.WorksheetFunction.CountA(.Columns("G:H")) = 0 Then Exit Sub lastRowG = .Cells(.Rows.Count, "G").End(xlUp).Row lastRowH = .Cells(.Rows.Count, "H").End(xlUp).Row lastRow = Application.WorksheetFunction.Max(lastRowG, lastRowH) .Range("G1:H" & lastRow).Copy Destination:=destinationSheet.Range("A1") End With End Sub

3
Set the sheet names and run

Replace "Download data" and "Group" with your worksheet names, and change A1 if needed. Run CopyColumnsGandH from Developer > Macros, then check the destination worksheet.

Calculate the Last Row and Copy the Range Directly
Existing destination data: The copy overwrites cells in the destination range. It does not remove older data below the newly copied range.
Free Microsoft Office alternative

Work with Excel Spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar interface for working with spreadsheets. Open compatible Excel files and move your everyday spreadsheet work to WPS with ease; it does not fix Excel VBA issues.

View and edit spreadsheet data in WPS SpreadsheetsCompatible with Microsoft Excel XLSX filesFamiliar interface for an easier transition from OfficeFree and lightweight for everyday document work
microsoft office alternative - wps office

Frequently Asked Questions

How do I find the last populated row in column G only?

Within a source worksheet block, use lastRow = .Cells(.Rows.Count, "G").End(xlUp).Row, then build the range with .Range("G1:H" & lastRow). This assumes column G reaches the final record.

Why should I check both columns G and H?

If column H has data below the last populated cell in G, using only G would leave those rows out. Calculate both last rows and use Application.WorksheetFunction.Max to choose the larger one.

Do I need to select the cells before copying them?

No. Use sourceSheet.Range("G1:H" & lastRow).Copy Destination:=destinationSheet.Range("A1") to copy directly without selecting either worksheet.

What happens if columns G and H are empty?

End(xlUp) alone returns row 1 for an empty column. The example checks CountA on columns G:H and exits without copying if both columns are empty.

How can I keep the macro in my workbook?

In Excel, choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm). Run the macro from Developer > Macros when macros are permitted.