I'd check for empty cells with something like:

dim myRng as range
set myrng = nothing
on error resume next
set myrng = ....
on error goto 0

if myrng is nothing then
'do nothing
else
myrng.entirerow.delete
end if

or just turn off error checking before your delete statement.

On error resume next
worksheets....entirerow.delete
on error goto 0

================
Saved from a previous post:

If you want to see what's left in that cell after you convert ="" to values,
try:
Tools|Options|Transition Tab|Toggle Transition Navigation keys on.

Then select one of those cells and look at the formula bar. You'll see an
apostrophe. (Don't forget to toggle the setting to off.)

When I want to clean up this detritus, I do this:

Select the range (ctrl-a a few times to select all the cells)
Edit|Replace
what: (leave blank)
with: $$$$$
replace all

Immediately followed by:
Edit|Replace
what: $$$$$
with: (leave blank)
replace all

In code you could do something like:

Option Explicit
Sub testme()
With ActiveSheet
With .cells 'or a specific range: With .Range("D:D")
.Replace what:="", replacement:="$$$$$", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
.Replace what:="$$$$$", replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
End With
End With
End Sub

========
now those "empty" cells will really be empty and your .delete statement should
work.

Code Numpty wrote:

I have this macro to tidy up data in columns which leaves blank rows at the
end of the range. I want to delete the blank rows but the line

Worksheets("Disinfections").Range("pen_list").Spec ialCells(xlCellTypeBlanks).EntireRow.Delete

Results in error 1004 No Cells Found.

Here is the macro code
---------------------------------------------
Sub sort_disinfection_columns()

Worksheets("Disinfections").Range("pen_list").Copy
Worksheets("Disinfections").Range("pen_list").Past eSpecial
Paste:=xlPasteValues
Range("A3:A52").Sort Key1:=Range("A3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("B3:B52").Sort Key1:=Range("B3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("C3:C52").Sort Key1:=Range("C3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("D3:D52").Sort Key1:=Range("D3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("E3:E52").Sort Key1:=Range("E3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("F3:F52").Sort Key1:=Range("F3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("G3:G52").Sort Key1:=Range("G3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("H3:H52").Sort Key1:=Range("H3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("I3:I52").Sort Key1:=Range("I3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("A1").Select

'Delete blank rows

Worksheets("Disinfections").Range("pen_list").Spec ialCells(xlCellTypeBlanks).EntireRow.Delete

'Format remaining rows

Range("pen_list").Select
With Selection.Font
.Name = "Arial Black"
.Size = 24
End With
Range("A1").Select
End Sub

---------------------------------------------

I've used this elsewhere with no problem so grateful for any help.

--

Dave Peterson