VBA - if cell value true, offset, check value then delete if criteria met

Hi all,

Been a while since I have been on here or even coded in VBA, little rusty so need some help and start building a template toolbox for myself.
What i need the code to do is Look at a value in a column, then when it finds it, offset below and check to see what the next value is.
If it is "AVAIL" or "ENROUTE" then delete the entire row, then rinse and repeat.
Once i have the logic built for this one, i can modify the rest to do LOFF etc.
Here is the logic to the code I want, but of course its not working, could be as simple as I forget that i need to define something or im using the wrong suffix etc.

Sub work_filter()








Dim LR As Long




Application.ScreenUpdating = False
    
    For LR = Range("F" & Rows.Count).End(xlUp).Row To 2 Step -1
        If Range("F" & LR).Value = "LON" Then
            ActiveCell.Offset(1, 0).Activate
                If ActiveCell.Value = "ENROUTE" Or "AVAIL" Then
                Rows(LR).EntireRow.Delete
                End If
        End If
            
    Next LR




Application.ScreenUpdating = True








End Sub

Re: VBA - if cell value true, offset, check value then delete if criteria met

hey max,
Thanks for the comment back. I had a look and offset nowadays (i use to use it back in 2010), seems the wrong way to go, so all you are doing is adding a +1 to the cell count and checking the value.
doesnt seem to work,it deleted one "LON" and that was it, from my understanding this however looks from the bottom up yes? I kinda need it to go from top to bottom.

So the list will look something like this

Column F
LON
AVAIL
JOB
ENROUTE
LOF
LON
ENROUTE
AVAIL
JOB
ENROUTE
LOF

I need it to look at the value LON and if below it BEFORE the first JOB it has avil or enroute, to delete it. sometimes could be just LON then AVAIL or LON then ENROUTE or both.

Re: VBA - if cell value true, offset, check value then delete if criteria met

hey max, I made some other code but it does the same thing as your code.. it only deletes the LON, what i need is if ENROUTE, AVAIL is between LON and ONJOB then delete the AVAIL/ENROUTE

[TABLE="width: 77"]

LON

WRAPUP

ENROUTE

ONJOB

AVAIL

LOFF

ENROUTE

ONJOB

AVAIL

ENROUTE

ONJOB

WRAPUP

ENROUTE

ENROUTE

ONJOB

AVAIL

ENROUTE

ONJOB

AVAIL

ENROUTE

ONJOB

AVAIL

[/TABLE]

Firstrow = .UsedRange.Cells(1).Row
        lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row




        'We loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Firstrow To lastrow Step 1




            'We check the values in the F column in this example
   With .Cells(Lrow, "F")




                If .Value = "LON" Then
                 ' If its LON then skip to the next row to see if its AVAIL or ENROUTE
                    If .Offset(1, 0).Value = "AVAIL" Or .Offset(1, 0).Value = "ENROUTE" Or .Offset(1, 0).Value = "EOD" Then .EntireRow.Delete
                    




                End If




            End With




        Next Lrow




    End With

Re: VBA - if cell value true, offset, check value then delete if criteria met

so i need to remove all the enroute/avail (and others) that are between LON and the first instance of ONJOB (don't need to declare first instance i think as they need to LON before they do their first job anyway.

Re: VBA - if cell value true, offset, check value then delete if criteria met

max I think the above post got it, ill run the script over a few sheets, but from what I can see it did the job.
Ill add some extra sub routines to remove LON and some other values. and then repeat it for LOF.
+1

Re: VBA - if cell value true, offset, check value then delete if criteria met

works a treat, was about to use this as a template for the other step of the code. Many thanks, rep added