Excel telling me my blank cells aren't blank

excel, vba

Solution

A revelation: Some blank cells are not actually blank! As I will show cells can have spaces, newlines and true empty:

To find these cells quickly you can do a few things.

- The `=CODE(A1)` formula will return a #VALUE! if the cell is truly empty, otherwise a number will return. This number is the ASCII number used in `=CHAR(32)`.

- If you select the cell and click in the formula bar and use the cursor to select all.

Removing these:

If you only have a space in the cells these can be removed easily using:

- Press ctrl + h to open find and replace.

- Enter one space in the find what, leave replace with empty and ensure you have match entire cell contents is ticked in the options.

- Press replace all.

If you have newlines this is more difficult and requires VBA:

- Right click on the sheet tab > view code.

Then enter the following code. Remember the `Chr(10)` is a newline only replace this as required, e.g. `" " & Char(10)` is a space and a newline:

Sub find_newlines()
    With Me.Cells
        Set c = .Find(Chr(10), LookIn:=xlValues, LookAt:=xlWhole)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                c.Value = ""
                Set c = .FindNext(c)
                If c Is Nothing Then Exit Do
            Loop While c.Address <> firstAddress
        End If
    End With
End Sub

Now run your code pressing F5.

After file supplied: Select the range of interest for improved performance, then run the following:

Sub find_newlines()
    With Selection
        Set c = .Find("", LookIn:=xlValues, LookAt:=xlWhole)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                c.Value = ""
                Set c = .FindNext(c)
                If c Is Nothing Then Exit Do
            Loop While c.Address <> firstAddress
        End If
    End With
End Sub

Problem

I'm trying to get rid of the blank cells between my cells which have info in them by using F5 to find the blank cells, then Ctrl + - to delete them, and shift the cells up. But when I try to do that, it tells me that there are 'No cells found'. I've noticed that if I select my 'blank' cells, Excel still counts them: which is weird. But if I press Delete on those selected cells, the count goes away, and then I can go F5, blanks, Ctrl + - and Shift cells up, and it works... So my question is how can I still do that, but with these blank cells which Excel thinks aren't blank? I've tried to go through and just press delete over the blank cells, but I have a lot of data and realized that it would take me WAY too long. I need to find a way to select these 'blank' cells within a selection of data.

Original source