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

User · Answer

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  gt  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

User · Answer

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

User · Answer

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

User · Answer

Here s how I fixed this problem without any coding    Select the entire column that I wanted to delete the  blank  cells from  Click the Conditional Formatting tab up top  Select  New Rule   Click  Format only cells that contain   Change  between  to  equal to   Click the box next to the  equal to  box  Click one of the problem  blank  cells  Click the Format Button  Pick a random color to fill the box with  Press  OK   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   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

User · Answer

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

User · Answer

This worked for me    CTR-H to bring up the find and replace   leave  Find What  blank change  Replace with  to a unique text  something that you are positive won t be found in another cell  I used  xx     click  Replace All    copy the unique text in step 3 to  Find what    delete the unique text in  Replace with    click  Replace All

User · Answer

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

User · Answer

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  amp  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

User · Answer

a simple way to select and clear these blank cells to make them blank    Press ctrl   a or pre-select your range Press ctrl   f Leave find what empty and select match entire cell contents  Hit find all Press ctrl   a to select all the empty cells found Close the find dialog Press backspace or delete

User · Answer

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    Selecting the columns rows I wanted to clean  then doing  Find   no text  and  Replace   word of choice   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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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       amp  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  lt  gt  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  lt  gt  firstAddress         End If     End With End Sub

User · Answer

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

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

Examples related to excel

Examples related to vba