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

V

ValiMandy

New Member
Joined
May 10, 2016
Messages
31
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!
 
Sort by date Sort by votes
MickG said:
How about an example of your data ???
Click to expand...


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. :)


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

This is how it appears (formulas calculated):

[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ORDER 1: PALLET 1[/TD]
[TD]IMAGE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CASE COUNT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]PO #[/TD]
[TD]123456-10[/TD]
[TD][/TD]
[TD][/TD]
[TD]PALLET: 1 OF[/TD]
[TD]1[/TD]
[TD]PALLET: 1 OF 1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]20[/TD]
[TD][/TD]
[TD]ITEM DESCRIPTION[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]15[/TD]
[TD][/TD]
[TD]ITEM DESCRIPTION[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD]30[/TD]
[TD][/TD]
[TD]ITEM DESCRIPTION[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]45[/TD]
[TD][/TD]
[TD]ITEM DESCRIPTION[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]ITEM DESCRIPTION[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD]ITEM DESCRIPTION[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]ITEM DESCRIPTION[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD]ITEM DESCRIPTION[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD][/TD]
[TD]ITEM DESCRIPTION[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]ITEM DESCRIPTION[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD][/TD]
[TD][/TD]
[TD]247[/TD]
[TD][/TD]
[TD]TOTAL CASES ON PALLET[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD][/TD]
[TD]COUNT PERFORMED BY:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD][/TD]
[TD]COUNT VERIFIED BY:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]ORDER 1: PALLET 2[/TD]
[TD]IMAGE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CASE COUNT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD][/TD]
[TD]PO #[/TD]
[TD]123456-10[/TD]
[TD][/TD]
[TD][/TD]
[TD]PALLET: 2 OF[/TD]
[TD]1[/TD]
[TD]PALLET: 2 OF 1[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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!!
 
Last edited:
Upvote 0

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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 !!!!
Code: 
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
 
Last edited:
Upvote 0
MickG said:
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 !!!!
Code: 
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
Click to expand...

If only!! I fixed that. I tried copying and pasting the text directly from the sheet into the vba so it would match exactly. :(
 
Last edited:
Upvote 0
Try this:-
Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
How to get a specific result using IF formula based on multiple conditions?
2 3
Replies
25
Views
1K
rizwanulhasan
R
Z
  • Question Question
Delete Rows not needed Macro
2
Replies
12
Views
278
zone709
Z
S
  • Solved
Delete Rows with contents starting with 1 via Visual Basic
Replies
3
Views
55
Joe4
Joe4
A
  • Question Question
Macro copy text above
Replies
1
Views
132
Kaper
Kaper
T
  • Solved
Find #REF! errors in multiple tables and delete those rows
Replies
7
Views
228
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,236
Messages
6,170,906
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top