Run-time error 1004 - No cells were found

[excelnewbie1986]

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!!

 

[Fluff]

Firstly, when posting code, please use code tags, the # icon in the reply window.
Secondly this

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,

combined with

And there are no cells that say #NAME either.

Makes no sense to me.
If the code found a value of G in col Z it would be replaced with #NAME? & the Specialcells line of code would then delete those rows. No Error message.
If the code did not find G then you would get the error message, but no rows would have been deleted.

Do you have any event code running on that sheet?

 

[excelnewbie1986]

Firstly, when posting code, please use code tags, the # icon in the reply window.
Secondly this combined with Makes no sense to me.
If the code found a value of G in col Z it would be replaced with #NAME ? & the Specialcells line of code would then delete those rows. No Error message.
If the code did not find G then you would get the error message, but no rows would have been deleted.

Do you have any event code running on that sheet?

Unfortunately, I don't know what event code is, sorry . I'm pretty sure you gave me that removal sub originally - and it worked perfectly. It just doesn't seem to want to work now. The raw data I put into the master spreadsheet always (to start with) has values of G in col Z. After I run the macro and get the runtime error, when I close it, all the values of G are removed (along with the row), but nothing else after that point.

 

[Fluff]

Are you sure that it's failing on Col Z, rather then on one of the later columns?

 

[excelnewbie1986]

Are you sure that it's failing on Col Z, rather then on one of the later columns?

It is failing on one of the later columns! My colleague had changed the SQL stored procedure to not collect Water's Edge and Green Abbey (which are test databases anyway).....he just failed to tell me. Removing those to sections of code and it now works perfectly again!

Thanks once again @Fluff

 

[Fluff]

Glad to help & thanks for the feedback