Macro to select & clear (not delete) #REF errors.

ValiMandy

New Member
Joined
May 10, 2016
Messages
31
Hello,

I feel like I am so close to this but I think I am not adept enough at macros to get it right enough to work. And, I have searched high and low and found everything but what I am looking for. I also tried to use the macro recorder but as I have learned it won't work for this. I know how to do it manually through "Find" however I will not be using this spreadsheet and the people who will be need as few steps as possible.

I need a macro that will find all #REF errors in range A1:L756 and clear the cells. I don't want the cells deleted but I am fine with the formatting, contents, etc all cleared out.

Thank you very much for your help!

Mandy
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Hi Mandy,

How about using something like the following vba.

Code:
Sub RemoveRefError(rng As Range)   
    Dim cell As Range
    For Each cell In rng
        If InStr(1, cell.Formula, "REF!", vbTextCompare) Then cell.Clear
    Next cell
End Sub

Sub test()
    Dim rng As Range: Set rng = Sheet1.Range("A1:L756")
    Call RemoveRefError(rng)
End Sub

Presuming it is Sheet1 that has the data. I am sure there is much better ways, and lots of error trapping which could be introduced. But this should do the job.

Regards
Chris
 
Upvote 0
Chris,

Hey! It worked! Thank you very much!! I'm loving learning how these macros work and each time I have a new problem it pushes me closer to knowing what I'm doing! I really love having these forums to help me get there! Thanks again!

Mandy



Hi Mandy,

How about using something like the following vba.

Code:
Sub RemoveRefError(rng As Range)   
    Dim cell As Range
    For Each cell In rng
        If InStr(1, cell.Formula, "REF!", vbTextCompare) Then cell.Clear
    Next cell
End Sub

Sub test()
    Dim rng As Range: Set rng = Sheet1.Range("A1:L756")
    Call RemoveRefError(rng)
End Sub

Presuming it is Sheet1 that has the data. I am sure there is much better ways, and lots of error trapping which could be introduced. But this should do the job.

Regards
Chris
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

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