How to remove #N/A that appears through out a work

Question

I have to clear #N/A that happens through out my worksheet when ever i run my code. I'm not sure why and have been debugging for a while but to no avail. What i could do to remedy this problem is to delete it entirely from the page, where they happens randomly. If anyone know how to, do share a VBA code with me.

Codes of doing a simple copy and paste into another sheet

thevaluestocopy = Sheets("pivot").Cells(thefirstrow, 1) _
   .Resize(thelastrow - thefirstrow, 1)
    Worksheets("summary").Cells(3, 16) _
   .Resize(thelastrow - thefirstrow + 1, 16) = thevaluestocopy

I have nested that code with different Column because my pivot table changes most of the time. And when i copied for the 2nd time, #N/A appears.. Have no idea why and i believe this works work fine.

Answer 1

I don't know, but how about doing something like this instead? This is how I generally copy and paste, without problems.

Sheets("pivot").Range("your range").Copy
Worksheets("summary").Range("your range").PasteSpecial

UPDATE If you still want to simply remove all the #N/As with your current code, you can use some code like this.

If WorksheetFunction.IsNA(Cells(row, column)) Then Cells(row, column).ClearContents

Answer 2

You can run this line to remove any #N/A error values in the worksheet:

Cells.Replace "#N/A","",xlWhole

If the worksheet contains formulas you can try this:

Cells.SpecialCells(xlCellTypeFormulas,xlErrors).Clear