#REF! Halting my Macro
 

After selecting a range, say D5:D39
where d20 might have = d25 and d25 is blank
the folowing code is stopping <<HERE-belowon 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

#REF! Halting my Macro
 

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

--
PeterS

"JMay" schreef in bericht news:Kcgbb.4441$AH4.1113@lakeread06...
After selecting a range, say D5:D39
where d20 might have = d25 and d25 is blank
the folowing code is stopping <<HERE-belowon 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




---
Peter's outgoing mail is certified 100% Virus Free. (AVG)
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.518 / Virus Database: 316 - Release Date: 11-9-2003

#REF! Halting my Macro
 

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

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

--
PeterS

"JMay" schreef in bericht
news:Kcgbb.4441$AH4.1113@lakeread06...
After selecting a range, say D5:D39
where d20 might have = d25 and d25 is blank
the folowing code is stopping <<HERE-belowon 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




---
Peter's outgoing mail is certified 100% Virus Free. (AVG)
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.518 / Virus Database: 316 - Release Date: 11-9-2003

#REF! Halting my Macro
 

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


On Sun, 21 Sep 2003 07:46:04 -0400, "JMay" wrote:

After selecting a range, say D5:D39
where d20 might have = d25 and d25 is blank
the folowing code is stopping <<HERE-belowon 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

#REF! Halting my Macro
 

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

"Myrna Larson" wrote in message
...
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


On Sun, 21 Sep 2003 07:46:04 -0400, "JMay" wrote:

After selecting a range, say D5:D39
where d20 might have = d25 and d25 is blank
the folowing code is stopping <<HERE-belowon 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

#REF! Halting my Macro
 

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



--
Regards,
Tom Ogilvy


JMay wrote in message news:Kcgbb.4441$AH4.1113@lakeread06...
After selecting a range, say D5:D39
where d20 might have = d25 and d25 is blank
the folowing code is stopping <<HERE-belowon 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

#REF! Halting my Macro
 

Tks Tom for the clarification...
JMay

"Tom Ogilvy" wrote in message
...
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



--
Regards,
Tom Ogilvy


JMay wrote in message
news:Kcgbb.4441$AH4.1113@lakeread06...
After selecting a range, say D5:D39
where d20 might have = d25 and d25 is blank
the folowing code is stopping <<HERE-belowon 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

#REF! Halting my Macro
 

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





--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Tom Ogilvy" wrote in message ...
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



--
Regards,
Tom Ogilvy


JMay wrote in message news:Kcgbb.4441$AH4.1113@lakeread06...
After selecting a range, say D5:D39
where d20 might have = d25 and d25 is blank
the folowing code is stopping <<HERE-belowon 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

#REF! Halting my Macro
 

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.

--
Regards,
Tom Ogilvy


Ron de Bruin wrote in message
...
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





--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Tom Ogilvy" wrote in message
...
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



--
Regards,
Tom Ogilvy


JMay wrote in message
news:Kcgbb.4441$AH4.1113@lakeread06...
After selecting a range, say D5:D39
where d20 might have = d25 and d25 is blank
the folowing code is stopping <<HERE-belowon 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

#REF! Halting my Macro
 

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


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Tom Ogilvy" wrote in message ...
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.

--
Regards,
Tom Ogilvy


Ron de Bruin wrote in message
...
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





--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Tom Ogilvy" wrote in message
...
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



--
Regards,
Tom Ogilvy


JMay wrote in message
news:Kcgbb.4441$AH4.1113@lakeread06...
After selecting a range, say D5:D39
where d20 might have = d25 and d25 is blank
the folowing code is stopping <<HERE-belowon 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

#REF! Halting my Macro
 

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.


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Tom Ogilvy" wrote in message ...
It depends on how many unions you perform. I tested it where it unioned
every other cell in a single column (one at a time) as I recall, on a 2.4Ghz
machine and it took over 24 hrs to complete - and then crashed.

I like to use a formula

=if(condition,na(),"")

fill it down, then do

columns(1).specialcells(xlformulas,xlerrors).Entir eRow.delete

Use a dummy column in your code to put in the formula, then delete the
column after the above.


--
Regards,
Tom Ogilvy


Ron de Bruin wrote in message
...
Using Union is fast with 65536 records by the way Tom
5-6 seconds


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


Ron de Bruin wrote in message
...
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





--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Tom Ogilvy" wrote in message
...
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



--
Regards,
Tom Ogilvy


JMay wrote in message
news:Kcgbb.4441$AH4.1113@lakeread06...
After selecting a range, say D5:D39
where d20 might have = d25 and d25 is blank
the folowing code is stopping <<HERE-belowon 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