Can anyone help with a script?

[Excellis]

Hi All
I'm just wondering if anyone could write a script for me to carry out a mundane task that tbh contains too many records to handle manually.
I am able to record macros and make simple edits but this is beyond my ken.
Ideally what I would like is to completely delete lines that fit certain criteria, or, if that can't be done, delete the contents so I can sort the rows with the blank ones at the end.
Attached is a very small sample which I believe contains all the scenarios that can and do occur.
The data is anonymised but in essence what I have is:
An extract from a LMS
2 types of vehicle a front loader & a side-saddle
2 types of training, novice & refresher, operators do a novice once followed after 3 years by a refresher and further refreshers thereafter
2 levels of operators, managers & colleagues
In the mini sheet I've indicated what action, in the sample, I need to happen.
The rules are:
Anyone with a Manager Status - delete the line
Any colleague that has completed a novice and a refresher, keep the refresher and delete the novice line
Any colleague that has completed a novice but hasn't yet completed a refresher (there will be no record for the refresher) keep the novice line
Any colleague that was supposed to complete a novice but didn't, delete the novice line
Regarding the coloured cells in pink, this is not the same colleague, it's a common name and there are 2 occurrences. They're distinguished by their clock number
In yellow, this IS the same colleague but he is proficient in both vehicles.
I'm unable to influence any of the data in the LMS so I have to work with this information.
I would also like the script to be portable across iterations of the report so if it could be run in the 'local window' (can't remember what it's officially called) that would be great.

I'm sure there will be further questions which I'm happy to answer.
Thank you in anticipation
Tom

Extract_File.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	First Name	Last Name	Clock Number	Level/Grade	Course Code	Lesson Code	Lesson Title	Expiry date	Lesson Completed Date	Days out of date		Action required from the script
2	John	Bates	5500212	Manager	B7-Front Loader	54321	B7-Front Loader Novice		10/10/2019	824		DELETE - Due to Manager Status
3	John	Bates	5500212	Manager	B7-Front Loader	B7FLRef	B7-Front Loader Refresher	9/10/2022	10/10/2019	-271		DELETE - Due to Manager Status
4	Michael	Berrisford	5500230	Customer Assistant	B7-Front Loader	54321	B7-Front Loader Novice		16/11/2018	1152		DELETE - Due to colleague has a refresher
5	Michael	Berrisford	5500230	Customer Assistant	B7-Front Loader	B7FLRef	B7-Front Loader Refresher	15/11/2021	16/11/2018	57		KEEP
6	Peter	Clark	5500248	Manager	B7-Front Loader	B7FLRef	B7-Front Loader Refresher	8/04/2022	9/04/2019	-87		DELETE - Due to Manager Status
7	Sally	Fluen	5500257	Manager	B7-Front Loader	54321	B7-Front Loader Novice		18/06/2020	572		DELETE - Due to Manager Status
8	Sally	Fluen	5500257	Manager	B7-Front Loader	B7FLRef	B7-Front Loader Refresher	18/06/2023	18/06/2020	-523		DELETE - Due to Manager Status
9	Jenny	Sendi	5500311	Customer Assistant	C17-Side Saddle	678910	C17-Side Saddle Novice		9/07/2021	186		DELETE - Due to colleague has a refresher
10	Jenny	Sendi	5500311	Customer Assistant	C17-Side Saddle	C17SSRef	C17-Side Saddle Refresher	8/07/2024	9/07/2021	-909		KEEP
11	George	Fitzpatrick	5500365	Customer Assistant	B7-Front Loader	54321	B7-Front Loader Novice					DELETE - Colleague did not complete his Novice (Col I is blank)
12	Michael	Edwards	5500392	Customer Assistant	C17-Side Saddle	678910	C17-Side Saddle Novice	17/06/2024	18/06/2021	207		KEEP - Colleague has not needed to do a refresher
13	Wendy	Carruthers	5500401	Customer Assistant	B7-Front Loader	B7FLRef	B7-Front Loader Refresher	10/11/2022	11/11/2019	-303		KEEP
14	John	Brown	5500410	Customer Assistant	B7-Front Loader	54321	B7-Front Loader Novice		22/10/2021	81		DELETE - Due to colleague has a refresher
15	John	Brown	5500410	Customer Assistant	B7-Front Loader	B7FLRef	B7-Front Loader Refresher	21/10/2024	22/10/2021	-1014		KEEP
16	Brian	Reilly	5500428	Manager	C17-Side Saddle	C17SSRef	C17-Side Saddle Refresher	30/09/2024	1/10/2021	-993		DELETE - Due to Manager Status
17	Adam	Fothergill	5500437	Customer Assistant	B7-Front Loader	54321	B7-Front Loader Novice		23/04/2020	628		DELETE - Due to colleague has a refresher
18	Adam	Fothergill	5500437	Customer Assistant	B7-Front Loader	B7FLRef	B7-Front Loader Refresher	23/04/2023	23/04/2020	-467		KEEP
19	John	Brown	5500455	Customer Assistant	B7-Front Loader	54321	B7-Front Loader Novice		23/09/2020	475		DELETE - Due to colleague has a refresher
20	John	Brown	5500455	Customer Assistant	B7-Front Loader	B7FLRef	B7-Front Loader Refresher	23/09/2023	23/09/2020	-620		KEEP
21	Paul	Clements	5500473	Customer Assistant	B7-Front Loader	54321	B7-Front Loader Novice		18/02/2021	327		DELETE - Due to colleague has a refresher
22	Paul	Clements	5500473	Customer Assistant	B7-Front Loader	B7FLRef	B7-Front Loader Refresher	18/02/2024	18/02/2021	-768		KEEP
23	Mary	Evans	5500491	Customer Assistant	B7-Front Loader	54321	B7-Front Loader Novice		30/10/2020	438		DELETE - Due to colleague has a refresher
24	Mary	Evans	5500491	Customer Assistant	B7-Front Loader	B7FLRef	B7-Front Loader Refresher	30/10/2023	30/10/2020	-657		KEEP
25	Sam	Ewing	5500509	Customer Assistant	B7-Front Loader	54321	B7-Front Loader Novice					DELETE - Colleague did not complete his Novice (Col I is blank)
26	John	Jefferies	5510201	Customer Assistant	B7-Front Loader	54321	B7-Front Loader Novice		18/06/2020	572		DELETE - Due to colleague has a refresher
27	John	Jefferies	5510201	Customer Assistant	B7-Front Loader	B7FLRef	B7-Front Loader Refresher	18/06/2023	18/06/2020	-523		KEEP
28	John	Jefferies	5510201	Customer Assistant	C17-Side Saddle	678910	C17-Side Saddle Novice		9/07/2021	186		DELETE - Due to colleague has a refresher
29	John	Jefferies	5510201	Customer Assistant	C17-Side Saddle	C17SSRef	C17-Side Saddle Refresher	8/07/2024	9/07/2021	-909		KEEP
30
31	I WANT TO BE LEFT WITH:
32	Michael	Berrisford	5500230	Customer Assistant	B7-Front Loader	B7FLRef	B7-Front Loader Refresher	15/11/2021	16/11/2018	57
33	Jenny	Sendi	5500311	Customer Assistant	C17-Side Saddle	C17SSRef	C17-Side Saddle Refresher	8/07/2024	9/07/2021	-909
34	Michael	Edwards	5500392	Customer Assistant	C17-Side Saddle	678910	C17-Side Saddle Novice	17/06/2024	18/06/2021	207
35	Wendy	Carruthers	5500401	Customer Assistant	B7-Front Loader	B7FLRef	B7-Front Loader Refresher	10/11/2022	11/11/2019	-303
36	John	Brown	5500410	Customer Assistant	B7-Front Loader	B7FLRef	B7-Front Loader Refresher	21/10/2024	22/10/2021	-1014
37	Adam	Fothergill	5500437	Customer Assistant	B7-Front Loader	B7FLRef	B7-Front Loader Refresher	23/04/2023	23/04/2020	-467
38	John	Brown	5500455	Customer Assistant	B7-Front Loader	B7FLRef	B7-Front Loader Refresher	23/09/2023	23/09/2020	-620
39	Paul	Clements	5500473	Customer Assistant	B7-Front Loader	B7FLRef	B7-Front Loader Refresher	18/02/2024	18/02/2021	-768
40	Mary	Evans	5500491	Customer Assistant	B7-Front Loader	B7FLRef	B7-Front Loader Refresher	30/10/2023	30/10/2020	-657
41	John	Jefferies	5510201	Customer Assistant	B7-Front Loader	B7FLRef	B7-Front Loader Refresher	18/06/2023	18/06/2020	-523
42	John	Jefferies	5510201	Customer Assistant	C17-Side Saddle	C17SSRef	C17-Side Saddle Refresher	8/07/2024	9/07/2021	-909
Extract

 

[Zot]

It looks to me that:
If column D is Manager, delete row
If column H is blank, delete row

If that is true then try this

Sub Test()

Dim cell As Range, rngData As Range, rngDelete As Range
Dim ws As Worksheet

Set ws = ActiveWorkbook.Sheets("Sheet1")   ' Change sheet name here if required
Set rngData = ws.Range("A2", ws.Cells(Rows.Count, "A").End(xlUp))

For Each cell In rngData
    If ws.Range("D" & cell.Row) = "Manager" Then Set rngDelete = MarkDelete(cell, rngDelete)
    If ws.Range("H" & cell.Row) = "" Then Set rngDelete = MarkDelete(cell, rngDelete)
Next
rngDelete.EntireRow.Delete

End Sub

Function MarkDelete(c As Range, rng As Range) As Range
If rng Is Nothing Then Set MarkDelete = c Else Set MarkDelete = Union(rng, c)
End Function

 

[Excellis]

It looks to me that:
If column D is Manager, delete row
If column H is blank, delete row

If that is true then try this

Sub Test()

Dim cell As Range, rngData As Range, rngDelete As Range
Dim ws As Worksheet

Set ws = ActiveWorkbook.Sheets("Sheet1")   ' Change sheet name here if required
Set rngData = ws.Range("A2", ws.Cells(Rows.Count, "A").End(xlUp))

For Each cell In rngData
    If ws.Range("D" & cell.Row) = "Manager" Then Set rngDelete = MarkDelete(cell, rngDelete)
    If ws.Range("H" & cell.Row) = "" Then Set rngDelete = MarkDelete(cell, rngDelete)
Next
rngDelete.EntireRow.Delete

End Sub

Function MarkDelete(c As Range, rng As Range) As Range
If rng Is Nothing Then Set MarkDelete = c Else Set MarkDelete = Union(rng, c)
End Function

Thank you, Zot. A classic example of not seeing the wood for the trees. With my limited knowledge and reading through your script I think you're right, but when I run the script I get an error:
Run-time '91': Object variable or With block variable not set, and when I click on debug this is highlighted: rngDelete.EntireRow.Delete
Not sure what that means.

 

[Excellis]

Thank you, Zot. A classic example of not seeing the wood for the trees. With my limited knowledge and reading through your script I think you're right, but when I run the script I get an error:
Run-time '91': Object variable or With block variable not set, and when I click on debug this is highlighted: rngDelete.EntireRow.Delete
Not sure what that means.

Realised my mistake, I forgot to change the column letters from my example to the actual sheet, I'd deleted some columns as they weren't pertinent to my request.
Instead of looking for cells that "=Manager" what do I need to do change it to cells that contain the word manager e.g. so it finds "Day Manager" or "Night Manager" and any other variation.

 

[Zot]

You meant you want to delete if there is word Manager there?

If that is so, then change the line

If ws.Range("D" & cell.Row) = "Manager" Then Set rngDelete = MarkDelete(cell, rngDelete)

to

If ws.Range("D" & cell.Row) Like "*Manager" Then Set rngDelete = MarkDelete(cell, rngDelete)

 

[Excellis]

You meant you want to delete if there is word Manager there?

Yes, basically. In trying to simplify the example I think I over-simplified it. There are about 25 different job titles that contain the word Manager and I need to lose them all.
I ran your script in the example and that worked perfectly, thank you, I'm just trying to modify it to fit the real-world version I use.

 

[Zot]

Let me modify to make it more customized

 

[Zot]

Maybe can be more simplified but this is clear and easy to add any column and words.

Sub Test()

Dim cell As Range, rngData As Range, rngDelete As Range
Dim ws As Worksheet

Set ws = ActiveWorkbook.Sheets("Sheet1")   ' Change sheet name here if required
Set rngData = ws.Range("A2", ws.Cells(Rows.Count, "A").End(xlUp))

For Each cell In rngData
    Select Case ws.Range("D" & cell.Row)
        Case "Manager", "Day Manager", "Night Manager"
            Set rngDelete = MarkDelete(cell, rngDelete)
    End Select
    Select Case ws.Range("H" & cell.Row)
        Case ""
            Set rngDelete = MarkDelete(cell, rngDelete)
    End Select
Next
rngDelete.EntireRow.Delete

End Sub

Function MarkDelete(c As Range, rng As Range) As Range
If rng Is Nothing Then Set MarkDelete = c Else Set MarkDelete = Union(rng, c)
End Function

 

[Zot]

Another variation

Sub Test()

Dim cell As Range, rngData As Range, rngDelete As Range
Dim ws As Worksheet

Set ws = ActiveWorkbook.Sheets("Sheet1")   ' Change sheet name here if required
Set rngData = ws.Range("A2", ws.Cells(Rows.Count, "A").End(xlUp))

For Each cell In rngData
    Select Case True
        Case ws.Range("D" & cell.Row) Like "*Manager"
            Set rngDelete = MarkDelete(cell, rngDelete)
        Case ws.Range("H" & cell.Row) = ""
            Set rngDelete = MarkDelete(cell, rngDelete)
    End Select
Next
rngDelete.EntireRow.Delete

End Sub

 

[Excellis]

Another variation

Sub Test()

Dim cell As Range, rngData As Range, rngDelete As Range
Dim ws As Worksheet

Set ws = ActiveWorkbook.Sheets("Sheet1")   ' Change sheet name here if required
Set rngData = ws.Range("A2", ws.Cells(Rows.Count, "A").End(xlUp))

For Each cell In rngData
    Select Case True
        Case ws.Range("D" & cell.Row) Like "*Manager"
            Set rngDelete = MarkDelete(cell, rngDelete)
        Case ws.Range("H" & cell.Row) = ""
            Set rngDelete = MarkDelete(cell, rngDelete)
    End Select
Next
rngDelete.EntireRow.Delete

End Sub

Apologies for being a pest. I now get a Compile error: Sub or function not defined, which refers to Set rngDelete = MarkDelete(cell, rngDelete). Is it something I'm doing wrong?