[excel] Excel telling me my blank cells aren't blank

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! :)

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

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)

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.

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.

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

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?

'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

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'

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.

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

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

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 :)

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.

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

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.