如何删除所有不包含特定值的单元格（在 VBA/Excel 中）

How to delete all cells that do not contain specific values (in VBA/Excel)

提问by user3084100

I fully didn't understand how to follow the answer in vba deleting rows that do not contain set values defined in range(I need to use VBA for this). From what I gathered, i need to specify an array, then use some if then stuff.

我完全不明白如何按照vba 中的答案删除不包含范围中定义的设置值的行（为此我需要使用 VBA）。根据我收集的内容，我需要指定一个数组，然后使用一些 if then 的东西。

In my case, I want to create something that will search just a specified column and delete all values that do not contain specific letters/numbers. 1,2,3,4,5,s,f,p,a,b,c,o are the numbers/letters i want to keep. Cells which do not contain these values (even 11 or 1s should be deleted), I want only to delete the cell (not the whole row) and shift the cells below it up (i believe you can do this with the default .delete command).

就我而言，我想创建一些仅搜索指定列并删除所有不包含特定字母/数字的值的内容。1,2,3,4,5,s,f,p,a,b,c,o 是我想保留的数字/字母。不包含这些值的单元格（甚至应该删除 11 或 1s），我只想删除单元格（而不是整行）并将其下方的单元格向上移动（我相信您可以使用默认的 .delete 命令执行此操作）。

For example my columns look like this:

例如，我的列如下所示：

p
a
1
2
5
s
f
s
8

p
a
1
2
5
s
f
s
8

31
4
f

31
4
英尺

I want to screen my data so that all blank cells and all cells which do not contain the numbers or letter mentioned above (e.g. 31 and 8 in this case) are automatically deleted.

我想筛选我的数据，以便自动删除所有空白单元格和所有不包含上述数字或字母的单元格（例如，在本例中为 31 和 8）。

Thanks for your help!

谢谢你的帮助！

回答by Tim Williams

Sub Tester()

Dim sKeep As String, x As Long
Dim rngSearch As Range, c As Range

    'C1:C5 has values to keep
    sKeep = Chr(0) & Join(Application.Transpose(Range("C1:C5").Value), _
                                Chr(0)) & Chr(0)

    Set rngSearch = Range("A1:A100")

    For x = rngSearch.Cells.Count To 1 Step -1
        Set c = rngSearch.Cells(x)
        If InStr(sKeep, Chr(0) & c.Value & Chr(0)) = 0 Then
            c.Delete shift:=xlShiftUp
        End If
    Next x

End Sub

回答by Tim Williams

This will do

这会做

Sub Main()

Dim dontDelete
dontDelete = Array("1", "2", "3", "4", "5", "s", "f", "p", "a", "b", "c", "o")

Dim i As Long, j As Long

Dim isThere As Boolean

For i = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
    For j = LBound(dontDelete) To UBound(dontDelete)
        If StrComp(Range("A" & i), dontDelete(j), vbTextCompare) = 0 Then
            isThere = True
        End If
    Next j
    If Not isThere Then
        Range("A" & i).Delete shift:=xlUp
    End If
    isThere = False
Next i

End Sub

回答by Siddharth Rout

Another way :) Which doesn't delete the cells in a loop.

另一种方式:) 这不会删除循环中的单元格。

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim rngDEL As Range
    Dim strDel As String
    Dim arrDel
    Dim i As Long

    strDel = "1,11,Blah" '<~~ etc... You can pick this from a range as well
    arrDel = Split(strDel, ",")

    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws.Columns(1) '<~~ Change this to the relevant column
        For i = LBound(arrDel) To UBound(arrDel)
            .Replace What:=arrDel(i), Replacement:="", LookAt:=xlWhole, SearchOrder:= _
            xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        Next i

        On Error Resume Next
        Set rngDEL = .Cells.SpecialCells(xlCellTypeBlanks)
        On Error GoTo 0

        If Not rngDEL Is Nothing Then rngDEL.Delete Shift:=xlShiftUp
    End With
End Sub

回答by Jiminy Cricket

Sub DeleteValues()
Dim x As Integer
Dim i As Integer
Dim Arr(1 To 3) As String

Arr(1) = "1"
Arr(2) = "2"
Arr(3) = "3"

Range("A1").Select

For x = 1 To 10
    For i = 1 To 3
        If ActiveCell.Value = Arr(i) Then
            ActiveCell.Delete
        End If
    Next i
    ActiveCell.Offset(1, 0).Select
Next x

End Sub

This will loop through range("a1:a10") and delete any cell where the value = any of the array values (1,2,3)

这将遍历 range("a1:a10") 并删除值 = 任何数组值 (1,2,3) 的任何单元格

You should hopefully be able to work with this code and suit it to your needs?

您应该希望能够使用此代码并满足您的需求？