excel - VBA macro delete cells containing #N/A and shift up the cells (not the rows)

Question

I'm creating a ranking and I need to delete all the cells with #N/A (pasted as text, not formula) and to delete those cells and shhift them up.

The worksheet contains 503 raws and I need it from column A to T.

Thanks in advance, I have tried so many VBA codes of this web and I'm not able to find something that works.

Answer 1

Try,

dim rng as range

with worksheets("sheet1")
    on error resume next
    set rng = .range("A:T").specialcells(xlcelltypeformulas, xlerrors)
    if not rng is nothing then
        rng.delete shift:=xlup
    end if
    set rng = .range("A:T").specialcells(xlcelltypeconstants, xlerrors)
    if not rng is nothing then
        rng.delete shift:=xlup
    end if        
    on error goto 0
end with