Delete rows with #REF!

E

Eva Shanley

One of the departments here has a spreadsheet in which
they add or delete stores on a pretty regular basis; the
store numbers are used in at least 5 sheets. I have a
store table that copies the stores to each of the sheets,
but my problem is that if stores have been deleted, the
last few rows show #REF!. I want to delete the rows with
#REF! in them, but my code runs okay until the first #REF!
and then I get a Type mismatch error on line 2. Any help
is always appreciated. TIA

Range("D8").Select
Do Until ActiveCell = "" -Type mismatch error
If ActiveCell <> "#REF!" Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Delete
ActiveCell.Offset(0, 6).Select
End If
Loop
 
B

Bob Phillips

Eva,

The cell does not contain #Ref, that is just the presentation of it.
haven't tested this as I cannot figure out what would create a #REF, but try
it and let me know

Range("D8").Select
Do Until ActiveCell = ""
If ActiveCell <> CvErr(xlErrRef) Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Delete
ActiveCell.Offset(0, 6).Select
End If
Loop
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
K

Ken Wright

Why not delete them first. Perhaps something like this:-

Sub DelErrors()
On Error Resume Next ' In case there are no errors
Columns("A:A").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete

.....Rest of your stuff in here

End Sub
 
E

Eva Shanley

Hi Bob, thanks for the response. #Ref! shows up when a
store is deleted and there's a formula in a cell that
refers to the cell the deleted store was in. I tried your
code but I get a Type Mismatch error on Line 3. I'll
think of something eventually; this spreadsheet is really
bizarre anyway.
 
T

Tom Ogilvy

Range("D8").Select
Do Until ActiveCell = ""
if IsError(ActiveCell) then
If ActiveCell = CvErr(xlErrRef) Then
ActiveCell.EntireRow.Delete
End if
Else
ActiveCell.Offset(1, 0).Select
End If
Loop


Another approach

Dim rng as Range, rng1 as Range, rng2 as Range
Dim cell as Range
On Error Resume Next
set rng =Range(Range("D8"),Cells(rows.count,4).End(xlup))
set rng1 = rng.specialcell(xlformulas,xlErrors)
On Error goto 0
if not rng1 is nothing then
for each cell in rng1
if cell.Value = cvErr(xlErrRef) then
if rng2 is nothing then
set rng2 = cell
else
set rng2 = union(rng2,cell)
end if
end if
next
if not rng2 is nothing then
rng2.EntireRow.Delete
End if
End if
 
E

Eva Shanley

Thanks so much; great idea and it's working fine. I
substituted xlErrors for the 16 in the value just playing
around and it still worked; I'm curious as to what the 16
represents?
 
B

Bob Phillips

Eva,

I tried it now, and I get the Type Mismatch on the Do line, my code addition
worked. On that basis, try

Range("D8").Select
On Error Resume Next
Do Until ActiveCell = ""
If ActiveCell <> CVErr(xlErrRef) Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Delete
ActiveCell.Offset(0, 6).Select
End If
Loop


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
E

Eva Shanley

Thanks Tom, that's what I thought it probably was. How can
I get the code to check the entire workbook and delete the
rows and not just 1 column?
-----Original Message-----
That would delete any cell with an Error, not just those cells with #REF

--
Regards,
Tom Ogilvy

Why not delete them first. Perhaps something like this:-

Sub DelErrors()
On Error Resume Next ' In case there are no errors
Columns("A:A").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete

.....Rest of your stuff in here

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

--------------------------------------------------------
Click to expand...
------------------ ------------------

.
Click to expand...
 
K

Ken Wright

True, and thanks for the clarification Tom, as I should have made that clear in
my response, though from the OPs text I'm making the assumption that there will
be nothing else below that last set of #REFs anyway, so I don't think she will
be impacted.

Eva, as said above, I'm making assumptions here, but if there is any other data
below these #REFs that could potentially contain errors, then Tom's caveat means
you have to take my solution with a pinch of salt.

Re the 16, it is a constant for errors in the SpecialCells method

xlErrors - 16
xlLogical - 4
xlNumbers - 1
xlTextValues - 2

To get any combination of these you simply add the numbers, eg:-

xlErrors + xlLogical = 20
xlNumbers + xlErrors + xlLogical = 21

etc (Selecting all will be 23)
 
T

Tom Ogilvy

? xlerrors
16

--
Regards,
Tom Ogilvy

Eva Shanley said:
Thanks so much; great idea and it's working fine. I
substituted xlErrors for the 16 in the value just playing
around and it still worked; I'm curious as to what the 16
represents?
Click to expand...
 
T

Tom Ogilvy

What I said was that that will delete any "row" that contains an error value
produced by a formula in column A (as written). So if you had a #Div/0!,
then that row is gone as well. If you will only have #Ref, then no need
for concern.

--
Regards,
Tom Ogilvy



Eva Shanley said:
Thanks Tom, that's what I thought it probably was. How can
I get the code to check the entire workbook and delete the
rows and not just 1 column?
-----Original Message-----
That would delete any cell with an Error, not just those cells with #REF

--
Regards,
Tom Ogilvy

Why not delete them first. Perhaps something like this:-

Sub DelErrors()
On Error Resume Next ' In case there are no errors
Columns("A:A").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete

.....Rest of your stuff in here

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

--------------------------------------------------------
Click to expand...
------------------ ------------------
--



One of the departments here has a spreadsheet in which
they add or delete stores on a pretty regular basis; the
store numbers are used in at least 5 sheets. I have a
store table that copies the stores to each of the sheets,
but my problem is that if stores have been deleted, the
last few rows show #REF!. I want to delete the rows with
#REF! in them, but my code runs okay until the first #REF!
and then I get a Type mismatch error on line 2. Any help
is always appreciated. TIA

Range("D8").Select
Do Until ActiveCell = "" -Type mismatch error
If ActiveCell <> "#REF!" Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Delete
ActiveCell.Offset(0, 6).Select
End If
Loop
Click to expand...


.
Click to expand...
Click to expand...
 
K

Ken Wright

It does delete the entire rows. What Tom was pointing out was that it delete
ALL rows with ANY error in that column, and so hence the caveats in Tom's
response and my follow up.

When you say check the entire workbook, do you mean workbook, or worksheet, and
if you meant worksheet do you really mean worksheet, or is it just entire rows
you want to delete, based on finding errors in that particular column, in which
case that is what you have :)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :)
----------------------------------------------------------------------------



Eva Shanley said:
Thanks Tom, that's what I thought it probably was. How can
I get the code to check the entire workbook and delete the
rows and not just 1 column?
-----Original Message-----
That would delete any cell with an Error, not just those cells with #REF

--
Regards,
Tom Ogilvy

Why not delete them first. Perhaps something like this:-

Sub DelErrors()
On Error Resume Next ' In case there are no errors
Columns("A:A").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete

.....Rest of your stuff in here

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

--------------------------------------------------------
Click to expand...
------------------ ------------------
--



One of the departments here has a spreadsheet in which
they add or delete stores on a pretty regular basis; the
store numbers are used in at least 5 sheets. I have a
store table that copies the stores to each of the sheets,
but my problem is that if stores have been deleted, the
last few rows show #REF!. I want to delete the rows with
#REF! in them, but my code runs okay until the first #REF!
and then I get a Type mismatch error on line 2. Any help
is always appreciated. TIA

Range("D8").Select
Do Until ActiveCell = "" -Type mismatch error
If ActiveCell <> "#REF!" Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Delete
ActiveCell.Offset(0, 6).Select
End If
Loop
Click to expand...


.
Click to expand...
Click to expand...
 
K

Ken Wright

The other caveat I should have included, was that directly after using special
cells, you should close off the 'On error resume next' bit with an On error goto
0, then include your own code to finish off
 
E

Eva Shanley

Tom and Ken, thanks for all the help. I don't expect to
have any errors other than #REF! so I should be okay. I
didn't make myself very clear when I asked about checking
the workbook. This file is pretty goofy the way it's
layed out, and since the store numbers exist on several
sheets, when a store gets deleted from the table, all the
cells in all the sheets referring to that store result in
#REF!. So, I'd like to delete the rows with #REF! in them
on all sheets in the workbook. Thanks again, if it wasn't
for this group I'd be dead.
-----Original Message-----
It does delete the entire rows. What Tom was pointing out was that it delete
ALL rows with ANY error in that column, and so hence the caveats in Tom's
response and my follow up.

When you say check the entire workbook, do you mean workbook, or worksheet, and
if you meant worksheet do you really mean worksheet, or is it just entire rows
you want to delete, based on finding errors in that particular column, in which
case that is what you have :)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

---------------------------------------------------------- ------------------
It's easier to beg forgiveness than ask permission :)
---------------------------------------------------------- ------------------



Thanks Tom, that's what I thought it probably was. How can
I get the code to check the entire workbook and delete the
rows and not just 1 column?
-----Original Message-----
That would delete any cell with an Error, not just
Click to expand...
those
cells with #REF
--
Regards,
Tom Ogilvy

Why not delete them first. Perhaps something like this:-

Sub DelErrors()
On Error Resume Next ' In case there are no errors
16).EntireRow.Delete

.....Rest of your stuff in here

End Sub
Click to expand...
MVP -
Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

-----------------------------------------------------
Click to expand...
---
------------------ ---
------------------
--



One of the departments here has a spreadsheet in which
they add or delete stores on a pretty regular
Click to expand...
basis;
the
store numbers are used in at least 5 sheets. I
Click to expand...
have
a
store table that copies the stores to each of the sheets,
but my problem is that if stores have been deleted, the
last few rows show #REF!. I want to delete the rows with
#REF! in them, but my code runs okay until the
Click to expand...
first
#REF!
and then I get a Type mismatch error on line 2. Any help
is always appreciated. TIA

Range("D8").Select
Do Until ActiveCell = "" -Type mismatch error
If ActiveCell <> "#REF!" Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Delete
ActiveCell.Offset(0, 6).Select
End If
Loop
Click to expand...
Date:
18/03/2004
.
Click to expand...
Click to expand...


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.634 / Virus Database: 406 - Release Date: 18/03/2004


.
Click to expand...
 
T

Tom Ogilvy

In case you are saying this as a question
Dim sh as Worksheet
Dim rng as Range
for each sh in thisworkbook.worksheets
set rng = nothing
on Error resume Next
set rng = sh.Cells.SpecialCells(xlFormulas,xlErrors)
On Error goto 0
if not rng is nothing then
rng.EntireRow.Delete
end if
Next

Or you can just bull your way through with the shorter

Dim sh as Worksheets
On Error Resume next
for each sh in thisworkbook.worksheets
sh.Cells.SpecialCells(xlFormulas,xlErrors).Entirerow.Delete
Next
On Error goto 0
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How can I indicate in a macro to which last sheet to return ? 9
macro 2
Help me4 2
Can Vba workout if a selection is part of a group? 0
Loop Error Trap 3
"Object Library Invalid" Error occurring...why? 1
Loop Macro 2
Loop Repeats - PLS HELP 2

Top