Filtering without Range

[blu817]

Hello

I am trying to create a macro that can count how many times a stock shows up during a particular day. It needs to do a few things:

1. Filter for prior day or most recent date in column N
2. Filter column H for Volatility; its what the stock has been categorized as
3. Remove duplicates from column D, which is the name of the stocks, to show only single values
4. Show a count of how many symbols

I created the following macro but it only works just on the spreadsheet I created. I feel like it has to do with the range some of my spreadsheets have more lines than others. I've tried expanding it but still doesn't work on other spreadsheets. Any suggestions would be great



Rows("1:1").Select
Selection.AutoFilter

ActiveSheet.Range("$A$1:$N$77081").AutoFilter Field:=14, Operator:= _
xlFilterValues, Criteria2:=Array(2, Date - 1)
ActiveSheet.Range("$A$1:$N$77081").AutoFilter Field:=8, Criteria1:= _
"Volatility"

Columns("D:D").Select
Selection.Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Range("$A$1:$A$976574").RemoveDuplicates Columns:=1, Header:= _
xlYes
Range("B2").Select
ActiveCell.FormulaR1C1 = "=COUNTA(RC[-1]:R[998]C[-1])"

 

[Leith Ross]

Hello blu817,

What is not happening on the other worksheets? Please be specific.

 

[blu817]

When I try using it on an excel from 10/22, the first line for filter pulls nothing.

 

[Leith Ross]

Hello blu817,

Try this version of your macro and let me know the results.

Sub Test()


    Dim NewWks  As Worksheet
    Dim Rng     As Range
    Dim RngEnd  As Range
    Dim Wks     As Worksheet
    
        Set Wks = ActiveSheet
            Wks.AutoFilterMode = False
        
        Set Rng = Wks.Range("A1:N1")
        
        Set RngEnd = Wks.Columns("A:N").Find("*", , xlFormulas, xlWhole, xlByColumns, xlPrevious, False, False, False)
            If RngEnd Is Nothing Then Exit Sub
        
        Set Rng = Rng.Resize(RowSize:=RngEnd.Row - Rng.Row + 1)
            Rng.AutoFilter Field:=14, Operator:=xlFilterValues, Criteria2:=Array(2, Date - 1)
            Rng.AutoFilter Field:=8, Criteria1:="Volatility"
           
        On Error Resume Next
            Set Rng = Rng.SpecialCells(xlCellTypeVisible)
            If Err = 0 Then
                Set NewWks = Worksheets.Add(After:=Wks)
                Rng.RemoveDuplicates Columns:=1, Header:=xlYes
                Rng.Copy NewWks.Range("A1")
                Application.CutCopyMode = False
                NewWks.Cells(1, "B").Formula = "=COUNTA(RC[-1]:R[998]C[-1])"
            End If
        On Error GoTo 0


End Sub

 

[blu817]

Odd the results display nothing, anything I can provide to help you?

 

[Leith Ross]

Hello blu817,

If you can post a copy of the workbook that would be a big help.

 

[blu817]

Sorry I wasnt able to attach the excel due to firewall but here are some sample lines. Let me know if this helps

FININSTRTYPE

FININSTRSYMBOL

CUSIP

FININSTRNAME

EXPDATE

STRIKE

PUT_CALLCODE

OVERRIDETYPE

PREVIOUS_PRICE

EDITED_PRCE

CREATEDBY

CREATIONDATE

LAST_EDITED_BY

LAST_EDITED_DATE

DEBT

135087UL6

Canadian Government Bond

###################

Final Price

null

0

aa

10/26/18 17:41 a

bb

10/26/18 17:41 a

DEBT

135087UL6

Canadian Government Bond

###################

Final Price

null

0

aa

10/26/18 17:57 a

bb

10/26/18 17:57 a

DEBT

135087VW1

Canadian Government Bond

###################

Final Price

null

0

aa

10/26/18 17:41 a

bb

10/26/18 17:41 a

DEBT

135087VW1

Canadian Government Bond

###################

Final Price

null

0

aa

10/26/18 17:57 a

bb

10/26/18 17:57 a

DEBT

135087WL4

Canadian Government Bond

###################

Final Price

null

0

aa

10/26/18 17:41 a

bb

10/26/18 17:41 a

DEBT

135087WL4

Canadian Government Bond

###################

Final Price

null

0

aa

10/26/18 17:57 a

bb

10/26/18 17:57 a

DEBT

135087WV2

Canadian Government Bond

###################

Final Price

null

0

aa

10/26/18 17:41 a

bb

10/26/18 17:41 a

DEBT

135087WV2

Canadian Government Bond

###################

Final Price

null

0

aa

10/26/18 17:57 a

bb

10/26/18 17:57 a

DEBT

135087XQ2

Canadian Government Bond

###################

Final Price

null

0

aa

10/26/18 17:41 a

bb

10/26/18 17:41 a

DEBT

135087XQ2

Canadian Government Bond

###################

Final Price

null

0

aa

10/26/18 17:57 a

bb

10/26/18 17:57 a

DEBT

135087XW9

Canadian Government Bond

###################

Final Price

null

0

aa

10/26/18 17:41 a

bb

10/26/18 17:41 a

DEBT

135087XW9

Canadian Government Bond

###################

Final Price

null

0

aa

10/26/18 17:57 a

bb

10/26/18 17:57 a

DEBT

135087YQ1

Canadian Government Bond

###################

Final Price

null

0

aa

10/26/18 17:41 a

bb

10/26/18 17:41 a

DEBT

135087YQ1

Canadian Government Bond

###################

Final Price

null

0

aa

10/26/18 17:57 a

bb

10/26/18 17:57 a

DEBT

912810QA9

United States Treasury Note/Bond

###################

Final Price

null

0

aa

10/26/18 17:40 a

bb

10/26/18 17:40 a

DEBT

912810QA9

United States Treasury Note/Bond

###################

Final Price

null

0

aa

10/26/18 17:57 a

bb

10/26/18 17:57 a

EQUI

AALPH

99999AAL6

Adjusted AMR Corporation Placeholder

###################

Final Price

null

0

aa

10/26/18 17:52 a

bb

10/26/18 17:52 a

EQUI

AALPH

99999AAL6

Adjusted AMR Corporation Placeholder

###################

Final Price

null

0

aa

10/26/18 17:45 a

bb

10/26/18 17:45 a

OPTN

AAU

null

Almaden Minerals Ltd

11/16/18 0:00 a

0000002.500

C

Volatility

6.653620362

2

aa

10/22/18 13:50 a

bb

10/22/18 13:50 a

OPTN

AAU

null

Almaden Minerals Ltd

11/16/18 0:00 a

0000002.500

C

Volatility

6.653620362

2

aa

10/22/18 13:50 a

bb

10/22/18 13:50 a

OPTN

AAU

null

Almaden Minerals Ltd

11/16/18 0:00 a

0000002.500

P

Volatility

7.370314107

2

aa

10/22/18 13:50 a

bb

10/22/18 13:50 a

OPTN

AAU

null

Almaden Minerals Ltd

11/16/18 0:00 a

0000002.500

P

Volatility

7.370314107

2

aa

10/22/18 13:50 a

bb

10/22/18 13:50 a

OPTN

AAU

null

Almaden Minerals Ltd

11/16/18 0:00 a

0000005.000

C

Volatility

7.804109432

2

aa

10/22/18 13:50 a

bb

10/22/18 13:50 a

OPTN

AAU

null

Almaden Minerals Ltd

11/16/18 0:00 a

0000005.000

C

Volatility

7.804109432

2

aa

10/22/18 13:50 a

bb

10/22/18 13:50 a

OPTN

AAU

null

Almaden Minerals Ltd

11/16/18 0:00 a

0000005.000

P

Volatility

8.541032034

2

aa

10/22/18 13:50 a

bb

10/22/18 13:50 a

OPTN

AAU

null

Almaden Minerals Ltd

11/16/18 0:00 a

0000005.000

P

Volatility

8.541032034

2

aa

10/22/18 13:50 a

bb

10/22/18 13:50 a

OPTN

AAU

null

Almaden Minerals Ltd

12/21/18 0:00 a

0000002.500

C

Volatility

3.388951767

2

aa

10/22/18 13:50 a

bb

10/22/18 13:50 a

OPTN

AAU

null

Almaden Minerals Ltd

12/21/18 0:00 a

0000002.500

C

Volatility

3.388951767

2

aa

10/22/18 13:50 a

bb

10/22/18 13:50 a

OPTN

AAU

null

Almaden Minerals Ltd

12/21/18 0:00 a

0000002.500

P

Volatility

4.353259183

2

aa

10/22/18 13:50 a

bb

10/22/18 13:50 a

OPTN

AAU

null

Almaden Minerals Ltd

12/21/18 0:00 a

0000002.500

P

Volatility

4.353259183

2

aa

10/22/18 13:50 a

bb

10/22/18 13:50 a

OPTN

AAU

null

Almaden Minerals Ltd

12/21/18 0:00 a

0000005.000

C

Volatility

4.114042177

2

aa

10/22/18 13:50 a

bb

10/22/18 13:50 a

OPTN

AAU

null

Almaden Minerals Ltd

12/21/18 0:00 a

0000005.000

C

Volatility

4.114042177

2

aa

10/22/18 13:50 a

bb

10/22/18 13:50 a

OPTN

AAU

null

Almaden Minerals Ltd

12/21/18 0:00 a

0000005.000

P

Volatility

5.081096393

2

aa

10/22/18 13:50 a

bb

10/22/18 13:50 a

OPTN

AAU

null

Almaden Minerals Ltd

12/21/18 0:00 a

0000005.000

P

Volatility

5.081096393

2

aa

10/22/18 13:50 a

bb

10/22/18 13:50 a

OPTN

AAU

null

Almaden Minerals Ltd

3/15/19 0:00 a

0000002.500

C

Volatility

2.005687303

2

aa

10/22/18 13:50 a

bb

10/22/18 13:50 a

OPTN

AAU

null

Almaden Minerals Ltd

3/15/19 0:00 a

0000002.500

C

Volatility

2.005687303

2

aa

10/22/18 13:50 a

bb

10/22/18 13:50 a

OPTN

AAU

null

Almaden Minerals Ltd

3/15/19 0:00 a

0000002.500

P

Volatility

3.289548393

2

aa

10/22/18 13:50 a

bb

10/22/18 13:50 a

OPTN

AAU

null

Almaden Minerals Ltd

3/15/19 0:00 a

0000002.500

P

Volatility

3.289548393

2

aa

10/22/18 13:50 a

bb

10/22/18 13:50 a

OPTN

AAU

null

Almaden Minerals Ltd

3/15/19 0:00 a

0000005.000

C

Volatility

2.096531959

2

aa

10/22/18 13:50 a

bb

10/22/18 13:50 a

OPTN

AAU

null

Almaden Minerals Ltd

3/15/19 0:00 a

0000005.000

C

Volatility

2.096531959

2

aa

10/22/18 13:50 a

bb

10/22/18 13:50 a

OPTN

AAU

null

Almaden Minerals Ltd

3/15/19 0:00 a

0000005.000

P

Volatility

3.464064539

2

aa

10/22/18 13:50 a

bb

10/22/18 13:50 a

OPTN

AAU

null

Almaden Minerals Ltd

3/15/19 0:00 a

0000005.000

P

Volatility

3.464064539

2

aa

10/22/18 13:50 a

bb

10/22/18 13:50 a

OPTN

AAU

null

Almaden Minerals Ltd

6/21/19 0:00 a

0000002.500

C

Volatility

0

2

aa

10/22/18 13:50 a

bb

10/22/18 13:50 a

OPTN

AAU

null

Almaden Minerals Ltd

6/21/19 0:00 a

0000002.500

C

Volatility

0.0001

2

aa

10/22/18 13:50 a

bb

10/22/18 13:50 a

OPTN

AAU

null

Almaden Minerals Ltd

6/21/19 0:00 a

0000002.500

P

Volatility

3.190014219

2

aa

10/22/18 13:50 a

bb

10/22/18 13:50 a

OPTN

AAU

null

Almaden Minerals Ltd

6/21/19 0:00 a

0000002.500

P

Volatility

3.190014219

2

aa

10/22/18 13:50 a

bb

10/22/18 13:50 a

OPTN

AAU

null

Almaden Minerals Ltd

6/21/19 0:00 a

0000005.000

C

Volatility

0

2

aa

10/22/18 13:50 a

bb

10/22/18 13:50 a

OPTN

AAU

null

Almaden Minerals Ltd

6/21/19 0:00 a

0000005.000

C

Volatility

0.0001

2

aa

10/22/18 13:50 a

bb

10/22/18 13:50 a

OPTN

AAU

null

Almaden Minerals Ltd

6/21/19 0:00 a

0000005.000

P

Volatility

3.603095684

2

aa

10/22/18 13:50 a

bb

10/22/18 13:50 a

OPTN

AAU

null

Almaden Minerals Ltd

6/21/19 0:00 a

0000005.000

P

Volatility

3.603095684

2

aa

10/22/18 13:50 a

bb

10/22/18 13:50 a

OPTN

ABDC

null

Alcentra Capital Corporation

10/19/18 0:00 a

0000002.500

C

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

10/19/18 0:00 a

0000002.500

C

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

10/19/18 0:00 a

0000002.500

P

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

10/19/18 0:00 a

0000002.500

P

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

10/19/18 0:00 a

0000005.000

C

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

10/19/18 0:00 a

0000005.000

C

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

10/19/18 0:00 a

0000005.000

P

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

10/19/18 0:00 a

0000005.000

P

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

10/19/18 0:00 a

0000007.500

C

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

10/19/18 0:00 a

0000007.500

C

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

10/19/18 0:00 a

0000007.500

P

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

10/19/18 0:00 a

0000007.500

P

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

10/19/18 0:00 a

0000010.000

C

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

10/19/18 0:00 a

0000010.000

C

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

10/19/18 0:00 a

0000010.000

P

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

10/19/18 0:00 a

0000010.000

P

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

10/19/18 0:00 a

0000012.500

C

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

10/19/18 0:00 a

0000012.500

C

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

10/19/18 0:00 a

0000012.500

P

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

10/19/18 0:00 a

0000012.500

P

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

11/16/18 0:00 a

0000002.500

C

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

11/16/18 0:00 a

0000002.500

C

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

11/16/18 0:00 a

0000002.500

P

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

11/16/18 0:00 a

0000002.500

P

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

11/16/18 0:00 a

0000005.000

C

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

11/16/18 0:00 a

0000005.000

C

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

11/16/18 0:00 a

0000005.000

P

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

11/16/18 0:00 a

0000005.000

P

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

11/16/18 0:00 a

0000007.500

C

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

11/16/18 0:00 a

0000007.500

C

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

11/16/18 0:00 a

0000007.500

P

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

11/16/18 0:00 a

0000007.500

P

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

11/16/18 0:00 a

0000010.000

C

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

11/16/18 0:00 a

0000010.000

C

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

11/16/18 0:00 a

0000010.000

P

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

OPTN

ABDC

null

Alcentra Capital Corporation

11/16/18 0:00 a

0000010.000

P

Volatility

0.5

0.45

aa

9/24/18 16:04 a

bb

9/24/18 16:05 a

<tbody>
</tbody>

 

[Leith Ross]

Hello blu817,

Thanks for the sample data. There are couple of issues I found with the dates. When filtering dates there is also a time associated with the date. If you want yesterday's date, you cannot simply subtract 1. If the date is on Monday then the previous day would be Sunday. Unless you are working on Sunday, this probably not the approach you want. Both of these issue can be fixed in the macro. Is the work week Monday through Friday or something else?

 

[blu817]

You are absolutely right and my oversight. And in the world of macros the extra HH:MM makes a huge difference. The data will strictly be Monday - Friday but I plan on using it to dig through months of data on different spreadsheets from various days.