if value x or y, delete until next value.

[tourless]

Hi Folks,
I'm working with some data that looks like this...

CAP8503MKTG	04	EA	OM
		1
CAP8504MKTG	01	EA	OW
35-38		1	PO: 0009692
CAP8504MKTG	03	EA	OW
27-5		1	PO: 0009168
CAP8505MKTG	01	EA	OP
59-1		0	PO: 0008577
59-1		25	PO: 0009692
59-1		25	PO: 0009693
CAP8505MKTG	04	EA	OP
		1

Column B holds the values of 01, 03, or 04. I need to check each value in column B and delete any rows that are 03 or 04 until the next value in Column B. My end result should look like this...

CAP8504MKTG	01	EA	OW
35-38		1	PO: 0009692
CAP8505MKTG	01	EA	OP
59-1		0	PO: 0008577
59-1		25	PO: 0009692
59-1		25	PO: 0009693

I'd like to be able to add some vba to an automated routine that I'm building to do a few more things with the data but I'm stuck on this part. Any assistance is greatly appreciated.

 

[steve the fish]

See if this works once you change the sheet name to the correct one. It assumes you start at row 1 and also that the first number is available in B1.

Sub Macro1()

Dim lr As Long, arr, i As Long, x As String, rng As Range

With Sheets("Sheet1")
    lr = .Range("C" & .Rows.Count).End(xlUp).Row
    arr = .Range("B1:B" & lr)
    For i = LBound(arr, 1) To UBound(arr, 1)
        If Len(arr(i, 1)) = 0 Then
            arr(i, 1) = x
        Else
            x = arr(i, 1)
        End If
    Next
    For i = LBound(arr, 1) To UBound(arr, 1)
        If arr(i, 1) = "03" Or arr(i, 1) = "04" Then
            If rng Is Nothing Then
                Set rng = .Rows(i)
            Else
                Set rng = Union(rng, .Rows(i))
            End If
        End If
    Next
    If Not rng Is Nothing Then rng.Delete Shift:=xlUp
End With

End Sub