excel - 如何找到 "0"值而不是空单元格

Question

我有一个包含 8 列数据 A-H 的电子表格，每列的长度不同，并且可能包含我需要删除的“0”值。我没有使用循环执行此操作，而是尝试使用 .Find 方法执行此操作，但由于 find 方法将空单元格(其中一列短于最大长度列)分类为零，因此我变得不稳定。当函数找到其中一个单元格时，它会选择并删除该列顶部的一个单元格，而不是选择特定的单元格。我已经尝试使用我的代码使用 0 以外的值，它工作正常，因此 excel 将空单元格分类为“0”是一个问题。

有没有办法专门搜索值“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

在哪里

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

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

Answer 1

尝试这个

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

这将清除所有具有“0”的单元格
如果要删除具有“0”的单元格，也可以使用 .Find和 .FindNext .看到这个链接
话题 : .Find 和 .FindNext 在 Excel VBA 中
友情链接 : http://www.siddharthrout.com/2011/07/14/find-and-findnext-in-excel-vba/
引用该链接

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.