VBA & Macro Problems

How to Fix Excel VBA Returning Row 2 Instead of Row 1 in an Empty Column

Anum MushtaqAnum Mushtaq Nov 13, 2026 871 views

Question details

The user needs a VBA macro to find the first available row in a destination column and prevent an empty column from being treated as row 2.

How to Fix Excel VBA Returning Row 2 Instead of Row 1 in an Empty Column
Product
WPS Spreadsheets (Excel VBA)
Device & OS
not provided
Scenario
A user is writing a VBA macro to append data to a completely empty column on a destination worksheet.
Observed behavior
The expression End(xlUp).Row returns 1 for an empty column, and adding 1 sets nextRow to 2 instead of the expected row 1.
Before you start

Confirm whether the destination column is truly empty or contains a header cell, because the correct row calculation depends on what End(xlUp) returns in each case.

Solution 1Recommended

Check Whether the End(xlUp) Cell Is Empty Before Adding 1

Use this solution when an empty destination column causes End(xlUp).Row to return 1 and your macro writes to row 2 instead of row 1.

In Excel VBA, starting from the last row and moving up with End(xlUp) returns row 1 when the column has no data. Adding 1 blindly therefore makes an empty column start at row 2.

1
Open the VBA editor

In WPS Spreadsheets, press Alt+F11 or go to Developer > Visual Basic to open the VBA editor.

2
Select the target macro module

In the Project Explorer pane, double-click the module that contains your existing macro.

3
Replace the next-row calculation

Use this corrected logic and adapt the worksheet and column references: Dim lastCell As Range Set lastCell = destinationSheet.Cells(destinationSheet.Rows.Count, 1).End(xlUp) If lastCell.Value = "" Then nextRow = 1 Else nextRow = lastCell.Row + 1 End If

Check Whether the End(xlUp) Cell Is Empty Before Adding 1
Correct first-row detection: After this change, a completely empty column starts writing at row 1, while a column with existing data continues at the first blank row.
WPS Office VBA & Macros

Use WPS Spreadsheets to Fix Empty-Column VBA Row Detection

WPS Office supports Excel VBA macros, so you can open your existing macro workbook in WPS Spreadsheets, correct the End(xlUp) empty-column check, and run it without switching tools.

  1. 1. Install WPS Office: Download and install WPS Office from the official WPS website, then launch WPS Spreadsheets.
  2. 2. Open the macro workbook: Open your existing Excel macro workbook in WPS Spreadsheets. The file can be .xlsm or .xls.
  3. 3. Open the VBA editor: Press Alt+F11 or click Developer > Visual Basic to open the VBA editor.
  4. 4. Apply the corrected check: In the target module, add an If condition that checks whether the lastCell returned by End(xlUp) is empty before adding 1.
  5. 5. Run the macro: Run the macro from the Developer tab or by pressing F5 in the VBA editor, then confirm it writes to row 1 in an empty column.
Correctly identifies row 1 as the first available row in an empty columnSupports Excel .xlsm and .xls formats for existing macro workbooksFree and lightweight Office suite with a familiar Excel-like interfaceDeveloper tools let you edit and run VBA macros directly in WPS Spreadsheets
microsoft office alternative - wps office

Frequently Asked Questions

Why does End(xlUp).Row return 1 for an empty column in WPS Spreadsheets?

When the column has no data, starting from the last row and moving up with End(xlUp) jumps to row 1. If you always add 1 to that result, the macro treats row 2 as the first blank row.

How do I open the VBA editor in WPS Spreadsheets?

Press Alt+F11, or open the Developer tab and click Visual Basic. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon.

Can I use this VBA fix in both Microsoft Excel and WPS Spreadsheets?

Yes. The Range.End(xlUp) logic and the empty-cell check work in both applications, and WPS Spreadsheets can open and edit Excel macro-enabled workbooks such as .xlsm files.

How do I find the last used row in a column that already has data?

Use the same End(xlUp) code, but check the returned cell first. If the cell is not empty, use lastCell.Row + 1; if it is empty, use row 1 directly.

What should I do if the macro still writes to row 2 after applying the fix?

Check whether the column contains hidden formatting, formulas returning empty strings, or a header that is not really blank. Clear the column or adjust the empty-cell check for your exact worksheet layout.