Excel telling me my blank cells aren't blank

Question

So in excel 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.

Thanks in advance for your help! :)

Answer 1

a simple way to select and clear these blank cells to make them blank:

1. Press ctrl + a or pre-select your range
2. Press ctrl + f
3. Leave find what empty and select match entire cell contents.
4. Hit find all
5. Press ctrl + a to select all the empty cells found
6. Close the find dialog
7. Press backspace or delete

Answer 2

This worked for me:

1. CTR-H to bring up the find and replace
2. leave 'Find What' blank
3. change 'Replace with' to a unique text, something that you are
   positive won't be found in another cell (I used 'xx')
4. click 'Replace All'
5. copy the unique text in step 3 to 'Find what'
6. delete the unique text in 'Replace with'
7. click 'Replace All'

Answer 3

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.

1. 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).
2. 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:

1. Press ctrl + h to open find and replace.
2. Enter one space in the find what, leave replace with empty and ensure you have match entire cell contents is ticked in the options.
3. Press replace all.

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

1. Right click on the sheet tab > view code.

2. 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
   

3. 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

Answer 4

I had a similar problem where scattered blank cells from an export from another application were still showing up in cell counts.

I managed to clear them by

1. Selecting the columns/rows I wanted to clean, then doing
2. "Find" [no text] and "Replace" [word of choice].
3. Then I did "Find" [word of choice] and "Replace" with [no text].

It got rid of all hidden/phantom characters in those cells. Maybe this will work for you?

Answer 5

All, this is pretty simple. I have been trying for the same and this is what worked for me in VBA

Range("A1:R50").Select    'The range you want to remove blanks
With Selection
    Selection.NumberFormat = "General"
    .Value = .Value
End With

Regards, Anand Lanka

Answer 6

Not sure if this has already been said, but I had a similar problem with cells showing nothing in them, but not being blank when you run the IsBlank() formula.

I selected the entire column, selected Find & Replace, found cells with nothing and replaced with a 0, then ran find and replace again, finding cells with 0 and replacing with "".

This solved my problem and allowed me to search for Blank cells (F5, Special, Blanks) and delete rows that were blank....BOOM.

May not work for every application but this solved my problem.

Sometimes there are spaces in cells which appear blank but if you hit F2 on the cell, you'll see spaces. You can also search this way if you know the exact number of spaces in a cell

Answer 7

If you don't have formatting or formulas you want to keep, you can try saving your file as a tab delimited text file, closing it, and reopening it with excel. This worked for me.

Answer 8

'Select non blank cells
Selection.SpecialCells(xlCellTypeConstants, 23).Select
' REplace tehse blank look like cells to something uniqu
Selection.Replace What:="", Replacement:="TOBEDELETED", LookAt:=xlWhole, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
'now replace this uique text to nothing and voila all will disappear
Selection.Replace What:="TOBEDELETED", Replacement:="", LookAt:=xlWhole, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

Answer 9

Found another way. Set AutoFilter for all columns (important or you will misalign data) by selecting the header row > 'Data' tab > Sort and filter - 'Filter'. Use drop-down in first data column, untick 'Select all' and select only '(Blanks)' option > [OK]. Highlight rows (now all together) > right click > 'Delete row'. Head back to the drop-down > 'Select all'. Presto :)

Answer 10

This works with numbers.

If your range is O8:O20, then in a nearby empty range (e.g. T8:T20) enter =O8/1 and fill down. This will give you a result of #VALUE for the 'empty' cells and your original number will remain as it was.

Then with the range T8:20 selected (CTL-* if it's not already) hit F5 and choose Special. From the Special dialogue, choose Errors and click OK. This will deselect your actual numbers leaving only the #VALUE cells selected. Delete them and you will have actual empty cells. Copy T8:T20 and paste back over O8:O20.

Essentially, since blank cells doesn't work, you need to convert the 'empty' cells into something that the Go To Special can latch on to. Any action that would convert into #VALUE would work, and other 'error' types should be supported as well.

Answer 11

My method is similar to Curt's suggestion above about saving it as a tab-delimited file and re-importing. It assumes that your data has only values without formulas. This is probably a good assumption because the problem of "bad" blanks is caused by the confusion between blanks and nulls -- usually in the data imported from some other place -- so there shouldn't be any formulas. My method is to parse in place -- very similar to saving as a text file and re-importing, but you can do this without closing and re-opening the file. It's under Data > Text-to-Columns > delimited > remove all parsing characters (can also choose Text if you want) > Finish. This should cause Excel to re-recognize your data from scratch or from text and recognize blanks as really blank. You can automate this in a subroutine:

Sub F2Enter_new()         
   Dim rInput As Range
   If Selection.Cells.Count > 1 Then Set rInput = Selection
   Set rInput = Application.InputBox(Title:="Select", prompt:="input range", _ 
                                     Default:=rInput.Address, Type:=8)
'   Application.EnableEvents = False: Application.ScreenUpdating = False
   For Each c In rInput.Columns
      c.TextToColumns Destination:=Range(c.Cells(1).Address), DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
      Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
      FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
   Next c
   Application.EnableEvents = True: Application.ScreenUpdating = True
End Sub

You can also turn-on that one commented line to make this subroutine run "in the background". For this subroutine, it improves performance only slightly (for others, it can really help a lot). The name is F2Enter because the original manual method for fixing this "blanks" problem is to make Excel recognize the formula by pushing F2 and Enter.

Answer 12

Here's how I fixed this problem without any coding.

1. Select the entire column that I wanted to delete the "blank" cells from.
2. Click the Conditional Formatting tab up top.
3. Select "New Rule".
4. Click "Format only cells that contain".
5. Change "between" to "equal to".
6. Click the box next to the "equal to" box.
7. Click one of the problem "blank" cells.
8. Click the Format Button.
9. Pick a random color to fill the box with.
10. Press "OK".
11. This should change all of the problem "blank" cells to the color that you chose. Now Right click one of the colored cells, and go to "Sort" and "Put selected cell color on top".
12. This will put all of the problem cells at the top of the column and now all of your other cells will stay in the original order you put them in. You can now select all of the problem cells in one group and click the delete cell button on top to get rid of them.

Answer 13

The most simple solution for me has been to:

1)Select the Range and copy it (ctrl+c)

2)Create a new text file (anywhere, it will be deleted soon), open the text file and then paste in the excel information (ctrl+v)

3)Now that the information in Excel is in the text file, perform a select all in the text file (ctrl+a), and then copy (ctrl+c)

4)Go to the beginning of the original range in step 1, and paste over that old information from the copy in step 3.

DONE! No more false blanks! (you can now delete the temp text file)

Answer 14

Goto->Special->blanks does not like merged cells. Try unmerging cells above the range in which you want to select blanks then try again.

Answer 15

I had a similar problem with getting the COUNTA formula to count non-blank cells, it was counting all of them (even the blank one's as non-blank), I tried =CODE() but they had no spaces or new lines.

I found that when I clicked in the cell and then clicked out of it then the formula would count the cell. I had thousands of cells so could not do this manually. I wrote this VBA statement to literally check all the cells and if they were blank then to make them blank. Ignore the pointlessness of this macro and trust me that it actually worked by forcing Excel to recognize the empty cells as actually being empty.

'This checks all the cells in a table so will need to be changed if you're using a range
Sub CreateBlanks()

Dim clientTable As ListObject
Dim selectedCell As Range

Set clientTable = Worksheets("Client Table").ListObjects("ClientTable")

For Each selectedCell In clientTable.DataBodyRange.Cells
    If selectedCell = "" Then
        selectedCell = ""
    End If
Next selectedCell
End Sub

Answer 16

Save your dataset in CSV file and open that file and copy the dataset and paste to the excel file. and then crtl + g will work on your file, means the excel will recognize that blank is really blank.