用数组替换'If Activecell.value = X'的长列表？

I have written a macro to search through a column of text and remove values that match the text in the macro below (when the text matches the entire row is deleted). The list of values to remove is around 140 unique numbers, and I have listed 5 below as an example.

我编写了一个宏来搜索一列文本,并删除与下面的宏中的文本匹配的值(当文本匹配整个行被删除时)。要删除的值列表大约有140个唯一编号,我在下面列出了5个作为示例。

What would be a simplified way of writing this portion of code rather than writing 'Activecell.Value = XXXX Or _' 140 times?

编写这部分代码的简化方法是什么,而不是写'Activecell.Value = XXXX Or _'140次?

I was researching ways to use an array to store all the values I want deleted however I was not able to implement it properly.

我正在研究如何使用数组来存储我想要删除的所有值,但是我无法正确实现它。

Thanks for the help!

谢谢您的帮助!

Sub DeleteClosedStores()

Range("F7").Select

While ActiveCell.Value <> ""
    If ActiveCell.Value = 25401 Or _
        ActiveCell.Value = 8587 Or _
        ActiveCell.Value = 8275 Or _
        ActiveCell.Value = 8518 Or _
        ActiveCell.Value = 8522 Or Then

        Selection.EntireRow.Delete
    Else
        ActiveCell.Offset(1, 0).Select
   End If
Wend

5 个解决方案

#1

Write your macro like this:

像这样写你的宏:

Sub DeleteClosedStores()
Range("F7").Select
Dim KillArray
KillArray = Array(25401, 8587, 8275,8518,8522)
Dim f As Variant
While ActiveCell.value <> ""
    f = Filter(KillArray, ActiveCell.value)
    If UBound(f) <> -1 Then
        Selection.EntireRow.Delete
    Else
        ActiveCell.Offset(1, 0).Select
   End If
Wend
End Sub

Of course @sarvesh is right. You need to enter your values somewhere.

当然@sarvesh是对的。你需要在某处输入你的价值观。

#2

store all these values in a VB integer array and compare all values using For...Next loop. But still you have to type all these 140 values at least once to store in an array.

将所有这些值存储在VB整数数组中,并使用For ... Next循环比较所有值。但是,您仍然必须至少键入所有这140个值才能存储在数组中。

#3

I would at least use the Cells property of the sheet. This method iterates over the cells, instead of moving the ActiveCell. It would be considerably faster than moving the active cell.

我至少会使用工作表的Cells属性。此方法迭代单元格,而不是移动ActiveCell。它比移动活动单元快得多。

Private Sub deleteRows()
    Dim i As Integer
    i = 7
    While Cells(i, "F").value <> ""
        If checkCondition(Cells(i, "F").value) Then
            Cells(i, "F").EntireRow.Delete
        End If
        i = i + 1
    Wend
End Sub

Private Function checkCondition(value As String) As Boolean
    ' assuming everything is a number
    num = CLng(value)
    checkconditionmet = IIf(num = 25401 Or _
                            num = 8587 Or _
                            num = 8275 Or _
                            num = 8518 Or _
                            num = 8522, True, False)
End Function

This is O(n), but if you wanted to pre-load the cells into a variable, then iterate over that variable, that would be O(n) as well (O(n) + O(n)). But you don't want to iterate twice.

这是O(n),但是如果你想将单元预加载到变量中,那么迭代该变量,也就是O(n)(O(n)+ O(n))。但是你不想迭代两次。

#4

Try this code (btw, avoid usign Select and ActiveSth statements. It makes your code slow):

试试这段代码(顺便说一句,避免使用Select和ActiveSth语句。它会让你的代码变慢):

Sub DeleteClosedStores()
    Dim str1 As String
    Dim rng As Range

    str1 = "|25401|8587|8275|8518|8522|"

    Set rng = Range("F7")
    While rng.Value <> ""
        Set rng = rng.Offset(1)
        If InStr(1, str1, "|" & rng.Offset(-1) & "|") > 0 Then
            rng.Offset(-1).EntireRow.Delete
        End If
    Wend
End Sub

The main idea is to split your values using e.g. "|" like this: str1 = "|25401|8587|8275|8518|8522|", then sandwich your rng.Value with "|" like this "|" & rng.Offset(-1) & "|" (e.g if value of rng.Offset(-1) is 8587, than code will tries to find |8587| in |25401|8587|8275|8518|8522|).

主要想法是使用例如分割您的值。 “|”像这样:str1 =“| 25401 | 8587 | 8275 | 8518 | 8522 |”,然后用“|”将你的rng.Value夹在中间喜欢这个“|” &rng.Offset(-1)&“|” (例如,如果rng.Offset(-1)的值是8587,则代码将尝试查找| 8587 | in | 25401 | 8587 | 8275 | 8518 | 8522 |)。

#5

Non-looping answer will be quicker

非循环答案会更快

This one uses a working column with a test to delete the matching rows with an AutoFilter (excepting headers in row 1)

这个使用带有测试的工作列来使用AutoFilter删除匹配的行(除了第1行中的标题)

Sub KillEm()
    Dim rng1 As Range, rng2 As Range, rng3 As Range
    Set rng1 = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious)
    Set rng2 = Cells.Find("*", , xlValues, , xlByRows, xlPrevious)
    Set rng3 = Range(Cells(rng2.Row, rng1.Column), Cells(1, rng1.Column))
    Application.ScreenUpdating = False
    With rng3.Offset(0, 1)
        .FormulaR1C1 = "=SUM(COUNTIF(RC1,{25401,8587,8275,8518,8522}))=1"
        .AutoFilter Field:=1, Criteria1:="TRUE"
        .Offset(1, 0).Resize(rng3.Rows.Count - 1, 1).EntireRow.Delete            
        .EntireColumn.Delete           
    End With
    Application.ScreenUpdating = True
End Sub

#1