Macro- Delete 3 rows above and 14 below if cell contains text

[ValiMandy]

I would like a macro that will search for the following text phrases in column H and delete 3 rows above, the row the value is in and 14 rows after the value.

"Pallet 1 of 0"
"Pallet 2 of 0"
"Pallet 3 of 0"
"Pallet 2 of 1"
"Pallet 3 of 1"
"Pallet 3 of 2"

I have tried several codes and I am not sure what is the best way to approach this. I am still learning the basics of VBAs.

Thank you very much!

 

[ValiMandy]

How about an example of your data ???

It's kind of a weird spreadsheet. There are a lot of blank rows for formatting purposes and such. This is printed and needs to look a certain way. All the data is pulled from other sheets. I want them to be able to click a button and it will delete any pages that are not being utilized. They have to do this often and can't waste time manually deleting sheets. I tried using formulas and auto filters to hide rows that aren't being used but it prints blank pages because I have the pages set up a specific way. The indirect formulas are because they need to be able to delete rows and columns and not affect the formulas. I'm completely self taught through experimentation so if this is terrible, sorry!

I'll do my best.

	A	B	C	D	E	F	G	H
1	ORDER 1: PALLET 1	IMAGE					CASE COUNT
2		PO #	=IF(INDIRECT("'Pallet Build'!L4")="","",INDIRECT("'Pallet Build'!L4"))			PALLET: 1 OF	=INDIRECT("'Pallet Build'!L6")	=F2&G2
3
4			=IF(INDIRECT("'Pallet Build'!B23")="","",INDIRECT("'Pallet Build'!B23"))		=IF(INDIRECT("'Pallet Build'!C23")="","",INDIRECT("'Pallet Build'!C23"))
5
6			=IF(INDIRECT("'Pallet Build'!B24")="","",INDIRECT("'Pallet Build'!B24"))		=IF(INDIRECT("'Pallet Build'!C24")="","",INDIRECT("'Pallet Build'!C24"))
7
8			=IF(INDIRECT("'Pallet Build'!B25")="","",INDIRECT("'Pallet Build'!B25"))		=IF(INDIRECT("'Pallet Build'!C25")="","",INDIRECT("'Pallet Build'!C25"))
9
10			=IF(INDIRECT("'Pallet Build'!B26")="","",INDIRECT("'Pallet Build'!B26"))		=IF(INDIRECT("'Pallet Build'!C26")="","",INDIRECT("'Pallet Build'!C26"))
11
12			=IF(INDIRECT("'Pallet Build'!B27")="","",INDIRECT("'Pallet Build'!B27"))		=IF(INDIRECT("'Pallet Build'!C27")="","",INDIRECT("'Pallet Build'!C27"))
13
14			=IF(INDIRECT("'Pallet Build'!B28")="","",INDIRECT("'Pallet Build'!B28"))		=IF(INDIRECT("'Pallet Build'!C28")="","",INDIRECT("'Pallet Build'!C28"))
15
16			=IF(INDIRECT("'Pallet Build'!B29")="","",INDIRECT("'Pallet Build'!B29"))		=IF(INDIRECT("'Pallet Build'!C29")="","",INDIRECT("'Pallet Build'!C29"))
17
18			=IF(INDIRECT("'Pallet Build'!B30")="","",INDIRECT("'Pallet Build'!B30"))		=IF(INDIRECT("'Pallet Build'!C30")="","",INDIRECT("'Pallet Build'!C30"))
19
20			=IF(INDIRECT("'Pallet Build'!B31")="","",INDIRECT("'Pallet Build'!B31"))		=IF(INDIRECT("'Pallet Build'!C31")="","",INDIRECT("'Pallet Build'!C31"))
21
22			=IF(INDIRECT("'Pallet Build'!B32")="","",INDIRECT("'Pallet Build'!B32"))		=IF(INDIRECT("'Pallet Build'!C32")="","",INDIRECT("'Pallet Build'!C32"))
23
24			=SUM(C4:C23)		TOTAL CASES ON PALLET <tbody> </tbody>
25
26		Count Performed By: <tbody> </tbody>
27		Count Verified By: <tbody> </tbody>
28	ORDER 1: PALLET 2	IMAGE					CASE COUNT
29		PO #	=IF(INDIRECT("'Pallet Build'!L4")="","",INDIRECT("'Pallet Build'!L4"))			PALLET: 1 OF	=INDIRECT("'Pallet Build'!L6")	=F29&G29
30

<tbody>
</tbody>

This is how it appears (formulas calculated):

	A	B	C	D	E	F	G	H
1	ORDER 1: PALLET 1	IMAGE					CASE COUNT
2		PO #	123456-10			PALLET: 1 OF	1	PALLET: 1 OF 1
3
4			20		ITEM DESCRIPTION
5
6			15		ITEM DESCRIPTION
7
8			30		ITEM DESCRIPTION
9
10			45		ITEM DESCRIPTION
11
12			10		ITEM DESCRIPTION
13
14			5		ITEM DESCRIPTION
15
16			10		ITEM DESCRIPTION
17
18			2		ITEM DESCRIPTION
19
20			100		ITEM DESCRIPTION
21
22			10		ITEM DESCRIPTION
23
24			247		TOTAL CASES ON PALLET
25
26		COUNT PERFORMED BY:
27		COUNT VERIFIED BY:
28	ORDER 1: PALLET 2	IMAGE					CASE COUNT
29		PO #	123456-10			PALLET: 2 OF	1	PALLET: 2 OF 1
30

<tbody>
</tbody>

So, rows 1:27 are page 1. Rows 28:54 are page 2. This repeats until row 3420. They pull information for everything that will be on Pallet 1, page 2 is for pallet 2, etc. There is the possibility that each order will have up to 6 pallets. All the information from another sheet needs pulled onto this sheet. If there are fewer than 6 pallets it needs to delete the pages for whatever excess pallets there are.

I apologize if this is too confusing. Maybe this will help. Maybe it won't. Thank you!!

 

[MickG]

Try this in the code:-
The previous code had an extra space in the string ("PALLET: 1 OF 0")
Was :- ("PALLET: (extra space here) 1 OF 0")
Hopefully that's the problem !!!!

Dic("PALLET: 1 OF 0") = Empty
Dic("PALLET: 2 OF 0") = Empty
Dic("PALLET: 3 OF 0") = Empty
Dic("PALLET: 2 OF 1") = Empty
Dic("PALLET: 3 OF 1") = Empty
Dic("PALLET: 3 OF 2") = Empty

 

[ValiMandy]

Try this in the code:-
The previous code had an extra space in the string ("PALLET: 1 OF 0")
Was :- ("PALLET: 1 OF 0")
Hopefully that's the problem !!!!

Dic("PALLET: 1 OF 0") = Empty
Dic("PALLET: 2 OF 0") = Empty
Dic("PALLET: 3 OF 0") = Empty
Dic("PALLET: 2 OF 1") = Empty
Dic("PALLET: 3 OF 1") = Empty
Dic("PALLET: 3 OF 2") = Empty

If only!! I fixed that. I tried copying and pasting the text directly from the sheet into the vba so it would match exactly.

 

[MickG]

Try this:-

For n = 3420 To 2 Step -1
If Range("H" & n) <> "" Then Stop
    If Dic.exists(Range("H" & n).Value) Then
       Range("A" & n).Offset(-1).Resize(27).EntireRow.Delete
 End If
Next n

 

[ValiMandy]

Try this:-

For n = 3420 To 2 Step -1
If Range("H" & n) <> "" Then Stop
    If Dic.exists(Range("H" & n).Value) Then
       Range("A" & n).Offset(-1).Resize(27).EntireRow.Delete
 End If
Next n

I get an error on the "STOP" part...

 

[MickG]

Sorry !!!, you need to delete that line. I used it for testing !!!