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.

 

[bebo021999]

.Specialcells
sometime work, some time not
especially refer to a range, a cell, using specialcells.
It sometime keep history in memory.
For example:
A8="ABC"
A100 = "XYZ"
Use below code:

Columns(1).SpecialCells(xlLastCell).Select

to select last cell in column A, cell A100 is activated.

But, if we delete cell A100, run code again, A100 still be selected.

 

[zero269]

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

Hi Flashbond,

I just pulled that code off the web for reference in case it added value to my initial issue regarding that strange error message about "no cells were found".
I've seen video where people use 'cell' without declaring it, so I just assumed it was just me or my system that was the issue. I'm glad to hear that it's not the case.

Thanks,

 

[zero269]

UPDATE:

First let me say thanks to all those who took the time to offer insight to the issue I reported and coding suggestions for a VBA novice like myself.

As I was responding to Georgie earlier, I discovered that the code started to run again successfully on my sample data.
I had just enabled the XL2BB Add-In so that I could provide a Mini Sheet of my sample data with my Reply. I then decided to try the code again so I can get a screenshot of the Immediate window results and then confirm the error would occur if I entered anything in those problematic cells. Low and behold, the code ran successfully for the first time since I reported the issue yesterday.

I wanted to close and reopen the workbook and run the code again several times on my two problematic sheets, as well as a new one.
All tests ran successfully.
I also noticed Windows Update was prompting for a restart. So, after a restart, I ran my tests again several times; everything is back in working order.

Just for reference, these were the five updates that were installed today:

• 2023-03 Cumulative Update for Windows 10 Version 21H2 for x64-based Systems (KB5023696)
• 2023-03 .NET 6.0.15 Update for Client (KB5024670)
• 2023-02 Cumulative Update for Windows 10 Version 21H2 for x64-based Systems (KB5022834)
• 2023-02 Cumulative Update for .NET Framework 3.5, 4.8 and 4.8.1 for Windows 10 Version 21H2 for x64 (KB5022728)
• 2023-02 .NET 6.0.14 Security Update for Client (05023288)

I'm not going to claim I know what caused the problem or the solution. I can say this was definitely some sort of corruption that may have been caused by and then resolved by the add-in, or perhaps remediated by one of the updates.

Nonetheless, it's a mystery to me.

Here's a modified version of the original code I used to get the "first empty cell" in a column. I ran it against A-D:

Sub GetLastRows()

    Dim iChar As Integer, rngCol As String, LastRow As Long, DataRange As Range
    
    For iChar = 65 To 68 'Loop Columns A-D
'        Debug.Print """" & Chr(iChar) & 1 & ":" & Chr(iChar) & """"
        rngCol = ("" & Chr(iChar) & 1 & ":" & Chr(iChar) & "") 'Build string value "A1:A", etc.
    
        With ActiveSheet
            LastRow = .Range(rngCol & Rows.Count).SpecialCells(xlCellTypeBlanks).Offset(-1).Row 'Get first empty cell in column
            Set DataRange = ActiveSheet.Range(rngCol & LastRow) 'Set range to "[I]Last Row[/I]" in column
            'Display output in Immediate window
            Debug.Print DataRange.Address
            Debug.Print "Last Row is " & LastRow & " on Sheet: " & ActiveSheet.Name
        End With
    
    Next iChar
    
End Sub

Results show a complete and accurate range for each column:

VBA Testing CF Rules.xlsm
	A	B	C	D
1	A	B	C	D
2	Values 1	Values 2	Values 3	Values 4
3	24414	2065	11036	45380
4	48086	44524	20414	34199
5	2235	10149	6804	47718
6	5728	24414	45089	18560
7	7224	48086	14323	28300
8	31573	2235	39066	31573
9	42755	5728	34472	42755
10	46791	7224	26003	46791
11	14119	20312	19225	14119
12	29085	21749	9868	2154
13	6650	43734	3399	11912
14	33403	33380	21820	48278
15	14026	43480	13439	32426
16	11036	26597	29085	45925
17	20414	36998	6650	39981
18	6804	23664	33403	680
19	45089	13220	14026	48422
20	14323	3895	34525	45703
21	1	22254	32633	36233
22	21820	43480	21450	44923
23	15,675	38892	564	42048
24	32,735	43436	1680	1587
25	21,733	8244	6090	14826
26	49,870	20862	3986	2804
27		6665	24591	34492
28			38525	21569
29				7175
30
31	Overflow	Data	Overflow	Data
Corrupt_A21

 

[zero269]

.Specialcells
sometime work, some time not
especially refer to a range, a cell, using specialcells.
It sometime keep history in memory.
For example:
A8="ABC"
A100 = "XYZ"
Use below code:

Columns(1).SpecialCells(xlLastCell).Select

to select last cell in column A, cell A100 is activated.

But, if we delete cell A100, run code again, A100 still be selected.

Hi bebo,

In my case I needed the first empty cell. I'm using sample data (not shown here) with a Table Range below a Standard Range. If this was raw data, the xlLastCell is a must for sure. An alternate method I learned about today is .range.currentregion which is the same as the 'Select All" shortcut CTRL + A that selects all cells within the empty rows and columns.

For example, using my sample dataset, it results in the following range:

?ActiveCell.CurrentRegion.Address​

$A$1:$D$29​

Row 30 is empty (31 is not) and column E (and on) are empty.

Range.CurrentRegion property (Excel)

Office VBA reference topic

learn.microsoft.com

 

[Alex Blakenburg]

.range.currentregion which is the same as the 'Select All" shortcut CTRL + A that selects all cells within the empty rows and columns.

While Ctrl+A will give you the current region if you do NOT have a table, if you do have a table Ctrl+A will give you the databody range and exclude the headings.
To include the headings of a table and get the actual "current region" you need to Ctrl+A twice.
The true keyboard shortcut for current region is Ctrl+* (on the numeric keypad) OR Ctrl+Shift+8 (where Shift+8 = "*")

 

[zero269]

UPDATE:

Bad News:
After I reported everything was working again, the problem returned. I've pretty much given up on this issue and will just consider it a phenomenon considering its inconsistency. What I did before the issue returned:

1. I removed Sheets 7 - 9 that I was using for my testing prior to my previous post.
2. I added a new Sheet (7) to start from scratch and continue my CF Rules project.
3. I populated Column A with some random values (about 30 rows) and ran the code. It threw the same error regarding "no cells were found".

Good News:
After that, I decided to look into an alternate solution to replace the SpecialCells(xlCellTypeBlanks) method for getting the last row.

To my surprise, I found something that fits my needs a lot better... and even simpler:
And the best part is I haven't seen any errors after testing it on all the current worksheets (6) + 3 new ones.

Range("A1").End(xlDown).Row

This is the equivalent of using shortcut CTRL + Down Arrow. This takes me down to the last populated cell... not skipping any blanks.
I tested this using the same looping method for Columns A:D:

Sub GetLastRows()
    Dim iChar As Integer, rngCol As String, LastRow As Long, DataRange As Range
    For iChar = 65 To 68 'Loop Columns A-D
        rngCol = ("" & Chr(iChar) & 1 & ":" & Chr(iChar) & "") 'Build string value "A1:A", etc.
        With ActiveSheet
            LastRow = Range("A1").End(xlDown).Row ' CTRL + Down Arrow
            Set DataRange = ActiveSheet.Range(rngCol & LastRow) 'Set range to "Last Row" in column
        'Display output in Immediate window
            Debug.Print DataRange.Address
            Debug.Print "Last Row is " & LastRow & " on Sheet: " & ActiveSheet.Name
        End With
    Next iChar
End Sub