Identify and delete target rows 1

[LGMan]

Hi all,
I've had a little task that I've done manually as the recordset was only 20 rows deep so could see all the data on one screen.
Now I've got over a 300 records and no longer sustainable.

My data is 11 Columns Wide and where the Values in both Cols J and K are less than 28 I delete the whole row.
For example row 1 contains headers
If in Row 2 cells J2 and K2 are less then 28, I would delete the row.
I guess I'd need a loop, but not sure how to initially identify my target rows.
Anyone with a pointer or suggestion is greatly appreciated.
Thanks

 

[SkipVought]

Hi,

Turn on your AutoFilter.

Filter to SHOW the rows you want to delete.

Select the rows, right-click, DELETE.

If you need that coded, turn on your macro recorder.

Post back with your recorded code if you need help customizing.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

Skip, I had discounted the Autofilter route as I need to identify on 2 cells. If I switch autofilter on and show all rows where value of Col J is less than 28, there may be values in Col K that are greater than 28 and thus I want to keep that row rather than delete it.

Or am I missing a trick with the autofilter?

Thanks

 

[LGMan]

Sorry ignore that, I'm being stupid.
I'll get my coat.......................

 

[SkipVought]

I thought you said, "If in Row 2 cells J2 [red]and[/red] K2 are less then 28, I would delete the row."


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

Perhaps my example wasn't as water tight as I wanted.
If K2 equals 21, and J2 equals 23 then I'd delete that row.
If K3 equals 22, and J3 equals 35 then I'd leave that cell

 

[SkipVought]

PERFECT!

What's stopping you if you define the criteria in BOTH column J & K, < 28?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

Skip, I have got the auto filter to work, hence my 'I'll get my coat' quote.

What I'd like to do is delete the rows automatically, can that be done with VBA. The problem that I'm having is selecting the rows.

 

[SkipVought]

If you need that coded, turn on your macro recorder.

Post back with your recorded code if you need help customizing.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

Columns("J:K").Select
Selection.Autofilter
Selection.Autofilter Field:=1, Criteria1:="<24", Operator:=xlAnd
Selection.Autofilter Field:=2, Criteria1:="<24", Operator:=xlAnd

This filters to the rows that I'd like to delete, which can be done manually, but how would you identify them in VBA since the row numbers can be different each day the the process is run.

Thanks for your time thus far.

 

[SkipVought]

    With Columns("J:K")
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="<24"
        .AutoFilter Field:=2, Criteria1:="<24"
        Range(.Cells(2, 1), .Cells(.CurrentRegion.Rows.Count, .CurrentRegion.Columns.Count)).EntireRow.Delete
    End With
    ActiveSheet.ShowAllData

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

Many Thanks Skip, you are a legend