我有一段 VBA 代碼對作業表進行排序并洗掉其中一個列不包含特定值的所有行
Sub DeleteRows()
' Defines variables
Dim Cell As Range, cRange As Range, LastRow As Long, x As Long, TestRange As Range, MyRange As Range
' Defines LastRow as the last row of data based on column C
LastRow = Sheets("Sheet1").Cells(Rows.Count, "C").End(xlUp).Row
' Sets check range as E1 to the last row of C
Set cRange = Range("C1:C" & LastRow)
' For each cell in the check range, working from the bottom upwards
For x = cRange.Cells.Count To 1 Step -1
With cRange.Cells(x)
' If the cell does not contain one of the listed values then...
If .Value <> "Location1" And .Value <> "Location2" And .Value <> "Location3" Then
' Delete that row
.EntireRow.Delete
End If
End With
' Check next cell, working upwards
Next x
End Sub
問題是我有一個非常長且不斷增長的位置串列。我希望代碼將檢查范圍內的每個單元格與命名串列（“ReferenceLocations”）進行比較，并在單元格包含位置時洗掉該行，而不是指定位置（例如，“Location1”、“Location2”等）名稱不在該串列中。
如何更改該部分代碼（如果 .value<>...）以實作此目的？
uj5u.com熱心網友回復：
使用Application.Match和IsError：
If IsError(Application.Match(.Value, Range("ReferenceLocations"), 0)) Then
.EntireRow.Delete
End If
這假設您的命名范圍是單行或單列。如果這不是一個安全的假設，那么：
If Application.CountIfs(Range("ReferenceLocations"), .Value) = 0
.EntireRow.Delete
End If
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/518030.html
標籤：擅长vba
上一篇：根據多列中的字串條件洗掉行