If column E equal to column F then delete all that rows

[leonardo1234]

If column E equal to column F then delete all that entire rows in sheet2

[Oliver]

Adapt this to your needs

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.ScreenUpdating = False
Dim i as long
For i = 1 To 90 
If Range("E" & i).Value = Range("F" & i).Value Then Rows(i).EntireRow.Delete
Next i
Application.ScreenUpdating = True
end sub

[HansV]

Modifying Rudi's solution from your recent other thread:

Code: Select all

Sub Delete_Matches()
    Application.ScreenUpdating = False
    Range("A1").EntireColumn.Insert
    Range("A1").CurrentRegion.Columns(1).FormulaR1C1 = "=RC[5]=RC[6]"
    Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:="TRUE"
    With ActiveSheet.AutoFilter.Range
        On Error Resume Next
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        On Error GoTo 0
    End With
    ActiveSheet.AutoFilterMode = False
    Range("A1").EntireColumn.Delete
    Application.ScreenUpdating = True
End Sub

[leonardo1234]

Thnx HansV and Oliver for giving ur precious time and great support to this post
Problem Solved

[mirfield]

Adapt this to your needs

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.ScreenUpdating = False
Dim i as long
For i = 1 To 90 
If Range("E" & i).Value = Range("F" & i).Value Then Rows(i).EntireRow.Delete
Next i
Application.ScreenUpdating = True
end sub

I know the OP has always been answered, but in your code, I'd always recommend processing rows in reverse order if deleting rows.

Imagine you are on row 10 and the check is true. You delete that row and all rows move up; row 11 then becomes row 10. In your code, the counter increments and you move on to the new row 11, ignoring the old row 11 (which is now row 10).

Changing "For i = 1 To 90" to "For i = 90 to 1 Step -1" avoids this issue.

[leonardo1234]

Thnx Mirfield for the info

[leonardo1234]

Code: Select all

Sub STEP3()
    Application.ScreenUpdating = False
    Range("A1").EntireColumn.Insert
    Range("A1").CurrentRegion.Columns(1).FormulaR1C1 = "=RC[5]=RC[6]"
    Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:="TRUE"
    With ActiveSheet.AutoFilter.Range
        On Error Resume Next
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        On Error GoTo 0
    End With
    ActiveSheet.AutoFilterMode = False
    Range("A1").EntireColumn.Delete
    Application.ScreenUpdating = True
End Sub

Kindly mention the sheet name in the code sheet1 so that this code will only work for sheet1

[Oliver]

Try this:

After

Code: Select all

Application.ScreenUpdating = False

Add

Code: Select all

ThisWorkbook.Worksheets("Sheet1").Select

[HansV]

Alternatively:

Code: Select all

Sub STEP3()
    Application.ScreenUpdating = False
    With Worksheets("Sheet1")
        .Range("A1").EntireColumn.Insert
        .Range("A1").CurrentRegion.Columns(1).FormulaR1C1 = "=RC[5]=RC[6]"
        .Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:="TRUE"
        With .AutoFilter.Range
            On Error Resume Next
            .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            On Error GoTo 0
        End With
        .AutoFilterMode = False
        .Range("A1").EntireColumn.Delete
    End With
    Application.ScreenUpdating = True
End Sub

[leonardo1234]

Thnx HansV and Oliver Sir for ur great support