Removing blank and error cells from a range of cells

[j_e_r16]

Hi,

I have a worksheet with one word per cell, but with error (N/A) cells and blank cells intermittently dispersed throughout.
To remove these blank/error cells, I have been going to
Edit-->Go to--> Special --> Blank or Constant,error
and then Deleting these cells by shifting them up. This is a very time consuming process, though, because you can only select a certain amount of these cells at a time, so I have to do this for about 3 columns at a time for a worksheet filled up through the I* columns.

Does anyone know of a better way to remove these blank/error cells? Perhaps code for VBE?

Thanks!!

 

[schielrn]

I would start by recording a macro doing what you are already doing. That may give you the macro that will do that everytime. I would see that being the easiest until someone else paste's the code you actually need.

 

[royUK]

try using Special Cells & Application.Union

Dim rBlanks As Range
    Dim rErrors As Range
    Dim TheRng As Range
    With ActiveSheet
        Set rBlanks = .Cells.SpecialCells(xlCellTypeBlanks)
'16 limits to Errors
        Set rErrors = .Cells.SpecialCells(xlCellTypeFormulas, 16)
    End With
    Set TheRng = Application.Union(rBlanks, rErrors)
    'do something with the range
    MsgBox BigRng.Address