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

"Patrick Molloy" wrote:

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" wrote:

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").Spec ialCells(xlCellTypeBlanks)



"Patrick Molloy" wrote:

I assume that there are some blank rows?

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

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


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