Geek asks and answers

Replace the long list "If Activecell.value = X" with an array?

I wrote a macro to search a column of text and delete values ​​that match the text in the macro below (when the text matches the entire row is deleted). The list of deleted values ​​is about 140 unique numbers, and I gave 5 below as an example.

What would be a simplified way of writing this part of the code, rather than writing "Activecell.Value = XXXX or _" 140 times?

I learned how to use an array to store all the values ​​that I want to delete, but I could not properly implement it.

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
+3
source share
5 answers

Write your macro as follows:

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 specify the values.

+1

VB For...Next. 140 .

0

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

O (n), , , O (n) (O (n) + O (n)), .

0

(, usign 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 basic idea is to separate your values ​​using, for example, "|" for example: str1 = "|25401|8587|8275|8518|8522|"then your sandwich rng.Valuewith "|" for example "|" & rng.Offset(-1) & "|"(for example, if the value rng.Offset(-1)is equal 8587, then the code will search |8587|in |25401|8587|8275|8518|8522|).

0
source

Non-zero loop response will be faster

This one uses a working column with a test to remove matching rows from AutoFilter(except for the headers in row 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
0
source

More articles:


All Articles