CODEWITHSUNDEEP
excelvba
MM_1980
MM_1980

Reputation: 25

Delete rows based on list of row numbers

I have a list of row numbers that I need to keep. All other rows need deleted.

This macro deletes entire rows based on row numbers in a list. It works exactly as intended.

How can it be altered to delete all rows EXCEPT those rows on the list?


Dim deleteRows As Range
Dim data() As Variant
Dim i As Double

Dim SourceWks As Worksheet
Dim oldWks As Worksheet

Set SourceWks = Sheets("TBDws")
Set oldWks = Sheets("TBDsamples")

    With SourceWks
        data = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
    End With

    Set deleteRows = oldWks.Rows(data(1, 1))

    For i = 2 To UBound(data, 1)

        Set deleteRows = Union(deleteRows, oldWks.Rows(data(i, 1)))

    Next i

    deleteRows.Delete Shift:=xlUp

End Sub

Upvotes: 1

Views: 109

Answers (1)

norie
norie

Reputation: 9867

This will delete all the rows on the sheet TBDsamples that aren't listed in column A on TBDws

Sub DeleteThings()
Dim SourceWks As Worksheet
Dim oldWks As Worksheet
Dim deleteRange As Range
Dim arrRows() As Variant
Dim Res As Variant
Dim I As Long

    Set SourceWks = Sheets("TBDws")
    Set oldWks = Sheets("TBDsamples")

    With SourceWks
        arrRows = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
    End With

    For I = 1 To oldWks.Range("A" & Rows.Count).End(xlUp).Row

        Res = Application.Match(I, arrRows, 0)
        If IsError(Res) Then
            If deleteRange Is Nothing Then
                Set deleteRange = oldWks.Rows(I)
            Else
                Set deleteRange = Union(deleteRange, oldWks.Rows(I))
            End If
        End If

    Next I

    deleteRange.Delete Shift:=xlUp

End Sub

Upvotes: 1

Related Questions