SpecialCells(xlCellTypeBlanks) = error 1004 No cells found

[Code Numpty]

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").SpecialCells(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").PasteSpecial
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("D352").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").SpecialCells(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

 

[Patrick Molloy]

I assume that there are some blank rows?

try this
dim found as range
set found =
Worksheets("Disinfections").Range("pen_list").SpecialCells(xlCellTypeBlanks).

if not found is nothing then
found.rows.delete
set found = nothing ' release memory
end ig

 

[Code Numpty]

Thank you Patrick
I must be missing something fairly simple here.

My range pen_list originally contains lookup formulas. The macro converts
the formulas to data and then sorts the columns so that all data is at the
top of the range. As I have converted formulas to data the remaining rows at
the bottom of the range are now blank - or at least I thought they were :-(

Your code gives me the no cells found error on the line

Set found =
Worksheets("Disinfections").Range("pen_list").SpecialCells(xlCellTypeBlanks)

 

[Patrick Molloy]

well as a trial, i had a column of data rangenamed, cleared the last ffew
rows and this workd exactly as written on the tin.

Are you sure that the cells are empty, maybe a space in there which yuo
can't see?

BUT, you're right. Interstingly, this failed when I copy/pastespecial. hmmmm

maby skip the cut/paste part?

 

[Code Numpty]

Hmm,
I used copy/paste special to get the data (simply numbers), rather than
formulas into the cells so that the data sort worked.

 

[Dave Peterson]

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")
.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.