Excel Error Codes

Fix Excel VBA Error 1004 When Checking Duplicate Values

Muhammad TalhaMuhammad Talha Nov 20, 2026 869 views

Question details

Resolve VBA error 1004 when code reads worksheet cells while checking for duplicate values.

Fix Excel VBA Error 1004 When Checking Duplicate Values
Product
Microsoft Excel VBA
Device & OS
not provided
Scenario
A VBA macro checks worksheet values for duplicates using a row variable and the Cells property.
Observed behavior
The macro raises error 1004 when it attempts to read a cell using a row variable that may not contain a valid worksheet row number.
Before you start

Open the workbook in Excel and press Alt+F11 to inspect the macro in the VBA editor. Identify the worksheet and the row that the duplicate check should read.

Solution 1Recommended

Assign a Valid Row and Check the Worksheet Reference

Use this fix when the error occurs at a Cells reference that uses an unassigned or invalid row variable.

An unassigned numeric variable defaults to zero, but Excel worksheet rows start at 1. A descriptive name such as lngRow also makes the code easier to follow.

1
Find the failing statement

In the VBA editor, locate the line that reads the cell, such as sEntity = .Cells(Row, 6).Value. When the error appears, click Debug to highlight the failing line.

2
Assign the row before reading cells

Declare a Long variable and assign the intended row before using it. For example, use Dim lngRow As Long followed by lngRow = 2, or assign the current row from your existing loop.

3
Verify the worksheet reference

Set a worksheet variable to the sheet containing the data, for example: Set ws = ThisWorkbook.Worksheets("Sheet1"). Declare ws As Worksheet and replace Sheet1 with the actual sheet name. ThisWorkbook refers to the workbook containing the macro.

4
Update and test the cell references

Replace the old references with ws.Cells(lngRow, 6).Value and, where needed, ws.Cells(lngRow, 11).Value. Press F8 to step through the macro and confirm lngRow is between 1 and ws.Rows.Count before either reference runs.

Assign a Valid Row and Check the Worksheet Reference
Other causes are possible: Error 1004 can have several causes. If lngRow is valid, use Debug to inspect the exact failing statement and check the worksheet and any other range references it uses.
Free Microsoft Office alternative

Review Spreadsheet Values with WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Microsoft Office formats, making it easy to move everyday spreadsheet work to WPS. It does not fix an Excel VBA error.

Review and filter spreadsheet data when looking for duplicate values.Open and edit Microsoft Excel XLSX and XLS files.Use a familiar spreadsheet interface for everyday tasks.Move existing Office documents into a lightweight office suite.
QA img-9

Frequently Asked Questions

Why does Cells(Row, 6) cause error 1004?

If Row is an unassigned numeric variable, its value is zero, and worksheet row zero does not exist. Assign a valid row number before calling Cells.

How can I check the row variable while the macro runs?

In the VBA editor, click Debug when the error appears, then inspect the variable in the Locals window or step through the code with F8. Confirm that its value is at least 1 and does not exceed the worksheet's row count.

Do I have to rename the Row variable?

Renaming it is not required to fix an invalid row number, but a name such as lngRow makes its purpose clearer. The essential fix is to assign a valid value before using it.

How do I make sure Cells refers to the correct worksheet?

Set a Worksheet variable to the intended sheet, such as Set ws = ThisWorkbook.Worksheets("Sheet1"), and use ws.Cells(lngRow, 6). Replace Sheet1 with the actual sheet name.