VBA: Run-time error 1004. No cells were found.

[zero269]

Hello,

I just started getting this error today.

I'm building several macros that deal with Conditional Formatting (so far 20). They all use the following code to get the first empty cell in column A, which I'm using as my LastRow variable.

It wasn't until I got to Sheet 7 is when this issue occured. It's been persistent with any new Sheets I create. I even created a whole new workbook and the same issue occurred. However, this code runs flawlessly in Sheets 1 thru 6 in my VBA Testing workbook.

Sub GetLastRow()
    Dim LastRow As Long, DataRange As Range
    With ActiveSheet
        LastRow = .Range("A2:A" & Rows.Count).SpecialCells(xlCellTypeBlanks).Offset(-1).Row 'Get first empty cell in column A -1
        Set DataRange = .Range("A3:A" & LastRow)
        MsgBox DataRange.Address
        MsgBox "Last Row is " & LastRow & " on Sheet: " & ActiveSheet.Name
    End With
End Sub

Here's a phenomenon to say the least:

Cell A21 is breaking the code in Sheet 7+. Here's why:

I can populate cells up to A20 and the code runs successfully.
Excel HAPPY

If I populate A21, the code breaks and throws that run-time error about how there are no cells.
Excel NOT Happy

If I populate A22 down (after clearing A21), the code runs successfully.
Excel Happy Again

I suspected some sort of corruption, so I ruled out memory, hard drive, and OS corruption.
I even did a fresh install of O365.

I'm stumped. This issue is nothing shy of an irritating inconsistency.

On the topic of 'cell", I noticed too, that I am unable to run any code that uses 'cell'. Mainly loops I believe; like this one:
This is just to serve as a potential symptom to my main issue.

Sub Macro1()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    For Each cell In ws.Columns(1).Cells
        If IsEmpty(cell) = True Then cell.Select: Exit For
    Next cell
End Sub

Has anyone seen anything like this before?
Thanks for any insight.

 

[Georgiboy]

I avoid using special cells to get the last row, i tend to use:

LastRow = .Range("A2:A" & Rows.Count).End(xlUp).Row

As for the code below, i would not run code on an entire column but to help you understand what is wrong 'cell' should be defined as a range, i would avoid using 'cell' as it can be used by excel itself, i would use 'rCell' instead where the r stands for reference:

Sub Macro1()
    Dim ws As Worksheet
    Dim rCell As Range
    
    Set ws = ActiveSheet
    For Each rCell In ws.Columns(1).Cells
        If IsEmpty(rCell) = True Then rCell.Select: Exit For
    Next rCell
End Sub

 

[Legacy 143009]

Sub GetLastRow()
    Dim LastRow As Long, DataRange As Range
    With ActiveSheet
        LastRow = .Range("A2:A" & Rows.Count).SpecialCells(xlCellTypeBlanks).Offset(-1).Row 'Get first empty cell in column A -1
        Set DataRange = .Range("A3:A" & LastRow)
        MsgBox DataRange.Address
        MsgBox "Last Row is " & LastRow & " on Sheet: " & ActiveSheet.Name
    End With
End Sub

If I populate A21, the code breaks and throws that run-time error about how there are no cells.
Excel NOT Happy

View attachment 87481

For this part of your question, its the normal behavior. Selecting blank cells only works in used range. It finds no blank cells in the used range. This will be safer if your column has inconsistent data:

Sub GetLastRow()
    Dim LastRow As Long, DataRange As Range
    With ActiveSheet
        LastRow = .Range("A2").End(xlDown).Row 'Get first empty cell in column A -1
        Set DataRange = .Range("A3:A" & LastRow)
        MsgBox DataRange.Address
        MsgBox "Last Row is " & LastRow & " on Sheet: " & ActiveSheet.Name
    End With
End Sub

Sub Macro1()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    For Each cell In ws.Columns(1).Cells
        If IsEmpty(cell) = True Then cell.Select: Exit For
    Next cell
End Sub

View attachment 87483

For this part of your question, I assume you are using Option Explicit. If yes, you have to declare cell As Range explicitly.

 

[Peter_SSs]

i would avoid using 'cell' as it can be used by excel itself,

Where can 'cell' be used by excel itself?

 

[Georgiboy]

Where can 'cell' be used by excel itself?

Like the below, even though it is a worksheet formula i still avoid using any variable names that are the same/ similar to existing excel functions:

Book1
	A	B
1	8	TRUE
Sheet1

Cell Formulas
Range	Formula
A1:B1	A1	=CELL("width")
Dynamic array formulas.

 

[Legacy 143009]

Where can 'cell' be used by excel itself?

I thought the same. the code worked OK in my test file. cell is not a reserved name by Excel.

 

[Georgiboy]

It may not be reserved but it is close to a function name =CELL()

 

[Peter_SSs]

Like the below

OK, fair enough - but there is no overlap with vba so it is quite fine to use cell as a variable in vba. If you want to avoid it that's fine as a personal decision but no adverse consequences if others decide to use it as a variable name in vba.

 

[Georgiboy]

OK, fair enough - but there is no overlap with vba so it is quite fine to use cell as a variable in vba. If you want to avoid it that's fine as a personal decision but no adverse consequences if others decide to use it as a variable name in vba.

I make you right, it is a preference. Once you have encountered a few errors due to naming variables - it makes you steer well clear in the future.

 

[zero269]

I avoid using special cells to get the last row, i tend to use:

LastRow = .Range("A2:A" & Rows.Count).End(xlUp).Row

As for the code below, i would not run code on an entire column but to help you understand what is wrong 'cell' should be defined as a range, i would avoid using 'cell' as it can be used by excel itself, i would use 'rCell' instead where the r stands for reference:

Sub Macro1()
    Dim ws As Worksheet
    Dim rCell As Range
 
    Set ws = ActiveSheet
    For Each rCell In ws.Columns(1).Cells
        If IsEmpty(rCell) = True Then rCell.Select: Exit For
    Next rCell
End Sub

Hi Georgi,

Normally I would agree with you, but that method skips over blank cells to the very bottom. For my dataset I can't go all the way down. It would try to include the Table Range that I'm using a few rows below my Standard Range.

My screenshots don't reflect my original data set. I had to scrub it and try some random values using a New Sheet to see if the code would error again; and it did.

Personally, I don't think there is anything wrong with the code. I suspect some sort of corruption. I just can't pinpoint it.

My reasoning is that the code runs flawlessly in 10 different Sub routines across the first 6 Sheets. It wasn't until Sheet 7 when I started having this issue.

The other reasons are the code only throws errors in Column A. For example:
On Sheet 7, cell A21 is the culprit.
On Sheet 8, it's cell A14.
...

*****************************************************************************************

OK, something just changed after I enabled the XL2BB Add-In to provide my sample data.
I need to check something out before I realize later it's just a fluke.

I'll be back...