EntireRow.Delete

[Steph]

Hello. Anyone see a problem with the code below? It deletes an entire row
if cell D is blank. It works great if the range has a few blank cells in
it. BUT, if there are no blank cells in the range, it deletes the entire
range, and I have no idea why?! Help??

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

 

[JE McGimpsey]

Since you have on Error Resume Next, when SpecialCells returns an error
(i.e., no blank cells), rng is still defined as the entire range, so it
gets deleted. Try:


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

 

[Guest]

Try this...

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

HTH

 

[JE McGimpsey]

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

 

[Steph]

Yep, just tried it, and it did the same thing.

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

 

[Steph]

I think I got it. If you change the word Nothing to Empty, it works
perfectly.

 

[JE McGimpsey]

You've not Dim'd rng as a Range object, then. Shame on you...

 

[Steph]

But that's the strange thing....I do have rng Dim'd as Range!

 

[Guest]

Oops sorry. I had the same problem once before and fixed it. I must have used
empty not nothing... Sorry...

 

[JE McGimpsey]

Then you have something amiss. Empty is only valid for Variants. If rng
is a Range object, you should use

If Not rng Is Nothing Then...

Note the use of Is rather than =.

By using

If not rng = Empty

you're comparing rng's default .Value property (i.e., the value stored
in rng) to Empty. If the cell is blank, True will be returned. If not,
you'll get False. In either case, it has nothing to do with whether rng
was set correctly.

 

[Tom Ogilvy]

I don't see any difference.

 

[JE McGimpsey]

Disregard - you said you changed Nothing to Empty, but I assumed you
meant you changed

If Not rng Is Nothing Then...

to

If Not rng = Empty Then...

If you use

If Not rng Is Empty

instead, I don't see any change in behavior.

 

[Tom Ogilvy]

I second that emotion.

--
Regards,
Tom Ogilvy

Disregard - you said you changed Nothing to Empty, but I assumed you
meant you changed

If Not rng Is Nothing Then...

to

If Not rng = Empty Then...

If you use

If Not rng Is Empty

instead, I don't see any change in behavior.

 

[Steph]

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]

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