CODEWITHSUNDEEP
excelvbafor-loop
Fah
Fah

Reputation: 203

Delete the row if the cell value on sheet is the same then the cell value on another sheet

I need to delete the row if the value of column G on a sheet named OV is = the value on column G on a sheet named all_teams. I am trying different approach, however when it works only deletes 1 row.

Sub test_delete()

    Dim j As Variant
    Dim ltrow As Long

    ltrow = OV.Range("C" & Cells.Rows.Count).End(xlUp).Row
                    
    For j = 2 To ltrow
    
        If all_teams.Range("G" & j).Value = OV.Range("G" & ltrow).Value Then
                    OV.Rows(j).Delete
        End If
                
    Next j
        
End Sub

What would be the best approach for it?

Upvotes: 0

Views: 64

Answers (3)

Error 1004
Error 1004

Reputation: 8220

Try:

Option Explicit

Sub test_delete()

    Dim j As Variant
    Dim ltrow As Long
    Dim wsOV As Worksheet, wsAll As Worksheet

    With ThisWorkbook
        Set wsOV = .Worksheets("OV")
        Set wsAll = .Worksheets("all_teams")
    End With
    
    ltrow = wsOV.Range("C" & wsOV.Cells.Rows.Count).End(xlUp).Row
                    
    For j = ltrow To 2 Step -1
    
        If wsAll.Range("G" & j).Value = wsOV.Range("G" & ltrow).Value Then
            wsOV.Rows(j).EntireColumn.Delete
        End If
                
    Next j
        
End Sub

Upvotes: 1

FaneDuru
FaneDuru

Reputation: 42236

Try the next code, please. Please, properly set the used worksheets:

Sub test_delete()
    Dim OV As Worksheet, all_teams As Worksheet, lastROV As Long, lastRAllT As Long
    Dim i As Variant, j As Variant, rngDel As Range
    'you have to set here the sheets...

    lastROV = OV.Range("G" & cells.Rows.count).End(xlUp).Row
    lastRAllT = all_teams.Range("G" & cells.Rows.count).End(xlUp).Row
                    
    For j = 2 To lastROV
        For i = 2 To lastRAllT
            If all_teams.Range("G" & i).Value = OV.Range("G" & j).Value Then
                If rngDel Is Nothing Then
                   Set rngDel = OV.Rows(j)
                Else
                    Set rngDel = Union(rngDel, OV.Rows(j))
                End If
                Exit For
            End If
        Next i
    Next j
    If Not rngDel Is Nothing Then rngDel.EntireRow.Delete xlUp
End Sub

Upvotes: 1

PCosmo
PCosmo

Reputation: 33

Learnt this from one of my previous questions.

When deleting rows in a for loop, its best to do it backwards, so the following

For j = ltRow To 2 Step -1

I believe this could help you out!

sub test_delete()

dim j as variant
dim ltrow as long

ltrow = worksheets("OV").Range("C" & Rows.Count).End(xlUp).Row

for j = ltRow to 2 Step -1
   if sheets("all_teams").Range("G" & J).Value = Sheets("OV").Range("G" & ltrow).value then
         Sheets("OV").Rows(J).entirerow.delete
    end if
next
end sub

untested, but I think it may work!

Upvotes: 1

Related Questions