• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

macro to remove # N/A over large database

R

RAM72

Member
Hi all Guys
Am looking a macro to remove # N/A in cells, also some cells have an excel formula in it.

The #N/A is split on continious and non continious ranges from A2 to BF2 .

The maximum range is 15000 rows of data which comes from an another interface,

So the macro should be able to highlight light blue background to advise me where # N/A has been removed .

Thanks those can help me
 
Hi RAM72
Try this code that highlight the cells with that #N/A error and the clear the contents of these cells
Code: 
Sub HighlightRemoveNAError()
  On Error Resume Next
  With Union(Cells.SpecialCells(2, 16), Cells.SpecialCells(-4123, 16))
  .Interior.ColorIndex = 33
  .ClearContents
  End With
  On Error GoTo 0
End Sub

Or simply you can select all the cells and then use conditional formatting and put this formula
Code: 
=ISNA(A1)
 
Last edited:
YasserKhalil said:
Hi RAM72
Try this code that highlight the cells with that #N/A error and the clear the contents of these cells
Code: 
Sub HighlightRemoveNAError()
  On Error Resume Next
  With Union(Cells.SpecialCells(2, 16), Cells.SpecialCells(-4123, 16))
  .Interior.ColorIndex = 33
  .ClearContents
  End With
  On Error GoTo 0
End Sub

Or simply you can select all the cells and then use conditional formatting and put this formula
Code: 
=ISNA(A1)
Click to expand...

Hi YasserKhalil

Tested on dummy database thanks for your help works marvelously.

Saved from me a daily headache.

thanks
RAM1972
 
@Deepak
Highlighting is just part of OP's request as he said "to highlight light blue background" and he could clear contents or to comment this line to just detect the errors .. or comment the line of highlighting to just clear the errors
 
You must log in or register to reply here.
Back
Top