Purify blank cells on Excel

Question

first of all: I am using Excel 2007 with a .XLS file.

I have a column that contains some cells with values but other random cells are in "blank". But when I do ISBLANK on this cells it returns me FALSE on all of them. So I assume they contain a value, and I want them really blank (only the cells I see without values).

I already tried replacing spaces with nothing, and nothing with nothing but still not working.

Thanks.

So you are trying to create a truly empty cell? Just hit the DELETE key when selected? — BruceWayne
What happens if you just click on the cell and click delete on your keyboard? — jcarroll
In a empty column use this =Trim(Clean(A1)) and copy down. Where A1 is the first cell in your column you are testing. Then copy and paste the values back. — Scott Craner

Gary's Student Gary's Student · Accepted Answer · 2016-06-30T16:37:37

Select the cells you wish to cleanup and run:

Sub KleanUp()
    Dim rng As Range, r As Range

    Set rng = Intersect(Selection, ActiveSheet.UsedRange)
    For Each r In rng
        If r.Value = "" Then r.Clear
    Next r
End Sub

Purify blank cells on Excel

1 Answers