#DIV/0! criteria

  • Thread starter Thread starter Newby
  • Start date Start date
N

Newby

I have a column range containing values and also some
#DIV/0! errors. I would like to loop through the range and
delete rows containg the #DIV/0!s
I have tried the following code:

Range("P").Cells(1).Select
Do Until Selection = ""
If Selection = "#DIV/0!" Then
Selection.EntireRow.Delete
End If
Selection.Offset(1).Select
Loop

This fails on the 2nd line when a #DIV/0! cell is
encountered. The loop will work successfully if I
insert 'On Error Resume Next' above line 2 but this is
dangerous since it will ignore other errors that I may not
wish to ignore. Please advise on a better way
TIA
Rodders
 
One way:

Public Sub DeleteDiv0Rows()
Dim delRange As Range
Application.ScreenUpdating = False
Range("1:1").Insert
On Error Resume Next
With Range("P1")
.Value = "temp"
.AutoFilter Field:=1, Criteria1:="#DIV/0!"
End With
Set delRange = Rows("2:2").Resize( _
Rows.Count - 1).SpecialCells(xlCellTypeVisible)
If Not delRange Is Nothing Then delRange.EntireRow.Delete
Selection.AutoFilter
Range("1:1").Delete
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

Another:

Public Sub DeleteDiv0Rows2()
Dim delRange As Range
Dim cell As Range
For Each cell In Range("P1:P" & _
Range("P" & Rows.Count).End(xlUp).Row)
If cell.Text = "#DIV/0!" Then
If delRange Is Nothing Then
Set delRange = cell
Else
Set delRange = Union(delRange, cell)
End If
End If
Next cell
If Not delRange Is Nothing Then delRange.EntireRow.Delete
End Sub

Note that you almost never need to select or activate a range in
order to work with it. Using the range object directly makes your
code smaller, faster and, IMO, easier to maintain.
 
Newby said:
I have a column range containing values and also some
#DIV/0! errors. I would like to loop through the range and
delete rows containg the #DIV/0!s
I have tried the following code:

Range("P").Cells(1).Select
Do Until Selection = ""
If Selection = "#DIV/0!" Then
Selection.EntireRow.Delete
End If
Selection.Offset(1).Select
Loop

This fails on the 2nd line when a #DIV/0! cell is
encountered. The loop will work successfully if I
insert 'On Error Resume Next' above line 2 but this is
dangerous since it will ignore other errors that I may not
wish to ignore. Please advise on a better way
TIA
Rodders
Click to expand...
Hi Rodders - how about searching in the column that creates the divide by
zero in the first place - ie contains zero?

Or you could change the formula to avoid a divide by zero in the first place
but instead creates a nonsense value, eg -9999999, then search for that in
your macro.

Geoff
 
Newby said:
I have a column range containing values and also some
#DIV/0! errors. I would like to loop through the range and
delete rows containg the #DIV/0!s
I have tried the following code:

Range("P").Cells(1).Select
Do Until Selection = ""
If Selection = "#DIV/0!" Then
Selection.EntireRow.Delete
End If
Selection.Offset(1).Select
Loop

This fails on the 2nd line when a #DIV/0! cell is
encountered. The loop will work successfully if I
insert 'On Error Resume Next' above line 2 but this is
dangerous since it will ignore other errors that I may not
wish to ignore. Please advise on a better way
TIA
Rodders
Click to expand...


Having now had the advantage of seeing J E McGimpsey's very full reply, I
can see what one problem is with your code. You have used Selection in lines
2 and 3 to return the cell's value. The .value property of the range is the
default property if you do not specify anything. If the value is in fact an
error, then that apparently causes an error in VBA (as you know).

If you look at J E McGimpsey's second example, you will see that he has used
the .text property, which does not cause an error. I think if you change
Selection to Selection.text your code will now work without causing an
error.

BTW, I am not suggesting that what J E McGimpsey has said is wrong in any
way. I am sure that this approach is much better in the long run. However,
as a beginner with VBA myself, I find it helpful to spot what actually is
specifically wrong with the code you had.

Incidentally, I ended up with this:

Public Sub test()
Dim MySelect, NextSelect As Range
Set MySelect = Range("P1")

Do Until MySelect.Text = ""

Set NextSelect = MySelect.Offset(1, 0)
If MySelect.Text = "#DIV/0!" Then
MySelect.EntireRow.Delete
End If
Set MySelect = NextSelect

Loop
End Sub
 
Public Sub test()
Dim MySelect, NextSelect As Range
Set MySelect = Range("P1")

Do Until MySelect.Text = ""

Set NextSelect = MySelect.Offset(1, 0)
If MySelect.Text = "#DIV/0!" Then
MySelect.EntireRow.Delete
End If
Set MySelect = NextSelect

Loop
End Sub
Click to expand...

Sorry to reply to my own post and bore anyone reading this, but the above
code does not work very well and it actually demonstrates why JEMcG was
saying that it is a bad idea to select the cell you want to delete. I think
that the above code does not delete both instances if you have two Div/0
cells one below the other.
 
Just another possibility:

If that is the only error in the column (you won't have #N/A or #Value for
instance)

Dim rng as Range
On Error Resume Next
set rng = Columns(16).SpecialCells(xlFormulas,xlErrors)
On Error goto 0
if not rng is nothing then
rng.EntireRow.Delete
End if
 
JE,
Thankyou very much indeed for your response. I find your
second method (below) to be compact and easy to read
(having looked up the methods/properties that are new to
me.
Please tell me, having obtained the multiple
range 'delrange', in addition to deleting 'delrange' how
could I delete the 'delrange row numbers across several
other worksheets?
TIA once more
Rodders
 
Tom,
Thanks for a super compact solution.
Just 2 questions if you could suffer me once more:
What is the effect of 'on error goto 0'
and : how would I extend the deletion of rows in rng say
on sheet1 to also delete the same row numbers in several
other sheets?
Thanks again
Rodders
 
Special Cells raises an error if no cells matching the criteria are found,
so

ON Error Resume Next
says to ignore the error

ON Error goto 0
says to do normal error handling, so an error is raised if one is
encountered. I like to have minimal time under On Error Resume Next, since
it can suppress errors which you need to see during development.

rng will hold the range of rows to be deleted, so you can do assign the
address to a string (sAddr)

Dim sAddr as String
Dim rng as Range
On Error Resume Next
set rng = Columns(16).SpecialCells(xlFormulas,xlErrors)
On Error goto 0

if not rng is nothing then
sAddr = rng.Address(0,0)
rng.EntireRow.Delete
for each sh in Worksheets(Array("sheet1","Sheet5","Sheet11")
sh.Range(sAddr).EntireRow.Delete
Next
End if

If there are too many cells, this could fail since using the string as an
argument to Range is limited. Here is an alternative which you might want to
use anyway:

This uses the grouped sheet approach which largely isn't supported in VBA,
but can sometime work by using the selection object. It appears to work in
this case (tested in xl2000)

assume sheet3 is the sheet where you will make the determination

Sub Tester2()
Dim sAddr As String
Dim rng As Range
Worksheets(Array("sheet3", "sheet1", "Sheet5", "Sheet11")).Select
Worksheets("Sheet3").Activate
On Error Resume Next
Set rng = ActiveSheet.Columns(16).SpecialCells(xlFormulas, xlErrors)
On Error GoTo 0

If Not rng Is Nothing Then
rng.EntireRow.Select
Selection.Delete
End If
Worksheets("Sheet3").Select
End Sub
 
Thanks Tom,
The grouped sheet approach and selection object works
great in Excel 97 too.
Once again,
thankyou
 
Back
Top