Delete #REF! rows on 12 sheets

[SlinkRN]

Hi!
I have 12 sheets that are linked to my first sheet and when I delete a row on the first sheet the linked rows on the other 12 sheets come up with #REF! I need to write a macro that will delete all the #REF! rows on the 12 sheets. Any ideas?
Thanks! Slink

 

[Smitty]

sorry - no time now, but try INDIRECT

smitty

 

[nisht]

Select sheet where you have error.

Goto

Edit -> Goto - > special - > formula -> Errors

Now, rightclick any cell which is selected and then select Delete

you will be give the options.

this is semi-automatic method.

 

[nisht]

Try this procedure.. take backup beforey you try.


Sub sr10()
Dim sht As Worksheet

For Each sht In Worksheets
Debug.Print sht.Name
sht.Activate
sht.Select

' This selects the cell with formula having error

On Error Resume Next ' if special cells are not found it will create error.
Cells.SpecialCells(xlCellTypeFormulas, 16).Interior.ColorIndex = 38

' Change your logic as shown below if you want to delete the rows
' I have not tested this line of code. SO take backup of your file
' and then test this procedure. uncomment it and see if it works.
'Cells.SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete

Next sht
End Sub

 

[SlinkRN]

ADVERTISEMENT

Thanks for your replies Nisht, code is really what I need. I tried the code in your last reply. It searches all the sheets, but it didn't delete or color them (I tried both ways). I put the code in the workbook and then I tried it in a worksheet, but neither way deleted the rows. Thanks, Slink

 

[SlinkRN]

My apologies! Some of my sheets were protected which is why the delete didn't happen. After adding code to unprotect the sheets, your code works perfectly. Thank you so much!

 

[SlinkRN]

ADVERTISEMENT

BTW, I only really need it to search sheets 3 through 14. How would I adjust the code to do that?

 

[nisht]

Sub sr10()
Dim sht As Worksheet

For Each sht In Worksheets
Debug.Print sht.Name
select case sht.index
case 3 to 14 ' your 3 to 14 sheets are only checked. you can change the ' code to 2, 4 to 14 if you want to check 2 and 4 to 14 sheets

sht.Activate
sht.Select

' This selects the cell with formula having error

On Error Resume Next ' if special cells are not found it will create error.
Cells.SpecialCells(xlCellTypeFormulas, 16).Interior.ColorIndex = 38

' Change your logic as shown below if you want to delete the rows
' I have not tested this line of code. SO take backup of your file
' and then test this procedure. uncomment it and see if it works.
'Cells.SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
caseelse

Next sht
End Sub


Please note: I have not checked this code.

 

[SlinkRN]

Thanks! I'll give that a try!