vba 如何找到“0”值而不是空单元格

How to find "0" values rather than empty cells

提问by Chris Birmingham

I have a spreadsheet containing 8 columns of data, A-H, each of which is a different length and may contain values of "0" which I need deleting. Rather than do this with a loop, I'm trying to do this using the .Find method but I'm becoming unstuck as the find method keeps classifying empty cells (where a column is shorter than the max length column) as zeros. When the function finds one of these cells it selects and deletes a cell at the top of that column rather than selecting the specific cell. I've tried using my code using values other than 0 and it works fine so it's a problem with excel classifying empty cells as "0".

我有一个包含 8 列数据 AH 的电子表格，每列数据的长度不同，可能包含我需要删除的“0”值。我没有使用循环来执行此操作，而是尝试使用 .Find 方法来执行此操作，但由于 find 方法不断将空单元格（其中一列短于最大长度列的列）归为零，因此我变得心烦意乱。当函数找到这些单元格之一时，它会选择并删除该列顶部的一个单元格，而不是选择特定的单元格。我试过使用我的代码使用 0 以外的值，它工作正常，所以 excel 将空单元格分类为“0”是一个问题。

Is there a way to search specifically for the value "0"? I've tried using str(0) to specify a string value of "0" but get the same result. A subset of my code as it stands is:

有没有办法专门搜索值“0”？我尝试使用 str(0) 指定字符串值“0”但得到相同的结果。我的代码的一个子集是：

Row = wf.Max(Cells(Rows.Count,1).End(xlUp).Row, Cells(Rows.Count,8).End(xlUp_.Row

'Originally I had zero = 0 or "0" but I tried the following to get a string version
zero = Str(0)
zerofix = Right(zero,1)

Do While Check_Val_Existence(zerofix,Row) = True
   Set aCell = ActiveSheet.Range(Cells(1,1), Cells(Row,8)).Find(What:=zerofix,LookIn:=xlValues)
   If Not aCell Is Nothing Then
      aCell.Delete Shift:=xlUp
   End If
Loop

where

在哪里

Function Check_Val_Existence(ByVal Srch, ByVal Row) As Boolean

Dim rFnd As Range

Set rFnd = ActiveSheet.Range(Cells(1,1), Cells(Row,8)).Find(What:=Srch)
If Not rFnd Is Nothing Then
   Check_Val_Existence = True
Else
   Check_Val_Existence = False
End If

End Function

I'd rather not have to loop through the code and search each column in turn but it's beginning to look like I might have to do just that.

我宁愿不必遍历代码并依次搜索每一列，但看起来我可能不得不这样做。

采纳答案by Siddharth Rout

Try this

尝试这个

Sheet1.Cells.Replace What:="0", Replacement:="", LookAt:=xlwhole, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False  

This will clear all cells which have "0"

这将清除所有具有“0”的单元格

If you want to delete the cells which have "0" then you can also use .Findand .FindNext. See this link

如果要删除具有“0”的单元格，则还可以使用.Find和.FindNext。看这个链接

Topic: .Find and .FindNext In Excel VBA

主题：Excel VBA 中的.Find 和.FindNext

Link: http://www.siddharthrout.com/2011/07/14/find-and-findnext-in-excel-vba/

链接：http: //www.siddharthrout.com/2011/07/14/find-and-findnext-in-excel-vba/

Quote from that link

来自该链接的报价

In this tutorial, I will stress on how to use .Find to make your search faster.

The syntax of .Find is

expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

Where

Expression (Required): is any valid range Object. So if we take the above example then the range would be Range(“A1:A” & lastRow)

What (Optional Variant): is the “Search value”

After (Optional Variant): The cell after which you want the search to begin.

LookIn (Optional Variant): The type of information. (xlValues or xlFormulas)

LookAt (Optional Variant): Can be one of the following XlLookAt (constants): xlWhole or xlPart.

SearchOrder (Optional Variant): Can be one of the following XlSearchOrder constants: xlByRows or xlByColumns.

SearchDirection: Can be one of these XlSearchDirection constants. xlNext default xlPrevious

MatchCase (Optional Variant): True to make the search case sensitive. The default value is False.

MatchByte (Optional Variant): Used only if you've selected or installed double-byte language support. True to have double-byte characters match only double-byte characters. False to have double-byte characters match their single-byte equivalents.

SearchFormat (Optional Variant): The search format.

在本教程中，我将重点介绍如何使用 .Find 来加快搜索速度。

.Find 的语法是

expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

在哪里

表达式（必需）：是任何有效范围的对象。因此，如果我们采用上面的示例，那么范围将是 Range(“A1:A” & lastRow)

什么（可选变体）：是“搜索值”

After (Optional Variant)：您希望在其后开始搜索的单元格。

LookIn（可选变体）：信息的类型。（xlValues 或 xlFormulas）

LookAt（可选变体）：可以是以下 XlLookAt（常量）之一：xlWhole 或 xlPart。

SearchOrder（可选变体）：可以是以下 XlSearchOrder 常量之一：xlByRows 或 xlByColumns。

SearchDirection：可以是这些 XlSearchDirection 常量之一。xlNext 默认 xlPrevious

MatchCase (Optional Variant): True 使搜索区分大小写。默认值为 False。

MatchByte（可选变体）：仅在您选择或安装双字节语言支持时使用。真让双字节字符只匹配双字节字符。让双字节字符与其单字节等价物匹配是错误的。

SearchFormat（可选变体）：搜索格式。

回答by brettdj

Rather then work with all cells, do the prelim work to define the range of interest - i.e. cells that have numbers in them either entered as constants or as formulae. This excludes your blank cels from the search which will both speed up your code, and eliminate the false flags.

与其对所有单元格进行处理，不如先做初步工作来定义感兴趣的范围——即其中包含数字的单元格，要么作为常量输入，要么作为公式输入。这会从搜索中排除您的空白 cel，这将加速您的代码，并消除错误标志。

In this example rng3returns the cells of interest on the ActiveSheet. You would then run your Findroutine on this range

在此示例中，rng3返回 上感兴趣的单元格ActiveSheet。然后你会Find在这个范围内运行你的例程

Sub LookAtZeros()
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
On Error Resume Next
Set rng1 = Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
Set rng2 = Cells.SpecialCells(xlCellTypeFormulas, xlNumbers)
On Error GoTo 0
If Not rng1 Is Nothing Then
If Not rng2 Is Nothing Then
Set rng3 = Union(rng1, rng2)
Else
Set rng3 = rng1
End If
Else
If Not rng2 Is Nothing Then Set rng3 = rng2
End If
If Not rng3 Is Nothing Then
MsgBox "Range with numeric constants and/or numeric formulae is " & rng3.Address(0, 0)
Else
MsgBox "no numbers", vbCritical
End If
End Sub

回答by Robert Ilbrink

My suggestion is to do a general replace of 0 to =0/0, which should give an error (and still gives you a way to restore the values back to 0 if needed, due to the unique calculation), then do a find/select special errors.

我的建议是将 0 替换为 = 0/0，这应该会出现错误（并且由于独特的计算，如果需要，您仍然可以将值恢复为 0），然后执行 find/选择特殊错误。