Run-time error 1004 - No cells were found

excelnewbie1986

New Member
Joined
May 16, 2018
Messages
16
Hi all,

Hoping someone can help! When running the macro below, I am getting a run-time error 1004 - No cells were found error. I have tried diagnosing this issue myself, but am at a loss. This macro worked perfectly on the worksheet it was originally created on. So I used that worksheet as a template, and I then paste a tonne of raw data into the template to run the macro to remove certain things, but now when I run the macro in subsequent copied versions of the template, it no longer works. The blue text denotes where the debugger has picked up the error.

Application.ScreenUpdating = False
With Range("Z:Z")
.Replace "G", "=GGG", xlWhole, , False, , False, False
.SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
End With

Application.ScreenUpdating = False
With Range("V:V")
.Replace "WATERS EDGE PRIMARY SCHOOL", "=GGG", xlWhole, , False, , False, False
.SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
End With

Application.ScreenUpdating = False
With Range("V:V")
.Replace "Green Abbeys", "=GGG", xlWhole, , False, , False, False
.SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
End With

With Range("W:W")
.Replace "331", "=GGG", xlWhole, , False, , False, False
.SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
End With

With Range("W:W")
.Replace "860", "=GGG", xlWhole, , False, , False, False
.SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
End With

With Range("W:W")
.Replace "", "=GGG", xlWhole, , False, , False, False
.SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
End With

With Range("Y:Y")
.Replace "", "=GGG", xlWhole, , False, , False, False
.SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
End With

Thanks in advance!!
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,338
Office Version
2019, 2016, 2013
Platform
Windows
probably doesn't know what sheet it should be working on so

sheets("your sheet name"). then range
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,719
Office Version
365
Platform
Windows
When you get the error have a look at col Z on the active sheet. Do you have any cells saying #NAME ?
If not there are no cells that contain "G"
 

excelnewbie1986

New Member
Joined
May 16, 2018
Messages
16
When you get the error have a look at col Z on the active sheet. Do you have any cells saying #NAME ?
If not there are no cells that contain "G"
Hello again!

Prior to running the macro, there are values of 'G' in column Z. When I run the macro and it produces the runtime error, when I click OK, the values of 'G' have disappeared from column Z, but the values in other columns remain. I'm guessing it's because the macro doesn't finish running?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,719
Office Version
365
Platform
Windows
When you get the error click Debug, which line of code is highlighted in yellow?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,719
Office Version
365
Platform
Windows
Can you please post the section of code, saves me counting lines ;)
 

excelnewbie1986

New Member
Joined
May 16, 2018
Messages
16
Sorry both. I had pasted the code into the original post, but not the whole sub......so it's line 17 for me, but line 4 (highlighted blue) below. Not only am I trying to get used to Excel, but forums too :LOL:

Code:
[COLOR=#333333]Application.ScreenUpdating = False[/COLOR]
[COLOR=#333333]With Range("Z:Z")[/COLOR]
[COLOR=#333333].Replace "G", "=GGG", xlWhole, , False, , False, False[/COLOR]
[COLOR=#0000ff].SpecialCells(xlFormulas, xlErrors).EntireRow.Delete[/COLOR]
[COLOR=#333333]End With[/COLOR]

[COLOR=#333333]Application.ScreenUpdating = False[/COLOR]
[COLOR=#333333]With Range("V:V")[/COLOR]
[COLOR=#333333].Replace "WATERS EDGE PRIMARY SCHOOL", "=GGG", xlWhole, , False, , False, False[/COLOR]
[COLOR=#333333].SpecialCells(xlFormulas, xlErrors).EntireRow.Delete[/COLOR]
[COLOR=#333333]End With[/COLOR]

[COLOR=#333333]Application.ScreenUpdating = False[/COLOR]
[COLOR=#333333]With Range("V:V")[/COLOR]
[COLOR=#333333].Replace "Green Abbeys", "=GGG", xlWhole, , False, , False, False[/COLOR]
[COLOR=#333333].SpecialCells(xlFormulas, xlErrors).EntireRow.Delete[/COLOR]
[COLOR=#333333]End With[/COLOR]

[COLOR=#333333]With Range("W:W")[/COLOR]
[COLOR=#333333].Replace "331", "=GGG", xlWhole, , False, , False, False[/COLOR]
[COLOR=#333333].SpecialCells(xlFormulas, xlErrors).EntireRow.Delete[/COLOR]
[COLOR=#333333]End With[/COLOR]

[COLOR=#333333]With Range("W:W")[/COLOR]
[COLOR=#333333].Replace "860", "=GGG", xlWhole, , False, , False, False[/COLOR]
[COLOR=#333333].SpecialCells(xlFormulas, xlErrors).EntireRow.Delete[/COLOR]
[COLOR=#333333]End With[/COLOR]

[COLOR=#333333]With Range("W:W")[/COLOR]
[COLOR=#333333].Replace "", "=GGG", xlWhole, , False, , False, False[/COLOR]
[COLOR=#333333].SpecialCells(xlFormulas, xlErrors).EntireRow.Delete[/COLOR]
[COLOR=#333333]End With[/COLOR]

[COLOR=#333333]With Range("Y:Y")[/COLOR]
[COLOR=#333333].Replace "", "=GGG", xlWhole, , False, , False, False[/COLOR]
[COLOR=#333333].SpecialCells(xlFormulas, xlErrors).EntireRow.Delete[/COLOR]
[COLOR=#333333]End With[/COLOR]
 
Last edited by a moderator:

Watch MrExcel Video

Forum statistics

Threads
1,099,124
Messages
5,466,830
Members
406,501
Latest member
TheoDoc

This Week's Hot Topics

Top