Delete #REF! rows on 12 sheets

SlinkRN

Well-known Member
Joined
Oct 29, 2002
Messages
715
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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.
 
Upvote 0
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
 
Upvote 0
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. :rolleyes: Thanks, Slink
 
Upvote 0
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!
 
Upvote 0
BTW, I only really need it to search sheets 3 through 14. How would I adjust the code to do that?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top