MTBthePRO
MTBthePRO

Reputation: 520

VBA: Delete one row if values match

I am trying to create a macro that looks for "Total net" and "Program Operation net" in column C. Once located, the macro compares the rows of these two cells and of their values match then row of "Total Net" get deleted.

This is my code so far...

Sub DeletingEmptyPages()
Dim WS As Worksheet
For Each WS In Sheets

Dim Mystring As String
Dim MystringII As String

MystringII = "Total Net"
Mystring = "Program Operating Net"


  Dim n As Long
  Dim nlast As Long
  Dim rw As Range
  Set rw = ActiveWorkbook.ActiveSheet.UsedRange.Rows
  nlast = rw.count
  For n = nlast To 9 Step -1
      If (Column(c).Value = MystringII And Column(c).Value = Mystring) Then
      rw.Rows(n).Delete

      End If
      Next n
      Next WS
      End Sub

Upvotes: 0

Views: 1496

Answers (2)

A.S.H
A.S.H

Reputation: 29332

I suppose that when both strings exist in column "C", you want to compare if columns "A" and "B" are equal in both rows. You can use the following code and easily adapt it if more columns need to be compared on the matched rows:

Sub Delete_DuplicateTotalNet()

    Dim WS As Worksheet, row1 As Long, row2 As Long
    For Each WS In Sheets
        With WS
            On Error Resume Next
            row1 = WorksheetFunction.Match("Total Net", .Columns("C"), 0)
            row2 = WorksheetFunction.Match("Program Operating Net", .Columns("C"), 0)
            If Err.Number <> 0 Then GoTo NextWS
            If .Range("A" & row1).Value = .Range("A" & row2).Value And _
               .Range("B" & row1).Value = .Range("B" & row2).Value Then
               .Rows(row1).Delete
            End If
        End With
NextWS:
     Err.Clear
     Next WS
End Sub

Upvotes: 2

MTBthePRO
MTBthePRO

Reputation: 520

This is what it looks like but causes a runtime error

    Sub Delete_DuplicateTotalNet()

        Dim WS As Worksheet, row1 As Long, row2 As Long, rng As Long
For Each WS In Sheets
On Error GoTo NextWS

With WS
     If WS.Visible = xlSheetVisible Then

    row1 = WorksheetFunction.Match("Total Net", .Columns(3), 0)
    row2 = WorksheetFunction.Match("Program Operating Net", .Columns(3), 0)
    If .Range("D" & row1).Value = .Range("D" & row2).Value And _
    .Range("E" & row1).Value = .Range("E" & row2).Value And _
    .Range("F" & row1).Value = .Range("F" & row2).Value And _
    .Range("G" & row1).Value = .Range("G" & row2).Value And _
    .Range("H" & row1).Value = .Range("H" & row2).Value And _
    .Range("I" & row1).Value = .Range("I" & row2).Value And _
    .Range("J" & row1).Value = .Range("J" & row2).Value And _
    .Range("K" & row1).Value = .Range("K" & row2).Value Then
    .Rows(row1).Delete
    End If
   End If
   End With
   NextWS:
  Err.Clear
  Next WS

   End Sub

Upvotes: 0

Related Questions