Need help with deleting rows that match a list

S

silverback24

Board Regular
Joined
Jul 30, 2013
Messages
58
Ok, I feel like a moron. I found a code that works great as long as the sheet is named sheet1, I cannot figure out how to make it work for my sheet named Hold Queue
Any help is appreciated. 8 numbers in sheet2 cells A2:A9 Searching column A in Hold Queue, 1500 is arbitrary large number I put there.

Code:

Option Explicit
Sub FilterMulti()

Dim i As Integer
Dim ar(1 To 9) As String

For i = 1 To 9 'Start in Row 2
ar(i) = Sheet2.Range("A" & i + 1)
Next i

Sheet1.[A1:J1500].AutoFilter 1, ar, xlFilterValues
Sheet1.[A2:A1500].EntireRow.Delete
Sheet1.[a1].AutoFilter 'Turn Filter Off
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
The problem I am having is this works when I put the data into a new workbook with the same names but it doesn't work with my workbook for some reason. There is no error when it runs. I can paste my other macro if that would help.
 
Upvote 0
Option Explicit

Sub HoldQueue()
'
' HoldQueue Macro
'

'
Dim LastRow As Long

'Clear Hold Queue Sheet
Sheets("Hold Queue").Select
Cells.Select
Selection.ClearContents
Columns("G:G").Select
Selection.FormatConditions.Delete

'Import Hold Queue File
With ActiveSheet.QueryTables.Add(Connection:="TEXT;Z:\txt\CLINTHOLDQ.TXT", _
Destination:=Range("$A$1"))
'.CommandType = 0
.Name = "CLINTHOLDQ_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

'Adding Headers
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "Account Number"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Order Date"
Range("C1").Select
ActiveCell.FormulaR1C1 = "IHR"
Range("D1").Select
ActiveCell.FormulaR1C1 = "NAME"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Reference Number"
Range("F1").Select
ActiveCell.FormulaR1C1 = "PO Number"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Ship Date"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Pick Time"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Pack Time"
Range("K1").Select
ActiveCell.FormulaR1C1 = "Account Name"
Range("L1").Select
ActiveCell.FormulaR1C1 = "Segment"
Cells.Select
ActiveSheet.Range("$A$1:$L$30000").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, _
6), Header:=xlYes
Columns("F:F").Select
Selection.NumberFormat = "0"

'Fixing order Date
Range("J1").Select
ActiveCell.FormulaR1C1 = "ODD"
LastRow = Range("B" & Rows.Count).End(xlUp).Row 'Find the row number of the last cell used in column "B"
Range("J2").Resize(LastRow - 1).FormulaR1C1 = "=DATE(LEFT(RC[-8],4),MID(RC[-8],5,2),RIGHT(RC[-8],2))"
Columns("J:J").Select
Selection.Copy
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("B:B").Select
Application.CutCopyMode = False
Selection.NumberFormat = "m/d/yyyy"
Columns("J:J").Select
Selection.ClearContents
Range("J1").Select
ActiveCell.FormulaR1C1 = "Notes"

'Initially Formatting Columns
Columns("A:L").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Rows("1:1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("A:L").Select
Columns("A:L").EntireColumn.AutoFit
Columns("E:E").Select
Selection.NumberFormat = "0"
Columns("E:E").EntireColumn.AutoFit

'Adding Ship Date and Notes Formulas
'Formula without sameday shipping
'LastRow = Range("B" & Rows.Count).End(xlUp).Row 'Find the row number of the last cell used in column "B"
'Range("G2").Resize(LastRow - 1).FormulaR1C1 = "=RC[-5]+VLOOKUP(RC[-6],'Rules Sheet'!C[-6]:C[-4],3,FALSE)+VLOOKUP(WEEKDAY('Hold Queue'!RC[-5],1),'Rules Sheet'!R1C8:R8C9,2,FALSE)+VLOOKUP(WEEKDAY(RC[-5]+VLOOKUP(RC[-6],'Rules Sheet'!C[-6]:C[-4],3,FALSE)+VLOOKUP(WEEKDAY('Hold Queue'!RC[-5],1),'Rules Sheet'!R1C8:R8C9,2,FALSE),1),'Rules Sheet'!R10C8:R17C9,2,FALSE)"

'Formula with same day shipping
LastRow = Range("B" & Rows.Count).End(xlUp).Row 'Find the row number of the last cell used in column "B"
Range("G2").Resize(LastRow - 1).FormulaR1C1 = "=RC[-5]+INDEX('Rules Sheet'!C[-3],MATCH(CONCATENATE('Hold Queue'!RC[-6],IF(LEN(RC[-4])=1,""-0"",""-""),'Hold Queue'!RC[-4]),'Rules Sheet'!C[-4],-1))+VLOOKUP(WEEKDAY('Hold Queue'!RC[-5],1),'Rules Sheet'!R1C9:R8C10,2,FALSE)+VLOOKUP(WEEKDAY(RC[-5]+INDEX('Rules Sheet'!C[-3],MATCH(CONCATENATE('Hold Queue'!RC[-6],""-"",'Hold Queue'!RC[-4]),'Rules Sheet'!C[-4],-1))+VLOOKUP(WEEKDAY('Hold Queue'!RC[-5],1),'Rules Sheet'!R1C9:R8C10,2,FALSE),1),'Rules Sheet'!R10C9:R17C10,2,FALSE)+IF(ISNUMBER(RC[1]),RC[1],0)"
LastRow = Range("B" & Rows.Count).End(xlUp).Row 'Find the row number of the last cell used in column "B"
Range("J2").Resize(LastRow - 1).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-4],'Note Sheet'!C[-9]:C[-8],2,FALSE),IFERROR(VLOOKUP(RC[-5],'Note Sheet'!C[-9]:C[-8],2,FALSE),""""))"

'Adding Conditional Formatting to Column G and J
Columns("G:G").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=G1<today()"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5263615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=G1=TODAY()"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveWindow.SmallScroll Down:=-15
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=G1>TODAY()"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=G1="""""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Pattern = xlNone
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("G1").Select
Selection.FormatConditions.Delete

Columns("L:L").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="trad", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.399945066682943
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:="perf", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 16751103
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:="ecommerce", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5627318
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

'Adjusting and Hiding Columns
Columns("B:C").Select
Range("C1").Activate
Selection.EntireColumn.Hidden = True
Columns("A:A").ColumnWidth = 15
Columns("D:D").ColumnWidth = 40
'Columns("F:F").ColumnWidth = 53.14
Columns("G:G").ColumnWidth = 30
Columns("H:H").ColumnWidth = 15
Columns("I:I").ColumnWidth = 15
Columns("J:J").ColumnWidth = 55
Columns("K:K").ColumnWidth = 30
Columns("L:L").ColumnWidth = 15
Columns("G:G").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("H:H").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("I:I").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

'Sort by Color (Red, Yellow, Green) and Ship Date
ActiveWorkbook.Worksheets("Hold Queue").sort.SortFields.Clear
ActiveWorkbook.Worksheets("Hold Queue").sort.SortFields.Add(Range("G2:G5000") _
, xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _
80, 80)
ActiveWorkbook.Worksheets("Hold Queue").sort.SortFields.Add(Range("G2:G5000") _
, xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _
192, 0)
ActiveWorkbook.Worksheets("Hold Queue").sort.SortFields.Add(Range("G2:G5000") _
, xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(146, _
208, 80)
ActiveWorkbook.Worksheets("Hold Queue").sort.SortFields.Add Key:=Range( _
"G2:G5000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Hold Queue").sort
.SetRange Range("A1:H5000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

'Re-adding Formula with same day shipping
LastRow = Range("B" & Rows.Count).End(xlUp).Row 'Find the row number of the last cell used in column "B"
Range("G2").Resize(LastRow - 1).FormulaR1C1 = "=RC[-5]+INDEX('Rules Sheet'!C[-3],MATCH(CONCATENATE('Hold Queue'!RC[-6],IF(LEN(RC[-4])=1,""-0"",""-""),'Hold Queue'!RC[-4]),'Rules Sheet'!C[-4],-1))+VLOOKUP(WEEKDAY('Hold Queue'!RC[-5],1),'Rules Sheet'!R1C9:R8C10,2,FALSE)+VLOOKUP(WEEKDAY(RC[-5]+INDEX('Rules Sheet'!C[-3],MATCH(CONCATENATE('Hold Queue'!RC[-6],""-"",'Hold Queue'!RC[-4]),'Rules Sheet'!C[-4],-1))+VLOOKUP(WEEKDAY('Hold Queue'!RC[-5],1),'Rules Sheet'!R1C9:R8C10,2,FALSE),1),'Rules Sheet'!R10C9:R17C10,2,FALSE)+IF(ISNUMBER(RC[1]),RC[1],0)"
LastRow = Range("B" & Rows.Count).End(xlUp).Row 'Find the row number of the last cell used in column "B"
Range("J2").Resize(LastRow - 1).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-4],'Note Sheet'!C[-9]:C[-8],2,FALSE),IFERROR(VLOOKUP(RC[-5],'Note Sheet'!C[-9]:C[-8],2,FALSE),""""))"
LastRow = Range("B" & Rows.Count).End(xlUp).Row 'Find the row number of the last cell used in column "B"
Range("K2").Resize(LastRow - 1).FormulaR1C1 = "=VLOOKUP(RC[-10],'Rules Sheet'!C[2]:C[3],2,FALSE)"
LastRow = Range("B" & Rows.Count).End(xlUp).Row 'Find the row number of the last cell used in column "B"
Range("L2").Resize(LastRow - 1).FormulaR1C1 = "=VLOOKUP(RC[-11],'Rules Sheet'!C[1]:C[3],3,FALSE)"

'Remove unwanted accounts
Dim i As Integer
Dim ar(1 To 9) As String

For i = 1 To 9 'Start in Row 2
ar(i) = Sheet2.Range("A" & i + 1)
Next i

Sheets("Hold Queue").[A1:J1500].AutoFilter 1, ar, xlFilterValues
Sheets("Hold Queue").[A2:A1500].EntireRow.Delete
Sheets("Hold Queue").[a1].AutoFilter 'Turn Filter Off

'Automation
'Application.OnTime Now + TimeValue("00:15:00"), "HoldQueue"
End Sub

</today()"
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Delete row if field contains Apostrophe
Replies
3
Views
69
Peterfc2
P
E
  • Solved
Delete Row if
Replies
6
Views
150
Eric Penfold
E
M
  • Solved
Comparing two columns on different sheets and deleting the entire row if any cell is different
Replies
9
Views
349
Logit
Logit
S
  • Solved
Delete rows before a certain time
2 3
Replies
26
Views
765
Joe4
Joe4
N
  • Question Question
AutoFilter Field Macro Returns a Header Row only
2
Replies
11
Views
102
MARK858
MARK858

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

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