VBA Filter on column

cynthi263

New Member
Joined
May 14, 2019
Messages
19
Hi, I am trying to have the macro filter out a list of items i have in a specific column (there are over 600 items), copy that info and paste it in another cell. I've tried it out using a handful of items and it worked but when i pasted the entire list i kept getting errors. Since there are so many, could i have the Macro look at the list on another tab (product ids) and do it that way instead of having to enter all the items in the VBA page?

VBA Code:
Sub NM()
Set wb = ThisWorkbook

Set sh1 = wb.Sheets("OL Merged New")
Set sh2 = wb.Sheets("NM IDs")
Set sh3 = wb.Sheets("Prod IDs")

'filter nm ids and move to nm id tab

sh1.Range("A2:AB3000").AutoFilter Field:=16, Criteria1:=Array("1", "2", "3", "4", ETC), Operator:=xlFilterValues

sh1.Range("A2:AB3000").SpecialCells(xlCellTypeVisible).Copy
With sh2
sh2.Cells(2, 1).PasteSpecial xlPasteAll
End With
sh1.Range("A2:AB3000").Offset(1, 0).EntireRow.Delete

sh1.Range("A2:AB3000").AutoFilter Field:=16


End Sub
 
Last edited by a moderator:

Some videos you may like

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.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Ary = sh3.Range("A2", sh3.Range("A" & Rows.Count).End(xlUp))
sh1.Range("A2:AB3000").AutoFilter Field:=16, Criteria1:=Application.Transpose(Ary), Operator:=xlFilterValues
 

cynthi263

New Member
Joined
May 14, 2019
Messages
19
t
How about
VBA Code:
Ary = sh3.Range("A2", sh3.Range("A" & Rows.Count).End(xlUp))
sh1.Range("A2:AB3000").AutoFilter Field:=16, Criteria1:=Application.Transpose(Ary), Operator:=xlFilterValues

That did it! thank you so much!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

cynthi263

New Member
Joined
May 14, 2019
Messages
19

ADVERTISEMENT

Hello, regarding the same macro - i have the below and noticed that the line right under the header when autofiltered is not being deleted, causing a duplication of the line?

1592576353069.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
Is your header in row 1 or row 2?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
In that case change the A2 in the range to A1
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,321
Messages
5,624,011
Members
416,004
Latest member
reitz1

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
Top