Quick macro for filtering on certain values

[jdmc45]

Hi everybody,

I have a data set in the following form - column A is date, column B is code, column C is price. I also have a set of codes whose data, code (obviously) and price I would like. Up until now I have been filtering the full dataset, selecting the codes that I would like the data for manually, copying and pasting this selection to another tab. This is obviously very cumbersome and slow, as I have many many worksheets to do.

If any would be so kind to help me with a quick macro in VBA to complete this task it would be very much appreciated.

cheers,

James

 

[jdmc45]

Also, I have tried doing this with a for loop but is very slow, as for each code it goes on by one through the whole data set to find the code. If there is a faster way to do this it would be very much appreciated! I'm sure there is an optimised way to do this

 

[Peter_SSs]

Any chance of a small set of sample data and expected results to help clarify what you want?

 

[jdmc45]

Any chance of a small set of sample data and expected results to help clarify what you want?

Column A        Column B     Column C
01/05/2007     ABC             96.10
01/03/2008     ABD             94.10
....

This list extends for about 28,000 or so cells.

I have a list of codes (about 20 or so) including DBC, ABD, etc

I want code to input the list of codes (20 or so) from the worksheet and filter the full dataset (at the top) to only show data with those codes. The number of specific codes to filter on will change for every worksheet, and I have hundreds of worksheets to do.

Hope that helps.

 

[jdmc45]

The output will be only the data with the codes I specified, so if this involves a copy and paste to different location, stopping the autofilter, clear selection then that's fine.

Output

Column A      Column B    Column C
01/03/2004    DBC           934.2

 

[jdmc45]

This code still has an element of manual input and is definitely not ideal, and there is an issue with the AutoFilter range but gives you an idea about what I am trying to do. I have the full data in the DataSheet tab and want the output in the FilterData tab.

Sub Filter()
 
Application.ScreenUpdating = False
Sheets("FilterData").Range("A2:C" & ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row).ClearContents
'AutoFilter
Worksheets("DataSheet").Range("B2:B" & ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row).Formula = _
    "=OR(B2=IRBSWPEURQM1,B2=IRBSWPEURQM2,B2=IRBSWPEURQM3,B2=IRBSWPEURQM4,B2=IRBSWPEURQM5,B2=IRBSWPEURQM6,B2=IRBSWPEURQM7,B2=IRBSWPEURQM9,B2=IRBSWPEURQM12,B2=IRBSWPEURQM15,B2=IRBSWPEURQM16,B2=IRBSWPEURQM17,B2=IRBSWPEURQM18)"
Worksheets("DataSheet").Range("A1:C" & ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row).AutoFilter Field:=27, Criteria1:=True
'Copy
Worksheets("DataSheet").Range("A2:C" & ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row).Copy _
    Destination:=Worksheets("FilterData").Range("A2")
'AutoFilter Off
Worksheets("DataSheet").Range("A1:C" & ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row).AutoFilter
Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

No, I'm afraid I still can't really catch on.

- Your sample data was VERY small.
- You describe that you have 3 columns but your code is filtering column 27.
- Your code inserts formulas into the DataSheet. Those formulas appear to used named ranges which I don't know about so I can't manufacture a small sheet like yours.
- Are those formulas related to this task?
- Which is the ActiveSheet?
- You talk about specifying codes. Where/how are you specifying those?
- What version of Excel are you using?