VBA to loop through 2 columns

[TravelingMan]

Hey guys,

I am a little confused on creating a macro that can loop through 2 columns

Column B and Column C

Column B would be (-0209,-0600,-0900,-0920,-0930, or -M150)

Column C would be (-0201,-0202A,-0202B,-1010,-0115,-0610,-0940,-0150, or -C150)

if none of these were found in the row, the row would be deleted.

This is what I have but I am not very adept at VBA.. as will be evident by the below..

any help would be appreciated


Sub test()
ActiveSheet.Select

Dim ws As Worksheet, i&, y&, lastRow&, value$
Set ws = ActiveWorkbook.ActiveSheet
lastRow = ws.Range("D" & ws.Rows.Count).End(xlUp).Row
For i = lastRow To 2 Step -1
For y = lastRow To 2 Step -1
value = ws.Cells(i, B).value
' Check if it contains one of the keywords.
If Not (value Like "*-0209*" _
Or value Like "*-0600*" _
Or value Like "-0900*" _
Or value Like "*-0920*" _
Or value Like "*-M150*" _
Or value Like "-0930*") _
value = ws.Cells(i, C).value
If Not (value Like "*-0201*" _
Or value Like "*-0202A*" _
Or value Like "*-0202B*" _
Or value Like "*-1010*" _
Or value Like "*-0115*" _
Or value Like "*-0610*" _
Or value Like "*-0940*" _
Or value Like "*-0150*" _
Or value Like "*-C150*") _
Then
' Protected values not found. Delete the row.
ws.Rows(i).Delete
End If
Next
End Sub

 

[vavs]

Are you trying to delete the row if column B does not contain one of the values and column C doesn't contain one of the values?

If so, I would declare the variables at the same time and with different names ValueB and ValueC

You can use the select case statement

Select case ValueB
Case not like "*-0209*" ,"*-0600*" ,"-0900*","*-0920*","*-M150*","-0930*"
select case ValueC
Case not like "*-0209*" ,"*-0600*" ,"-0900*","*-0920*","*-M150*","-0930*"
case else Delete the row
end select
case else delete the row
End select


I am running through this quickly so I just copied the B values, you can make the adjustment. What this does is it checks for the values in column B if it is protected, it checks column C. If both are good, you can add 1 to your rows and repeat until you are done. If it fails on column B it will delete the row, if it fails on column C it deletes the row even thought B was in the list. I am not sure if that is what you are trying to accomplish.

 

[Eric W]

You certainly had the right idea. I think you had an extra line with "For y=".

Here's a version that should be a little cleaner, and run very fast:

Sub DeleteRows()
Dim OKvals(2)
Dim ws As Worksheet, r As Long, LastRow As Long, i As Integer, j As Integer, MyValue As String, MyCols

    OKvals(1) = Array("-209", "-0600", "-0900", "-920", "-0930", "-M150")
    OKvals(2) = Array("-201", "-202A", "-202B", "-1010", "-0115", "-0610", "-0940", "-0150", "-C150")
    
    Set ws = ActiveSheet
    LastRow = ws.UsedRange.Row + ws.UsedRange.Rows.Count - 1
    MyCols = ws.Range("B1:C" & LastRow)
    
    For r = 2 To LastRow
        For i = 1 To 2
            MyValue = MyCols(r, i)
            For j = 0 To UBound(OKvals(i))
                If InStr(MyValue, OKvals(i)(j)) > 0 Then GoTo ValidRow:
            Next j
        Next i
        ws.Cells(r, "B") = "#N/A"
ValidRow:
    Next r
    
    Columns("B:B").SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
        
End Sub

It saves all the "good" values in arrays for easier maintenance. It then reads the entire range of the B and C columns into an internal array. This is much faster than reading each line at a time. It then reads that array checking for valid codes. If it does not find one, it flags that row with #N/A. Then the final line deletes all of the flagged lines in one shot. Very quick.

Let me know if this works for you.

 

[TravelingMan]

Eric,

This is very fast and does work to a degree. I am currently getting a type mismatch error after running it through 1 time.

 

[TravelingMan]

Eric,

I got it to work.

It works very well..

Now I just need to delete all rows with N/A

Thanks!

 

[Eric W]

The last line:

Columns("B:B").SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete

will delete every row with a #N/A in column B.