#REF! Halting my Macro

[JMay]

After selecting a range, say D539
where d20 might have = d25 and d25 is blank
the folowing code is stopping <<HERE-below>>on d20 - haven't I provided for
this?
TIA,

Sub DeleteRows()
Dim r As Long
With Selection
For r = .Cells.Count To 1 Step -1
With .Cells(r, 1)
HERE >>> If .Value = 0 Or .Value = "#REF!" Then .EntireRow.Delete
End With
Next
End With
End Sub

 

[PeterS]

As "#REF!" is no value but text, you could try to test for ISERROR(.Cells(r,1))

 

[JMay]

How "exactly" would I substitute ISERROR(.Cells(r,1)) into my existing code
below?
a bit unsure here...
TIA

 

[Myrna Larson]

If what you see in the cell is #REF!, then the cell contains an error value, not the text you
are seeing (and testing for). Try it this way:

With .Cells(r, 1)
If IsError(.Value) Then
.EntireRow.Delete
ElseIf .Value = 0 Then
.EntireRow.Delete
End If
End With

 

[JMay]

Thanks Myrna, "I-Got-It!! (tks to you)"

If what you see in the cell is #REF!, then the cell contains an error value, not the text you
are seeing (and testing for). Try it this way:

With .Cells(r, 1)
If IsError(.Value) Then
.EntireRow.Delete
ElseIf .Value = 0 Then
.EntireRow.Delete
End If
End With

 

[Tom Ogilvy]

Sub DeleteRows()
Dim r As Long
With Selection
For r = .Cells.Count To 1 Step -1
With .Cells(r, 1)
If .Value = 0 Or .Text = "#REF!" Then .EntireRow.Delete
End With
Next
End With
End Sub

will check for #REF specifically

Demo'd from the immediate window:

? activeCell.Text
#REF!
? activeCell.Text = "#REF!"
True

 

[JMay]

Tks Tom for the clarification...
JMay

Sub DeleteRows()
Dim r As Long
With Selection
For r = .Cells.Count To 1 Step -1
With .Cells(r, 1)
If .Value = 0 Or .Text = "#REF!" Then .EntireRow.Delete
End With
Next
End With
End Sub

will check for #REF specifically

Demo'd from the immediate window:

? activeCell.Text
#REF!
? activeCell.Text = "#REF!"
True

 

[Ron de Bruin]

Hi Tom

I was playing with union today and like to ask you this

This is working fast and great for the whole column A

Sub test2()
Dim cell As Range, Rng As Range
For Each cell In Range("A:A")
If cell.Value = 0 Or cell.Text = "ron" Then
If Rng Is Nothing Then
Set Rng = cell
Else
Set Rng = Union(Rng, cell)
End If
End If
Next
If Not Rng Is Nothing Then
Rng.EntireRow.Delete
End If
End Sub

But if I change it to this
If cell.Value = 0 Or cell.Text = "#REF!" Then
I get an error 13

Can you tell me why Tom

 

[Tom Ogilvy]

Well thanks for pointing that out. The first check (cell.Value = 0 ) would
give you the type mismatch.

it could be handled with

If cell.Text = "0" Or cell.Text = "#REF!" Then

so in JMay's case it would be the same alteration.

 

[Ron de Bruin]

Using Union is fast with 65536 records by the way Tom
5-6 seconds

 

[Ron de Bruin]

Hi Tom

You are right Tom I was testing with blocks of cells with the same value.
But it was fun to play with.(I learn things today)

I use the example you give with the dummy column also a lot.
I think it is the best way to do it.