Reputation: 520
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
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
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