Deleting a row - followup question

[carrera]

I was asking earlier about trying to delete a row on one sheet, that is
referenced to another cell on a summary sheet.

Jim T. said it causes the reference to become invalid, so the cell on the
summary sheet displays #REF!

I'm asking now about a macro that would automatically delete whatever row is
now invalid. How would I state that no matter which cell/row is now #REF!,
to delete that row. In other words, if a cell is #REF!, delete that row.

Thanks again.

 

[Dave Peterson]

Start from the bottom row and work your way up.

Dim iRow as long
with worksheets("Somesheethere")
for irow = .cells(.rows.count,"A").end(xlup).row to 1 step -1
if lcase(.cells(irow,"A").text) = lcase("#ref!") then
.rows(irow).delete
end if
next irow
end with

I used column A to determine the last row and to look for the reference error.

 

[carrera]

I already have a macro in place to print the sheet out with all the rows
where there is data in a particular cell in the row.

Can I just take this macro starting with Dim iRow....and paste it to the end
of the print macro? I mean before the "end sub" line.

Also, what do you mean "start from the bottom and work your way up"? you
mean I have to do this to every row?!!!?


Boy, I know just enough to be dangerous.

 

[Dave Peterson]

I'd move the Dim statement to the top of the code--that's where I put all my
declarations.

Looping from the bottom up is one way to inspect every cell to see what they
contain.

If you can get rid of all the errors (ref, value, div/0, ...), you could use
something like:

on error resume next
worksheets("somesheetnamehere").range("a1").entirecolumn _
.cells.specialcells(xlCellTypeFormulas, xlerrors).entirerow.delete
on error goto 0

It's looking for any error in any formula in any cell in column A.