PDA

View Full Version : Loop through rows and delete/keep



Alias
10-12-2012, 03:18 AM
Hello,

My VB knowledge is poor, but my program knowledge is okay.

I have rows in my excel sheet.
Each row contains one of two letters; S or F.

S = success
F = fail

I have been asked to find any row in the last month (dates are at the top of each column), where there has been 3 or more "F"s in a row. So basically on a run of 3 or more days where there is a fail.

If a row contains this, it keeps it, if there is no 3+ fails in a row, then remove it.

Can anyone advise how I would go about this? I'm assuming using a loop, and then maybe the COUNTIFS function, but I cant get anything to work...

Teeroy
10-12-2012, 06:55 PM
Try:

Sub deleteRowsWithout3Fails()
Dim lastcol As Integer
Dim j As Integer
Dim bKeepRow As Boolean
Dim rTestRow As Range

lastcol = Sheets("sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
Set rTestRow = Sheets("sheet1").Range("A2")

Do While rTestRow.Value <> ""
bKeepRow = False
For j = 1 To lastcol - 2
If Cells(rTestRow.Row, j) = "F" And Cells(rTestRow.Row, j + 1) = "F" _
And Cells(rTestRow.Row, j + 2) = "F" Then
bKeepRow = True
Exit For
End If
Next j

Set rTestRow = rTestRow.Offset(1, 0)
If Not bKeepRow Then
rTestRow.Offset(-1, 0).EntireRow.Delete
End If
Loop
End Sub