CODEWITHSUNDEEP
excelvbafor-loopsearchdelete-row
Heidi Traustadottir
Heidi Traustadottir

Reputation: 13

VBA search for values and delete from list (for loop is too slow)

I get an Excel document sent each week with a list of employees that haven't completed training for a new IT-system we're getting. However, the training is not relevant for everyone, so I need to clean up in order to get the real numbers.

I have made a list of the employees which training is not relevant for (Oprydning), and use the following code to search for one name at a time and delete from the other list (Datagrundlag). 

Private Sub RydOpKnap_Click()
Dim OprydningAntal As Long
Dim DataAntal As Long
Dim Find As String

Worksheets("Datagrundlag - endelig").Visible = True

OprydningsAntal = Worksheets("Oprydning").UsedRange.Rows.Count
DataAntal = Worksheets("Datagrundlag - endelig").UsedRange.Rows.Count

  Dim r, s As Long
    For r = 2 To OprydningsAntal
        Find = Worksheets("Oprydning").Cells(r, 1).Value
            For s = 2 To DataAntal


                If Worksheets("Datagrundlag - endelig").Cells(s, 3).Value = Find Then
                    Worksheets("Datagrundlag - endelig").Cells(s, 3).EntireRow.Delete
            Exit For
            End If
            Next s
    Next r

    Worksheets("Datagrundlag - endelig").Visible = False

    ActiveWorkbook.RefreshAll

End Sub

This takes forever though, since the list of not-relevant employees is currently 460 different values (and will get bigger). Is there another way to do this? I'm quite new to VBA, but would it be possible to use an array? Does the "Command Button" that initiates the code, make it slower?

Thanks in advance!

Heidi

Upvotes: 1

Views: 258

Answers (2)

user10765660
user10765660

Reputation:

Create an array from your current list of non-relevant staff and use it to AutoFilter with xlFilterValues. Delete visible rows and remove filter.

Option Explicit

Private Sub RydOpKnap_Click()
    Dim i As Long, j As Long

    ReDim notRelevant(0) As Variant

    With Worksheets("Oprydning")
        For i = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
            ReDim Preserve notRelevant(i-2)
            notRelevant(i - 2) = .Cells(i, "A").Value
        Next i
    End With

    With Worksheets("Datagrundlag - endelig")
        .Visible = True

        If .AutoFilterMode Then .AutoFilterMode = False
        With .Range(.Cells(1, "C"), .Cells(.Rows.Count, "C").End(xlUp))
            .AutoFilter field:=1, Criteria1:=(notRelevant), Operator:=xlFilterValues
            .Offset(1, 0).EntireRow.Delete
        End With
        .AutoFilterMode = False

        .Visible = False
        .Parent.RefreshAll
    End With

End Sub

Upvotes: 1

pnuts
pnuts

Reputation: 59475

Is there another way to do this?

Yes, use Conditional Formatting to colour those to be excluded eg, and filter to delete the rows with formatted cells.

SO53707228 example

Upvotes: 1

Related Questions