Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Filter one short list vs another very long list
I would like an example code of autofilter where on the linked sheet it will look up each part number in the yellow column and filter the pink column for that part number. Then return the column G value, the column B "filter on" value and the column N dollar amount, as displayed in the blue return example. Using the macro recorder I know the three columns have to be included in the filter and the copy the visible cells. The recorded code was to abstract for me to distill down to a proper autofilter macro. Thanks, Howard https://www.dropbox.com/s/kaiky4skoe...test.xlsm?dl=0 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Filter one short list vs another very long list
Hi Howard,
Am Thu, 23 Apr 2015 03:56:15 -0700 (PDT) schrieb L. Howard: https://www.dropbox.com/s/kaiky4skoe...test.xlsm?dl=0 please look he https://onedrive.live.com/?cid=9378A...121822A3%21326 for "Part Numbers" You have to download it, because macros are disabled in OneDrive Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Filter one short list vs another very long list
Wow, never thought it would be that concise.
Thanks a ton, this will make a great how to example. Appreciate it Claus. Howard |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Filter one short list vs another very long list
Question, please. I don't quite understand this portion, did you determine the range or was that part of the filter function that determined the A1:C54, with B & C empty? CriteriaRange:=Sheets("Sheet2").Range("A1:C54") It seems it is optional, did you put it on sheet 2 just as a reference? Howard Sub AdvFilter() Dim LRow As Long With Sheets("Parts") LRow = .Cells(Rows.Count, 10).End(xlUp).Row .Range("G1:N" & LRow).AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Sheet2").Range("A1:C54"), _ CopyToRange:=Sheets("Sheet2").Range("F1:H1"), Unique:=False End With End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Filter one short list vs another very long list
Hi Howard,
Am Thu, 23 Apr 2015 04:50:23 -0700 (PDT) schrieb L. Howard: I don't quite understand this portion, did you determine the range or was that part of the filter function that determined the A1:C54, with B & C empty? CriteriaRange:=Sheets("Sheet2").Range("A1:C54") the advanced filter demands a criteria range. And all ranges (List range, criteria range and the destination range) must have the same headers. Therefore I copied the range to sheet2 and changed the headers to the list header. You can also enter one ore more criterias to the empty columns. You can also change the headers in sheets "Part" and use that range as criteria range. Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Filter one short list vs another very long list
Hi Howard,
Am Thu, 23 Apr 2015 13:57:28 +0200 schrieb Claus Busch: Therefore I copied the range to sheet2 and changed the headers to the list header. You can also enter one ore more criterias to the empty columns. if you only want to filter by Part Number you can also change the criteria range: CriteriaRange:=Sheets("Sheet2").Range("A1:A54") Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Filter one short list vs another very long list
It just occurred to me that the link is a single sheet.
In reality the yellow list will be on one sheet and the column G, J & N will be on another sheet, in the same columns, G, J & N. Not sure if autofilter is okay with two sheets. The returns need to be on the sheet with the yellow list. Hoard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change a long columns list to short ones in 2007 Excel? | Excel Discussion (Misc queries) | |||
Auto Filter List | Excel Programming | |||
Auto list/filter | Excel Discussion (Misc queries) | |||
Long list of words to find with Filter | Excel Discussion (Misc queries) | |||
Auto Filter From List | Excel Programming |