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

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


In article ,
"Steph" wrote:

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

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


"Steph" wrote:

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

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

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

"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

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

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




In article ,
"Steph" wrote:

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

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

"JE McGimpsey" wrote in message
...
You've not Dim'd rng as a Range object, then. Shame on you...




In article ,
"Steph" wrote:

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

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

"JE McGimpsey" wrote:

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

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.



In article ,
"Steph" wrote:

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

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

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.

In article ,
JE McGimpsey wrote:

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.

I second that emotion.

--
Regards,
Tom Ogilvy

"JE McGimpsey" wrote in message
...
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.

In article ,
JE McGimpsey wrote:

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.

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

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