VBA & Macro Problems

Excel VBA: Select Existing Rows and Avoid 1/0/1900 Dates

Algirdas JasaitisAlgirdas Jasaitis Nov 18, 2026 869 views

Question details

The user needs an Excel VBA macro to process only existing data rows and avoid turning blank cells into zero-date values.

Select Existing Rows in Excel VBA and Avoid 1/0/1900 Dates
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

In Excel, open the workbook and select Developer > Visual Basic. Open the module containing your macro, or select Insert > Module.

2
Find and process the data range

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

3
Run and check the macro

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.

4
Clear unwanted zero values

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.

Limit the VBA range to the last data row and skip blanks
Choose the right column: If column A has gaps or ends before the rest of the dataset, use a column that reliably identifies every data row. The example skips blank cells and errors, but it does not automatically exclude legitimate numeric zeros.
Free Microsoft Office alternative

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.

Review spreadsheet rows and date-formatted cells in WPS SpreadsheetsOpen and edit Microsoft Excel formats such as XLSXUse a familiar spreadsheet interface for an easier transitionWork with a free, lightweight office suite
microsoft office alternative - wps office

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.