Delete rows if a group does not contain a colored cell

[farmerjack190]

I have a sheet which we use to aid a our warehouse movements when new stock arrives (ZFLOOR) location. What I am needing to do is to have a script that can remove a block of items if "ZFLOOR" is not contained.
In this example I need to remove rows 7-10, 12-13, 15-16 and preferably the blank row below to keep formatting etc but need to keep data in rows 21-22 in this case. Obviously "ZFLOOR" will change each time but always in Column P so rows to be deleted will be subject to a group not containing ZFLOOR or a Green conditional formatted cell.

Have no idea where to start, any help appreciated.

 

[My Aswer Is This]

It's hard for me to understand because you are referring to row numbers but your image does not show row numbers.
And including if conditional formatting makes it harder.

 

[farmerjack190]

It's hard for me to understand because you are referring to row numbers but your image does not show row numbers.
And including if conditional formatting makes it harder.

The row numbers were only referenced to this example (I did realize I cut them I just counted the rows down in the example) as it could be any combination of rows required to be removed. The conditional format isn't necessary as I only use that as we do it visually and manually, if it were automated the conditional format I would remove. FYI the C/format only highlights where it sees ZFLOOR.

 

[My Aswer Is This]

Are you saying you want to delete all rows that do not have "ZFLOOR" in column P?

 

[farmerjack190]

Are you saying you want to delete all rows that do not have "ZFLOOR" in column P?

Where there is nested rows which may be of 2 or more rows and does NOT contain ZFLOOR in any of that nested group, then remove, if ZFLOOR is in a nested group, keep all adjoining rows. continue for all nested groups down.

 

[My Aswer Is This]

I will need someone else help you.
I do not understand "Nested"

 

[farmerjack190]

I will need someone else help you.
I do not understand "Nested"

Thanks for trying. When I mean nested I mean the rows that are grouped together, eg like some that I have circled in the image now.

 

[Akuini]

@farmerjack190
Try this:

Sub farmerjack190()

Dim c As Range, f As Range, i As Long
Application.ScreenUpdating = False
Set f = Range("P2", Cells(Rows.Count, "P").End(xlUp)).SpecialCells(xlCellTypeConstants)
For i = f.Areas.Count To 1 Step -1
    Set c = f.Areas(i).Find(What:="ZFLOOR", LookIn:=xlValues, lookAt:=xlWhole, _
    SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False)
        If Not c Is Nothing Then f.Areas(i).EntireRow.Delete
Next
Application.ScreenUpdating = True

End Sub

Example:
BEFORE:

Book1
	O	P	Q	R
1
2	Owen	3	Z	Manuel
3	Giovanni	2	O	Adrien
4	Jeremiah	1	F	Mario
5
6	Clayton	6	X	Joe
7	Marvin	ZFLOOR	J	Paxton
8
9
10	Ariel	12	W	Patrick
11	Iker	10	U	Boone
12	Blaise	11	B	Zein
13
14
15	Rex	ZFLOOR	N	Kellen
16	Ruben	17	M	Clyde
17	Aydin	18	K	Mark
18
Sheet1

AFTER:

Book1
	O	P	Q	R
1
2	Owen	3	Z	Manuel
3	Giovanni	2	O	Adrien
4	Jeremiah	1	F	Mario
5
6
7
8	Ariel	12	W	Patrick
9	Iker	10	U	Boone
10	Blaise	11	B	Zein
11
12
13
Sheet1

 

[farmerjack190]

@farmerjack190
Try this:

Sub farmerjack190()

Dim c As Range, f As Range, i As Long
Application.ScreenUpdating = False
Set f = Range("P2", Cells(Rows.Count, "P").End(xlUp)).SpecialCells(xlCellTypeConstants)
For i = f.Areas.Count To 1 Step -1
    Set c = f.Areas(i).Find(What:="ZFLOOR", LookIn:=xlValues, lookAt:=xlWhole, _
    SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False)
        If Not c Is Nothing Then f.Areas(i).EntireRow.Delete
Next
Application.ScreenUpdating = True

End Sub

Example:
BEFORE:

Book1
	O	P	Q	R
1
2	Owen	3	Z	Manuel
3	Giovanni	2	O	Adrien
4	Jeremiah	1	F	Mario
5
6	Clayton	6	X	Joe
7	Marvin	ZFLOOR	J	Paxton
8
9
10	Ariel	12	W	Patrick
11	Iker	10	U	Boone
12	Blaise	11	B	Zein
13
14
15	Rex	ZFLOOR	N	Kellen
16	Ruben	17	M	Clyde
17	Aydin	18	K	Mark
18
Sheet1

AFTER:

Book1
	O	P	Q	R
1
2	Owen	3	Z	Manuel
3	Giovanni	2	O	Adrien
4	Jeremiah	1	F	Mario
5
6
7
8	Ariel	12	W	Patrick
9	Iker	10	U	Boone
10	Blaise	11	B	Zein
11
12
13
Sheet1

Hi, thanks for this but this code deletes the rows I want to keep and leaves the ones I would like removed, hence the macro works perfect but can you reverse the selection to be deleted so it keeps the rows sectioned with ZFLOOR? I really really appreciate your help.

 

[Akuini]

Sorry, misunderstood the criteria, try this one:

Sub farmerjack190_2()

Dim c As Range, f As Range, i As Long
Application.ScreenUpdating = False
Set f = Range("P2", Cells(Rows.Count, "P").End(xlUp)).SpecialCells(xlCellTypeConstants)
For i = f.Areas.Count To 1 Step -1
    Set c = f.Areas(i).Find(What:="ZFLOOR", LookIn:=xlValues, lookAt:=xlWhole, _
    SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False)
        If c Is Nothing Then f.Areas(i).EntireRow.Delete
Next
Application.ScreenUpdating = True

End Sub