Fix Excel VBA Error 1004 When Checking Duplicate Values
Question details
Resolve VBA error 1004 when code reads worksheet cells while checking for 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.
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.
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.
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.
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.
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.
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.

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.

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.




