Excel VBA: Find the Last Row and Copy Columns G and H
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.

- 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.
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.
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.
In Excel, open Developer > Visual Basic. On Windows, you can also press Alt+F11.
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
Replace "Download data" and "Group" with your worksheet names, and change A1 if needed. Run CopyColumnsGandH from Developer > Macros, then check the destination worksheet.

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.

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.




