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.

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

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

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