Help on my filter please

Eric Penfold

Active Member
Joined
Nov 19, 2021
Messages
250
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Please could you help as to why the filter will not work in this code. Need yesterdays & todays filtered.


VBA Code:
Sub FindValues()

    Dim ws           As Worksheet
    Dim Rng          As Range
    Dim Lrow         As Long, Yesterday As Long, ToDay As Long
    Dim Comparerng   As Variant, x As Variant, y As Variant, i As Variant
    Dim Sourcerng    As Range
    Dim YDate        As Date
    Dim TDate        As Date
    
    On Error Resume Next
    Set ws = ActiveSheet
    Lrow = ws.Range("A2").End(xlDown).Row
    Set Rng = ws.Range("A2:J" & Lrow)
    
        ToDay = (DateSerial(Year(Date), Month(Date), Day(Date)))
        TDate = ToDay
        TDate = Format(TDate, "mm/dd/yyyy")
        
        Yesterday = (DateSerial(Year(Date), Month(Date), Day(Date - 1)))
        YDate = Yesterday
        YDate = Format(YDate, "mm/dd/yyyy")
        
        Rng.AutoFilter Field:=1, _
        Criteria1:=">" & YDate, _
        Operator:=xlAnd, _
        Criteria2:="<" & TDate

       End Sub
 

Eric Penfold

Active Member
Joined
Nov 19, 2021
Messages
250
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Thanks Eric. Try this now: (EDITED)

VBA Code:
Sub Filter_2_days_v2()
    With ActiveSheet.Range("A1", Cells(Rows.Count, "A").End(xlUp))
        .AutoFilter 1, Format(Date, "dd/mm/yyyy"), 2, Format(Date - 1, "dd/mm/yyyy"), 0
    End With
End Sub
Thanks Kevin but it just shows today not today & yesterday??
 
Upvote 0

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

kevin9999

Well-known Member
Joined
Aug 28, 2020
Messages
1,447
Office Version
  1. 365
Platform
  1. Windows
Thanks Kevin but it just shows today not today & yesterday??
When I test it on a small dummy data set, it shows both.

Before
filter.xlsm
ABCDEFGHIJ
1header1header2header3header4header5header6header7header8header9header10
210/06/2022datadatadatadatadatadatadatadatadata
317/06/2022datadatadatadatadatadatadatadatadata
411/06/2022datadatadatadatadatadatadatadatadata
501/06/2022datadatadatadatadatadatadatadatadata
611/06/2022datadatadatadatadatadatadatadatadata
710/06/2022datadatadatadatadatadatadatadatadata
816/06/2022datadatadatadatadatadatadatadatadata
916/06/2022datadatadatadatadatadatadatadatadata
1015/06/2022datadatadatadatadatadatadatadatadata
1115/06/2022datadatadatadatadatadatadatadatadata
1217/06/2022datadatadatadatadatadatadatadatadata
13
Sheet1


After
filter.xlsm
ABCDEFGHIJ
1header1header2header3header4header5header6header7header8header9header10
317/06/2022datadatadatadatadatadatadatadatadata
816/06/2022datadatadatadatadatadatadatadatadata
916/06/2022datadatadatadatadatadatadatadatadata
1217/06/2022datadatadatadatadatadatadatadatadata
13
Sheet1


Is there any chance you could provide a sample using the XL2BB Tool?
 
Upvote 0

Eric Penfold

Active Member
Joined
Nov 19, 2021
Messages
250
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
2022 Coventry Back Order.xlsm
ABCDEFGHIJKLMN
1Order DateCustomer NameOrder No.Ent ByCodeDescriptionBO Qty.Intact Stock LevelsNotesBack Order Reason CodeSupplier NameDepotBack Order Release DateDays to Fulfil
201/06/2022T Vaughan Ltd SO0061658NIJVFFSC175150-175mm Flexible Coupling VSC175 105LSVIP001C-31938
301/06/2022Hannon Brothers Ltd SO0061620NIJSF27/10080g Non-Woven GP Geotextile 4.5 x 100m Roll 125ONIDON001C-31938
401/06/2022Oliver Curran Construction Ltd SO0061597NIJ4VGROBottle Gully Raising Piece x225mm High Orange 60LSPLA002C-31938
501/06/2022JD Excavations Tyrone Ltd SO0061618NIJ450VCRA450mm Inspection Chamber Shaft 300mm Height includes seal (Adoptable SFA7) Brown 240ONIBRE002C-31938
601/06/2022M&T Midlands Ltd SO0061365NIJ375TWCN375mm Twinwall Pipe Coupler 10ONINAY001C-31938
701/06/2022Elmcroft Construction Ltd SO0061655NIJ225R150225/150mm ID (172mm) D/Socket Naylor TW Drain Reducer 113LSNAY001C-31938
801/06/2022LF Solutions Ltd SO0061265NIJ10VF90250mm S/Socket Bend 87.5deg 20LSRED002C-31938
901/06/2022LF Solutions Ltd SO0061265NIJ10VF45250mm S/Socket Bend 45deg 10LSRED002C-31938
1001/06/2022LF Solutions Ltd SO0061265NIJ10VF15250mm S/Socket Bend 15deg 50LSRED002C-31938
1101/06/2022Hannon Brothers Ltd SO0061616NIJPOLYTPlease note above duct is standard twinwall electric duct. Not compliant with ENATS 12-24 or EDF/SSE requirements.10ONINONEC-31938
1201/06/2022Hannon Brothers Ltd SO0061616NIJ110RTWBL110mm OD Twinwall Electrical Duct x6m; Black c/w Coupler 1518NSINAY001C-31938
1301/06/2022West Point Construction LimitedSO0061485NIJ100PGD50Perforated Gas Duct: 100mm x 50m Coil; Yellow 50LSNAY001C-31938
1401/06/2022Southern Mastic Roofing LtdSO0061437NIJ33.0304Frost Inverted Roof drain assembly - fixed dome 220mm and 150mm gravel guard, medium sump body with clamp, vertical threaded outlet 4" BSP110NSINONEC-31938
1502/06/2022Gtek Plant Ltd SO0061251NG 450TDBLN450mm BBA Twinwall Plain Ended Pipe x6m 60ONINAY001C-31939
1602/06/2022Carey London Ltd SO0058403LK 31.6517Frost Spigot Adaptor BSP 3 to 82mm plastic 170ONIJIM001C-31939
1702/06/2022O'Halloran & O'Brien Ltd SO0052299SM 31.6002Frost Extension cast iron 140mm NPSM4 270ONIHEB001C-31939
1802/06/2022O'Halloran & O'Brien Ltd SO0052299SM 31.1071Frost Body S trap cast iron vertical outlet 100mm DIA supplied with 3 x 2" BSP plugged inlets 10LSHEB001C-31939
1902/06/2022Slane Construction Ltd SO0052069LK 31.0275.SFrost Floor drain 178mm circular stainless steel grating for vinyl floors with medium sump -'S' trap - spigot outlet size 100mm 10LSFRO001C-31939
2006/06/2022Norcon Construction Ltd SO0061805NIJ63TWOS 63mm OD Twinwall Duct x50m; Street Lighting Orange 20ONI-31941
2106/06/2022Flatbase Construction Services LtdSO0061545NIJ63PTNE90 63mm Puriton Pipe Elbow 90deg; Type A 30LS-31941
2206/06/2022Flatbase Construction Services LtdSO0061545NIJ63PTN50 Puriton Barrier Pipe: 63mm x 50m Coil; Type A SDR11 20LS-31941
2306/06/2022RM Contractors Ltd SO0061733NIJ32PD100 32/37mm Polyethylene Duct x100m Coil; Black Printed Electric 10ONI-31941
2406/06/2022A D Bly Construction Ltd SO0060761NG 225R160 225mm Naylor TW Socket to 160mm UPVC Socket Level Invert Reducer 3-1ONI-31941
2506/06/2022J Holland Construction Ltd SO0061635NIJ110TWBL 110mm OD Twinwall Electrical Duct x50m; Black 10ONI-31941
2606/06/2022Norcon Construction Ltd SO0061726NIJ100PGD50 Perforated Gas Duct: 100mm x 50m Coil; Yellow 30ONI-31941
2707/06/2022Goldmax Ltd SO0061716LK PB160 158-170mm Zinc Plated Pipe Bracket Rubber Lining - M8/M10 Thread 1000LS-31942
2807/06/2022Agetur UK Ltd SO0061735NIJ63TWBL 63mm OD Twinwall Duct x50m; Black 40ONI-31942
2907/06/2022ALBY Associates Ltd SO0061729LK 160TWBL25160mm OD Twinwall Electrical Duct x25m Coil Black (140mm ID) 40ONI-31942
3007/06/2022M&T Midlands Ltd SO0061845NIJ300RTWBLN300mm ID Twinwall Duct x6m; Black; P/E 10NSI-31942
3107/06/2022M&T Midlands Ltd SO0061845NIJ110TWBL 110mm OD Twinwall Electrical Duct x50m; Black 40ONI-31942
3207/06/2022McCoy Contractors Ltd SO0061917NIJ110TW90 110mm OD Twinwall Duct Bend Long Radius 90deg; P/E 90ONI-31942
3307/06/2022J Holland Construction LtdSO0061911NIJ110TW90 110mm OD Twinwall Duct Bend Long Radius 90deg; P/E 20ONI-31942
3408/06/2022Gavin Jones Limited SO0061331LK DUCT45 Naylor 450x450mm Duct Access Box 335mm Depth B125 Loading 30ONI-31943
3508/06/2022Barry Howard Homes Ltd SO0061739NIJ375TWSN 375mm Twinwall Drain Seal 865ONI-31943
3608/06/2022Barry Howard Homes Ltd SO0061739NIJ375TWCN 375mm Twinwall Pipe Coupler 20ONI-31943
3708/06/2022Barry Howard Homes Ltd SO0061739NIJ375TDBLN 375mm BBA Twinwall Plain Ended Pipe x6m 10ONI-31943
3808/06/2022LNT Construction Ltd SO0061862NIJ160TWBL 160mm OD TW Electrical Duct x50m Coil Black (140mm ID) 40ONI-31943
3909/06/2022Hannon Brothers Ltd SO0061553NIJDUCT60 Naylor 600x600mm Duct Access Box 335mm Depth B125 Loading 50ONI-31944
4009/06/2022Hannon Brothers Ltd SO0062158NIJDUCT60 Naylor 600x600mm Duct Access Box 335mm Depth B125 Loading 40ONI-31944
4109/06/2022Barry Howard Homes Ltd SO0062123NIJ60PGD50Perforated Gas Duct: 60mm x 50m Coil; Yellow 20ONI-31944
4209/06/2022Goldmax Ltd SO0062234LK 4VF20D 110mm PVC Drainage D/Socket Coupler 25233-31944
4309/06/2022Prestige Civil (Westbury) Engineering LtdSO0062139NIJ4VF20D 110mm PVC Drainage D/Socket Coupler 30233-31944
4409/06/2022Fenton Civil Engineering Ltd SO0062174LK 4VF20D 110mm PVC Drainage D/Socket Coupler 200233-31944
4509/06/2022Prestige Civil (Westbury) Engineering LtdSO0062110NIJ4VF20D 110mm PVC Drainage D/Socket Coupler 20233-31944
4609/06/2022Stave-Con Ltd SO0062178NIJ450VCRA450mm Inspection Chamber Shaft 300mm Height includes seal (Adoptable SFA7) Brown 400ONI-31944
4709/06/2022McCoy Contractors Ltd SO0062153NG 150TW30150mm ID/ 178mm OD Twinwall Duct Bend 30deg Long Radius P/E 40LS-31944
4810/06/2022Foran Construction Ltd SO0062190NIJ4VF20D 110mm PVC Drainage D/Socket Coupler 20233ONI-31945
4916/06/20224VF20D ONI-31949
5017/06/2022
Jun
Cell Formulas
RangeFormula
N2:N49N2=NETWORKDAYS(Jun!$A2,Jun!$M2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C20:C30Cell ValueduplicatestextNO
E20:E30Cell ValueduplicatestextNO
C14:C19Cell ValueduplicatestextNO
E14:E19Cell ValueduplicatestextNO
C2:C19Cell ValueduplicatestextNO
C58:C1048576,C31:C48,C1:C19Cell ValueduplicatestextNO
C58:C1048576,C31:C48,C1:C19Cell ValueduplicatestextNO
C58:C1048576,C31:C48,C1Cell ValueduplicatestextNO
E1:E19,E58:E1048576,E31:E49Cell ValueduplicatestextNO
 
Upvote 0

kevin9999

Well-known Member
Joined
Aug 28, 2020
Messages
1,447
Office Version
  1. 365
Platform
  1. Windows
Thanks Eric. OK, when I run this code:

VBA Code:
Sub Filter_2_days_v3()
    With ActiveSheet.Range("A1", Cells(Rows.Count, "A").End(xlUp))
        .AutoFilter 1, Format(Date, "dd/mm/yyyy"), 2, Format(Date - 1, "dd/mm/yyyy")
    End With
End Sub

I get this:
Before
filter.xlsm
ABCDEFGHIJKLMN
1Order DateCustomer NameOrder No.Ent ByCodeDescriptionBO Qty.Intact Stock LevelsNotesBack Order Reason CodeSupplier NameDepotBack Order Release DateDays to Fulfil
21/06/2022T Vaughan Ltd SO0061658NIJVFFSC175150-175mm Flexible Coupling VSC175 105LSVIP001C
31/06/2022Hannon Brothers Ltd SO0061620NIJSF27/10080g Non-Woven GP Geotextile 4.5 x 100m Roll 125ONIDON001C
41/06/2022Oliver Curran Construction Ltd SO0061597NIJ4VGROBottle Gully Raising Piece x225mm High Orange 60LSPLA002C
51/06/2022JD Excavations Tyrone Ltd SO0061618NIJ450VCRA450mm Inspection Chamber Shaft 300mm Height includes seal (Adoptable SFA7) Brown 240ONIBRE002C
61/06/2022M&T Midlands Ltd SO0061365NIJ375TWCN375mm Twinwall Pipe Coupler 10ONINAY001C
71/06/2022Elmcroft Construction Ltd SO0061655NIJ225R150225/150mm ID (172mm) D/Socket Naylor TW Drain Reducer 113LSNAY001C
81/06/2022LF Solutions Ltd SO0061265NIJ10VF90250mm S/Socket Bend 87.5deg 20LSRED002C
91/06/2022LF Solutions Ltd SO0061265NIJ10VF45250mm S/Socket Bend 45deg 10LSRED002C
101/06/2022LF Solutions Ltd SO0061265NIJ10VF15250mm S/Socket Bend 15deg 50LSRED002C
111/06/2022Hannon Brothers Ltd SO0061616NIJPOLYTPlease note above duct is standard twinwall electric duct. Not compliant with ENATS 12-24 or EDF/SSE requirements.10ONINONEC
121/06/2022Hannon Brothers Ltd SO0061616NIJ110RTWBL110mm OD Twinwall Electrical Duct x6m; Black c/w Coupler 1518NSINAY001C
131/06/2022West Point Construction LimitedSO0061485NIJ100PGD50Perforated Gas Duct: 100mm x 50m Coil; Yellow 50LSNAY001C
141/06/2022Southern Mastic Roofing LtdSO0061437NIJ33.0304Frost Inverted Roof drain assembly - fixed dome 220mm and 150mm gravel guard, medium sump body with clamp, vertical threaded outlet 4" BSP110NSINONEC
152/06/2022Gtek Plant Ltd SO0061251NG 450TDBLN450mm BBA Twinwall Plain Ended Pipe x6m 60ONINAY001C
162/06/2022Carey London Ltd SO0058403LK 31.6517Frost Spigot Adaptor BSP 3 to 82mm plastic 170ONIJIM001C
172/06/2022O'Halloran & O'Brien Ltd SO0052299SM 31.6002Frost Extension cast iron 140mm NPSM4 270ONIHEB001C
182/06/2022O'Halloran & O'Brien Ltd SO0052299SM 31.1071Frost Body S trap cast iron vertical outlet 100mm DIA supplied with 3 x 2" BSP plugged inlets 10LSHEB001C
192/06/2022Slane Construction Ltd SO0052069LK 31.0275.SFrost Floor drain 178mm circular stainless steel grating for vinyl floors with medium sump -'S' trap - spigot outlet size 100mm 10LSFRO001C
206/06/2022Norcon Construction Ltd SO0061805NIJ63TWOS 63mm OD Twinwall Duct x50m; Street Lighting Orange 20ONI
216/06/2022Flatbase Construction Services LtdSO0061545NIJ63PTNE90 63mm Puriton Pipe Elbow 90deg; Type A 30LS
226/06/2022Flatbase Construction Services LtdSO0061545NIJ63PTN50 Puriton Barrier Pipe: 63mm x 50m Coil; Type A SDR11 20LS
236/06/2022RM Contractors Ltd SO0061733NIJ32PD100 32/37mm Polyethylene Duct x100m Coil; Black Printed Electric 10ONI
246/06/2022A D Bly Construction Ltd SO0060761NG 225R160 225mm Naylor TW Socket to 160mm UPVC Socket Level Invert Reducer 3-1ONI
256/06/2022J Holland Construction Ltd SO0061635NIJ110TWBL 110mm OD Twinwall Electrical Duct x50m; Black 10ONI
266/06/2022Norcon Construction Ltd SO0061726NIJ100PGD50 Perforated Gas Duct: 100mm x 50m Coil; Yellow 30ONI
277/06/2022Goldmax Ltd SO0061716LK PB160 158-170mm Zinc Plated Pipe Bracket Rubber Lining - M8/M10 Thread 1000LS
287/06/2022Agetur UK Ltd SO0061735NIJ63TWBL 63mm OD Twinwall Duct x50m; Black 40ONI
297/06/2022ALBY Associates Ltd SO0061729LK 160TWBL25160mm OD Twinwall Electrical Duct x25m Coil Black (140mm ID) 40ONI
307/06/2022M&T Midlands Ltd SO0061845NIJ300RTWBLN300mm ID Twinwall Duct x6m; Black; P/E 10NSI
317/06/2022M&T Midlands Ltd SO0061845NIJ110TWBL 110mm OD Twinwall Electrical Duct x50m; Black 40ONI
327/06/2022McCoy Contractors Ltd SO0061917NIJ110TW90 110mm OD Twinwall Duct Bend Long Radius 90deg; P/E 90ONI
337/06/2022J Holland Construction LtdSO0061911NIJ110TW90 110mm OD Twinwall Duct Bend Long Radius 90deg; P/E 20ONI
348/06/2022Gavin Jones Limited SO0061331LK DUCT45 Naylor 450x450mm Duct Access Box 335mm Depth B125 Loading 30ONI
358/06/2022Barry Howard Homes Ltd SO0061739NIJ375TWSN 375mm Twinwall Drain Seal 865ONI
368/06/2022Barry Howard Homes Ltd SO0061739NIJ375TWCN 375mm Twinwall Pipe Coupler 20ONI
378/06/2022Barry Howard Homes Ltd SO0061739NIJ375TDBLN 375mm BBA Twinwall Plain Ended Pipe x6m 10ONI
388/06/2022LNT Construction Ltd SO0061862NIJ160TWBL 160mm OD TW Electrical Duct x50m Coil Black (140mm ID) 40ONI
399/06/2022Hannon Brothers Ltd SO0061553NIJDUCT60 Naylor 600x600mm Duct Access Box 335mm Depth B125 Loading 50ONI
409/06/2022Hannon Brothers Ltd SO0062158NIJDUCT60 Naylor 600x600mm Duct Access Box 335mm Depth B125 Loading 40ONI
419/06/2022Barry Howard Homes Ltd SO0062123NIJ60PGD50Perforated Gas Duct: 60mm x 50m Coil; Yellow 20ONI
429/06/2022Goldmax Ltd SO0062234LK 4VF20D 110mm PVC Drainage D/Socket Coupler 25233
439/06/2022Prestige Civil (Westbury) Engineering LtdSO0062139NIJ4VF20D 110mm PVC Drainage D/Socket Coupler 30233
449/06/2022Fenton Civil Engineering Ltd SO0062174LK 4VF20D 110mm PVC Drainage D/Socket Coupler 200233
459/06/2022Prestige Civil (Westbury) Engineering LtdSO0062110NIJ4VF20D 110mm PVC Drainage D/Socket Coupler 20233
469/06/2022Stave-Con Ltd SO0062178NIJ450VCRA450mm Inspection Chamber Shaft 300mm Height includes seal (Adoptable SFA7) Brown 400ONI
479/06/2022McCoy Contractors Ltd SO0062153NG 150TW30150mm ID/ 178mm OD Twinwall Duct Bend 30deg Long Radius P/E 40LS
4810/06/2022Foran Construction Ltd SO0062190NIJ4VF20D 110mm PVC Drainage D/Socket Coupler 20233ONI
4916/06/20224VF20D ONI
5017/06/2022
Sheet2


After
filter.xlsm
ABCDEFGHIJKLMN
1Order DateCustomer NameOrder No.Ent ByCodeDescriptionBO Qty.Intact Stock LevelsNotesBack Order Reason CodeSupplier NameDepotBack Order Release DateDays to Fulfil
4916/06/20224VF20D ONI
5017/06/2022
Sheet2


I'm getting both today (17th June) and yesterday (16th June)
 
Upvote 0
Solution

kevin9999

Well-known Member
Joined
Aug 28, 2020
Messages
1,447
Office Version
  1. 365
Platform
  1. Windows
UPDATE

My friend and fellow volunteer @Alex Blakenburg has pointed out a problem (thanks Alex) in the code so far provided – insofar as it will fail if the today/yesterday dates are single digit days. To overcome this, I’ve added a single line to the existing code to ensure the entire range is formatted to match the filter.

VBA Code:
Sub Filter_2_days_v4()
    With ActiveSheet.Range("A1", Cells(Rows.Count, "A").End(xlUp))
        .NumberFormat = "d/mm/yyyy"
        .AutoFilter 1, Format(Date, "d/mm/yyyy"), 2, Format(Date - 1, "d/mm/yyyy")
    End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,186,846
Messages
5,960,171
Members
438,464
Latest member
rangers277

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