vba Excel 告诉我我的空白单元格不是空白的

Excel telling me my blank cells aren't blank

提问by Vanya Burduk

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

因此，在 excel 中，我试图通过使用 F5 查找空白单元格，然后使用 Ctrl + - 删除它们，然后将单元格上移来摆脱包含信息的单元格之间的空白单元格。但是当我尝试这样做时，它告诉我“没有找到细胞”。

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

我注意到如果我选择我的“空白”单元格，Excel 仍然会计算它们：这很奇怪。但是，如果我在这些选定的单元格上按 Delete 键，计数就会消失，然后我可以按 F5、空格、Ctrl + - 和 Shift 单元格向上，它就可以工作了...

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.

所以我的问题是我怎样才能做到这一点，但是对于这些 Excel 认为不是空白的空白单元格？我试图通过并在空白单元格上按删除键，但我有很多数据并意识到这会花费我太长时间。我需要找到一种方法来在一组数据中选择这些“空白”单元格。

Thanks in advance for your help! :)

在此先感谢您的帮助！:)

采纳答案by glh

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

一个启示：一些空白单元格实际上并不是空白！正如我将展示的，单元格可以有空格、换行符和真正的空：

To find these cellsquickly 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 numberused in =CHAR(32).
2. If you select the cell and click in the formula bar and use the cursor to select all.

1. 该=CODE(A1)公式将返回#VALUE！如果单元格真的为空，否则将返回一个数字。这个数字是.csv 中使用的ASCII 数字=CHAR(32)。
2. 如果您选择单元格并在编辑栏中单击并使用光标全选。

Removing these:

删除这些：

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

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

1. Press ctrl + hto open find and replace.
2. Enter one space in the find what, leave replace withempty and ensure you have match entire cell contentsis ticked in the options.
3. Press replace all.

1. 按ctrl + h打开查找和替换。
2. 在find what 中输入一个空格，将替换保留为空，并确保您在选项中勾选了匹配的整个单元格内容。
3. 按全部替换。

If you have newlinesthis is more difficult and requires VBA:

如果您有换行符，这会更困难并且需要 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.

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

2. 然后输入以下代码。请记住，Chr(10)是换行符仅根据需要替换它，例如" " & Char(10)是空格和换行符：

   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. 现在按 运行您的代码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

回答by glh

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

一种选择和清除这些空白单元格以使其空白的简单方法：

1. Press ctrl + aor pre-select your range
2. Press ctrl + f
3. Leave find whatempty and select match entire cell contents.
4. Hit find all
5. Press ctrl + ato select all the empty cells found
6. Close the find dialog
7. Press backspaceor delete

1. 按下ctrl + a或预先选择您的范围
2. 按 ctrl + f
3. 将find what留空并选择match entire cell contents。
4. 点击查找全部
5. 按ctrl + a选择找到的所有空单元格
6. 关闭查找对话框
7. 按backspace或delete

回答by mike

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'

1. CTR-H 调出查找和替换
2. 将“查找内容”留空
3. 将“替换为”更改为唯一文本，
   在另一个单元格中找不到您肯定的内容（我使用了“xx”）
4. 点击“全部替换”
5. 将第 3 步中的唯一文本复制到“查找内容”
6. 删除“替换为”中的唯一文本
7. 点击“全部替换”

回答by Robin

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

1. 选择我想清理的列/行，然后做
2. “查找”[无文字] 和“替换”[选择词]。
3. 然后我做了“查找”[选择的词]和“替换”[无文字]。

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

它摆脱了这些单元格中的所有隐藏/幻影字符。也许这对你有用？

回答by user4649182

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

所有，这很简单。我一直在尝试同样的方法，这就是在 VBA 中对我有用的方法

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

Regards, Anand Lanka

问候， 阿南德兰卡

回答by Andras

'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

回答by Jenny

Found another way. Set AutoFilter for allcolumns (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 :)

找到了另一种方式。通过选择标题行>“数据”选项卡>“排序和过滤器”-“过滤器”，为所有列设置自动过滤器（重要的否则您将错位数据）。使用第一个数据列中的下拉菜单，取消选中“全选”并仅选择“（空白）”选项 > [确定]。突出显示行（现在全部放在一起）> 右键单击​​>“删除行”。返回下拉菜单>“全选”。急:)

回答by Ryan

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.

不确定这是否已经说过，但我有一个类似的问题，即单元格中没有显示任何内容，但在运行 IsBlank() 公式时不是空白。

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

我选择了整个列，选择了查找和替换，找到了没有任何内容的单元格并用 0 替换，然后再次运行查找和替换，用 0 查找单元格并用“”替换。

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

这解决了我的问题，并允许我搜索空白单元格（F5、特殊、空白）并删除空白行....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

有时单元格中的空格显示为空白，但如果您在单元格上按 F2，您会看到空格。如果您知道单元格中的确切空格数，您也可以通过这种方式进行搜索

回答by Curt

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.

如果您没有想要保留的格式或公式，您可以尝试将文件另存为制表符分隔的文本文件，关闭它，然后用 Excel 重新打开它。这对我有用。

回答by Gareth Brown

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.

我在使用 COUNTA 公式计算非空白单元格时遇到了类似的问题，它正在计算所有单元格（即使是空白单元格也是非空白单元格），我尝试了 =CODE() 但它们没有空格或新行。

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.

我发现当我点击单元格然后点击它时，公式会计算单元格。我有数千个单元格，因此无法手动执行此操作。我写了这个 VBA 语句来检查所有单元格，如果它们是空白的，那么将它们设为空白。忽略这个宏的无意义，相信我，它实际上是通过强制 Excel 将空单元格识别为实际上是空的。

'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