CODEWITHSUNDEEP
excelvba
DHI
DHI

Reputation: 89

how to delete a row that matches criteria in other sheet?

I have two spreadsheets one contains the data that I need to clean up (named "fedex") by deleting some rows that matches a list of numbers in another spreadsheet (named "sheet1"). Spreadsheet "fedex" column "H" has the numbers that match with spreadsheet "sheet1" column "A".

I'm getting a type mismatch error message when it gets to set lastrow = fedex.cells(Rows.count,8)end(xlUp).Row

I'm not sure if the rest of the code will work since I'm stuck there.

This is my code:

Sub deletepaidshipments()

Dim fedex As Worksheet
Set fedex = ThisWorkbook.Sheets("fedex_shipment_detail_payer_det")
Dim sheet1 As Worksheet
Set sheet1 = ThisWorkbook.Sheets("sheet1")
Set lastrow = fedex.Cells(Rows.Count, 8).End(xlUp).Row

For x = 2 To lastrow
    t = 0
    On Error Resume Next
    t = Application.WorksheetFunction.Match(fedex.Range("h" & x), sheet1.Range("a :a"), 0)
    On Error GoTo 0
    If t > 0 Then
        fedex.Rows(x, lastrow).Delete        
    End If
Next x

End Sub

Upvotes: 1

Views: 564

Answers (1)

Shai Rado
Shai Rado

Reputation: 33672

Try the shorter code version below (explanations inside the code comments):

Option Explicit

Sub deletepaidshipments()

Dim FedexSht As Worksheet
Dim Sht1 As Worksheet
Dim LastRow As Long
Dim x As Long

Set FedexSht = ThisWorkbook.Sheets("fedex_shipment_detail_payer_det")
Set Sht1 = ThisWorkbook.Sheets("sheet1")

With FedexSht
    LastRow = .Cells(.Rows.Count, "H").End(xlUp).Row

    For x = LastRow To 2 Step -1 ' allways loop backwards when deleting rows
        If Not IsError(Application.Match(.Range("H" & x).Value, Sht1.Range("A:A"), 0)) Then ' check if there is a match >> delete row
            .Rows(x).Delete
        End If
    Next x
End With

End Sub

Upvotes: 1

Related Questions