CODEWITHSUNDEEP
excelvba
Jake8281
Jake8281

Reputation: 71

Compare 2 cells in 2 different ranges if they match delete whole row

I am trying to delete rows where the same value occurs in both columns C and D on the same row

I am comparing between column C(BOS address 1) and D (Empower address 1)so if they have the same string remove the whole row. The code is below it executes normally but give Object 424 error after it runs.

Sub test()

Dim try As String
Dim lastrow As Long
Dim x As Variant
Dim row_count As Long
Dim lastrow_str As String
Dim lastrow_rng As String


With empower_report
    ' Get count of records to search through (list that will be deleted)'
    lastrow_str = getColStr("Empower Address 1")
    lastrow = Cells(Rows.Count, lastrow_str).End(xlUp).Row
    lastrow_rng = getColRange("BOS Address 1")

    ' Loop through the "master" list'
    For Each x In Range(lastrow_rng)
       ' Loop through all records in the second list.
       For row_count = lastrow To 1 Step -1
          ' Do comparison of next record'
          If x.Value = Cells(row_count, 4).Value Then
             ' If match is true then delete row.
             Cells(row_count, 4).EntireRow.Delete
           End If
       Next row_count
    Next
End With

End Sub

The error message ( 424 object required) because of this line. once I press end the code will run.

If x.Value = Cells(row_count, 4).Value Then


Example : input:   Column    C                     D 
                            denver             denver
                            denver              boston
Output:   Column    C                     D 
                            denver              boston

I don't have a lot of VBA experience yet. Thank you

Upvotes: 0

Views: 391

Answers (2)

VBasic2008
VBasic2008

Reputation: 54807

Remove Duplicates (Loop Column Range)

  • This is kind of a range study i.e. it surely can be done to be more elegant and more efficient e.g. if you could determine the whole range first, you could use RemoveDuplicates when possible data to the left and/or to the right would not be affected.
Option Explicit

Sub RemoveDupesLoop()

    Const Title1 As String = "Empower Address 1"
    Const Title2 As String = "BOS Address 1"
    Const tRow As Long = 1 ' Title (Header) Row
    
    Dim rg1 As Range
    Dim Col2 As Long
    
    With empower_report
        
        Dim cIndex As Variant
        
        cIndex = Application.Match(Title1, .Rows(tRow), 0)
        If IsError(cIndex) Then Exit Sub
        Dim Col1 As Long: Col1 = cIndex
        
        cIndex = Application.Match(Title2, .Rows(tRow), 0)
        If IsError(cIndex) Then Exit Sub
        Col2 = cIndex
        
        Dim lRow1 As Long: lRow1 = .Cells(.Rows.Count, Col1).End(xlUp).Row
        If lRow1 <= tRow Then Exit Sub
        
        Dim lRow2 As Long: lRow2 = .Cells(.Rows.Count, Col2).End(xlUp).Row
        If lRow2 <= tRow Then Exit Sub
        
        Dim lRow As Long
        If lRow1 < lRow2 Then
            lRow = lRow1
        Else
            lRow = lRow2
        End If
        
        Set rg1 = .Range(.Cells(tRow + 1, Col1), .Cells(lRow, Col1))
        
    End With
    
    Dim drg As Range
    Dim cCell As Range
    
    For Each cCell In rg1.Cells
        If cCell.Value = cCell.EntireRow.Cells(Col2).Value Then
            If drg Is Nothing Then
                Set drg = cCell
            Else
                Set drg = Union(drg, cCell)
            End If
        End If
    Next cCell
    
    If Not drg Is Nothing Then
        drg.EntireRow.Delete
    End If

End Sub

Upvotes: 1

chris neilsen
chris neilsen

Reputation: 53126

since you are trying to delete rows where the same value occurs in both columns C and D on the same row, you only need one loop.

Sub Demo()
    Dim FirstDataColumn  As Range
    Dim SecondDataColumn  As Range
    Dim i As Long
    
    With empower_report
        ' get reference to column data by any means you choose
        Set FirstDataColumn = .Range( ... )
        Set SecondDataColumn = .Range( ... )
        
        If FirstDataColumn.Row <> SecondDataColumn.Row Then
            ' ranges are not aligned
            Exit Sub
        End If
        
        If FirstDataColumn.Rows.Count <> SecondDataColumn.Rows.Count Then
            ' ranges are not the same size
            Exit Sub
        End If
        
        ' Loop the array
        For i = FirstDataColumn.Rows.Count To 1 Step -1
            ' Detect if items on same row are equal
            If FirstDataColumn.Cells(i, 1) = SecondDataColumn.Cells(i, 1) Then
                ' Delete row
                FirstDataColumn.Rows(i).EntireRow.Delete
            End If
        Next
    End With

End Sub

Note that this will be slower than it can be because:

  1. Loop ranges is slow
  2. Deleting rows one at a time is slow

If your data sets are small enough this may not be noticable.
On the other hand, if it's too slow for you, then you could

  1. Move the data to a variant array and loop that
  2. Collect a reference to rows to be deleted as you loop, then delete all rows in one go at the end

Upvotes: 2

Related Questions