Excel告诉我我的空白单元格不是空白的 [英] Excel telling me my blank cells aren't blank

问题描述

所以在excel中，我试图摆脱我们的单元格之间的空白单元格，通过使用F5找到空白单元格，然后按Ctrl + - 删除它们，并将单元格向上移动。但是，当我尝试这样做时，它告诉我有没有单元格。

我注意到，如果我选择我的空白单元格，Excel仍然计数它们：这很奇怪。但是如果我按删除这些选定的单元格，计数将消失，然后我可以去F5，空格，Ctrl + - 和Shift单元格，它的工作原理...

所以我的问题是怎么还能做到这一点，但这些空白单元格Excel认为不是空白的？我试图通过，只是按空白单元格删除，但我有很多数据，并意识到这将需要我WAY太长时间。 我需要找到一种在选择的数据中选择这些空白单元格的方法。

提前感谢您的帮助！ ：

解决方案

启示：某些空白单元格实际上不是空白的！因为我会显示单元格可以有空格，换行符和真空：

要快速找到这些单元格，您可以做一些事情。

1. = CODE（A1）公式将返回 #VALUE！如果单元格真的是空的，否则数字将返回。该号码是 = CHAR（32）中使用的 ASCII号码 ）。


2. 如果您选择单元格并单击公式栏，并使用光标选择全部。

删除这些：

如果您在单元格中只有一个空格这些可以使用以下方式轻松删除：

1. 按 ctrl + h 打开查找和替换。
   
2. 在中输入一个空格，查找，将替换为为空，并确保匹配整个单元格被勾选


3. 按替换全部。

如果您有换行符，这更加困难，需要VBA：

1. 右键单击工作表选项卡>查看代码。



2. 然后输入以下代码。记住 Chr（10）是一个换行符，只需要替换为例如& Char（10）是一个空格和换行符：

     Sub find_newlines（）
   与Me.Cells 
   设置c = .Find（Chr（10），LookIn：= xlValues，LookAt：= xlWhole）
   如果不是c不是然后
    firstAddress = c.Address 
    Do 
    c.Value =
    Set c = .FindNext（c）
   如果c是Nothing然后退出Do 
   循环，而c.Address<> firstAddress 
    End If 
    End With 
    End Sub 
     




3. 现在运行代码按 F5 。

---

提供文件后：选择要提高性能的兴趣范围，然后执行以下操作：

  Sub find_newlines（）
带选择
设置c = .Find（，LookIn：= xlValues，LookAt：= xlWhole）
如果不是c是没有，然后
 firstAddress = c.Address 
 Do 
 c.Value =
 Set c = .FindNext（c）
如果c不是，然后退出执行
循环，而c。地址<> firstAddress 
 End If 
 End With 
 End Sub 
  

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

这篇关于Excel告诉我我的空白单元格不是空白的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！