Solved: Trying to delete rows based on 3 variables

[kazza101]

Hi all

I know this has been posted before, but can't get it to work based on my macro that I have created from looking at others. Am a real newby!!

If the entries in Same_1,Same_2 and Same_3 are all yes, I need the row to be deleted. I have gotten all confused with the counter, which increments the rows down. Plus, also the macro I copied off used offset to show which column value on each row to look at.

Basically, my spreadsheet contains 28 columns, and I want it to look at columns 2,8 and 12, and if all values = yes, to delete the row. Sounds easy but not proving to be!


Application.ScreenUpdating = False
Dim Same_1 As String
Dim Same_2 As String
Dim Same_3 As String
Dim Counter As Integer
Counter = 0

Sheets("CAB Ref Data").Select
Range("a2").Select

Do While ActiveCell.Value <> ""

Counter = Counter + 1

ActiveCell.Offset(1, 0).Activate


Loop

Range("a2").Select

Do While Counter <> 0

ActiveCell.Offset(0, 1).Activate
Same_1 = ActiveCell.Value
'msgbox Same_1
ActiveCell.Offset(0, 6).Activate
Same_2 = ActiveCell.Value
'msgbox Same_2
ActiveCell.Offset(0, 4).Activate
Same_3 = ActiveCell.Value
'Msgbox Same_3


If Same_1 = "yes" And Same_2 = "yes" And Same_3 = "yes" Then


Rows(EntireRow).Select
Selection.Delete Shift:=x1up
Else
Exit Do
End If

ActiveCell.Offset(1, -11).Activate

Counter = Counter - 1

Loop


Range("a1").Select

Application.ScreenUpdating = True


End Sub

[Bob Phillips]

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "B" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cell As Range
Dim sh As Worksheet

Application.ScreenUpdating = False
With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow To 1 Step -1
If .Cells(i, "B").Value = "yes" And _
.Cells(i, "H").Value = "yes" And _
.Cells(i, "L").Value = "yes" Then
.Rows(i).Delete
End If
Next i

End With
Application.ScreenUpdating = True

End Sub
[/vba]