Dim rng As Range
Dim rw As Long
Consol.Activate
rw = 5
Set rng = Range(Cells(5, "Q"), Cells(Rows.Count, "Q").End(xlUp))
For Each cell In rng
If cell.Value = "total" Then
cell.EntireRow.Cut Destination:=Worksheets("Total") _
.Cells(rw, 1)
rw = rw + 1
End If
Next
Set rng = Nothing ' <=== added line
On Error Resume Next
Set rng = Range(Cells(5, 4), Cells(Rows.Count, _
4).End(xlUp)).SpecialCells(xlCellTypeBlanks)
If Not rng Is Empty Then rng.EntireRow.Delete
On Error GoTo 0

--
Regards,
Tom Ogilvy


"Steph" wrote in message
...
Hi guys,

Thanks for all of your responses. And you're right...makes no difference.
I was testing your code change on a small sample worksheet. A very
strange
thing is happening - my entire procedure is below. When I run the whole
thing, it deletes all rows on the sheet. BUT, when I comment out the top
part, it works perfectly and does not delete any rows. What is in the top
part that is messing me up?!

Dim rng As Range
Dim rw As Long
Consol.Activate
rw = 5
Set rng = Range(Cells(5, "Q"), Cells(Rows.Count, "Q").End(xlUp))
For Each cell In rng
If cell.Value = "total" Then
cell.EntireRow.Cut Destination:=Worksheets("Total") _
.Cells(rw, 1)
rw = rw + 1
End If
Next

On Error Resume Next
Set rng = Range(Cells(5, 4), Cells(Rows.Count, _
4).End(xlUp)).SpecialCells(xlCellTypeBlanks)
If Not rng Is Empty Then rng.EntireRow.Delete
On Error GoTo 0

"Tom Ogilvy" wrote in message
...
I don't see any difference.

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
I think I got it. If you change the word Nothing to Empty, it works
perfectly.

"JE McGimpsey" wrote in message
...
Did you try it? It has the same problem - it deletes all the cells
in
the range if there are no blanks.

In article ,
"Jim Thomlinson" wrote:

On Error Resume Next
Set rng = Range(Cells(5, "D"), Cells(Rows.Count,
"D").End(xlUp))
Set rng = rng.SpecialCells(xlBlanks)

if not rng is nothing then rng.EntireRow.Delete
On Error GoTo 0