Excel VBA: Select Existing Rows and Avoid 1/0/1900 Dates
Question details
The user needs an Excel VBA macro to process only existing data rows and avoid turning blank cells into zero-date values.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A macro processes date-formatted cells in a column that contains data starting in row 2.
- Observed behavior
- Processing extra blank rows can produce zero values that display as 1/0/1900 when formatted as dates.
Identify a column that contains a value for every data row; the example uses column A with headers in row 1. Check whether blanks within that column should be skipped or handled separately.
Limit the VBA range to the last data row and skip blanks
Use this approach when column A identifies the rows to process and data starts in row 2.
End(xlUp) finds the last nonempty cell in the selected column. It does not remove blank cells within the resulting range, so the loop also checks each cell before processing it.
In Excel, open the workbook and select Developer > Visual Basic. Open the module containing your macro, or select Insert > Module.
Add or adapt this code in the module. Replace the comment inside the loop with your processing logic: Sub ProcessExistingRows() Dim ws As Worksheet Dim rng As Range Dim cell As Range Dim lastRow As Long Set ws = ActiveSheet lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row If lastRow < 2 Then Exit Sub Set rng = ws.Range("A2:A" & lastRow) For Each cell In rng If Not IsError(cell.Value2) Then If Len(Trim$(CStr(cell.Value2))) > 0 Then ' Process cell here without converting blanks to zero. End If End If Next cell End Sub
Activate the intended worksheet, then select Developer > Macros, choose ProcessExistingRows, and select Run. Check that only the intended rows were processed and that no new zero-date values appeared.
If previous runs left unwanted zero-date values, select the affected cells and use Home > Clear > Clear Contents. Check the values first so you do not remove legitimate data.

Work with spreadsheet data in WPS Office
WPS Office is a free, lightweight alternative with a familiar interface for opening and editing spreadsheets. Its Microsoft Office format compatibility makes moving existing files straightforward. It does not repair an Excel VBA macro, and macro compatibility depends on the WPS edition and features available.

Frequently Asked Questions
Why does a macro show 1/0/1900 for a blank cell?
A macro may convert a blank to zero or write zero into a date-formatted cell. Depending on the date format and Excel settings, that zero can appear as 1/0/1900.
What if there is no data below the header?
The check If lastRow < 2 Then Exit Sub stops the example before it creates a range when column A has no data below row 1.
Does End(xlUp) skip blank cells between data rows?
No. It finds the last nonempty cell in the chosen column, but the range can still contain internal blanks. Check each cell before processing it.
What if column A does not contain every data row?
Change "A" in the last-row calculation and range to a column that reliably identifies the rows you need. Otherwise, the macro may stop before reaching all your data.
Will the example skip formulas that return an empty string?
Yes. The loop checks the displayed result through cell.Value2 and skips an empty string. However, such a formula can still affect where End(xlUp) finds the last row.




