Filter a Date column using VBA

holograful

New member
Joined
Feb 6, 2020
Messages
1
Reaction score
0
Points
0
Excel Version(s)
Excel 2016
Hi,

I need to filter a Date column using VBA code and a text box.
The user will type in the text box and the data will filter.

Below is the code I am using to filter columns that contain text data, and it works as intended:
Code:
Private Sub textbox_PanelDate_Change()
    Dim S As String
    S = textbox_PanelDate
    If Len(S) > 0 Then
        Range("B6").AutoFilter Field:=18, Criteria1:="*" & S & "*"
    Else
        Range("B6").AutoFilter Field:=18
    End If
End Sub

What is the code to use to filter a column that contain date data?

The column is formatted as short-date. See the uploaded images for reference.
When I type in the Panel text box the spreadsheet is filtering (Panel filter.jpg).
I expect the same result when I type in the Panel Date text box - but the result is blank cells only (Panel Date No filter.jpg).
I use the above code for both text boxes.

Let me know if you need more info.


Thank you.

Filter a Date column using VBA
 

Attachments

  • Panel filter.jpg
    Panel filter.jpg
    28.9 KB · Views: 9
  • Panel Date No filter.jpg
    Panel Date No filter.jpg
    14.3 KB · Views: 10
This is always a pain. Try:
Code:
  If Len(s) > 0 Then
    t = CLng(DateValue(s))
    'Range("B6").AutoFilter Field:=18, Criteria1:=t 'used to work.
    Range("B6").AutoFilter Field:=18, Criteria1:=">=" & t, Operator:=xlAnd, Criteria2:="<=" & t
  Else
    Range("B6").AutoFilter Field:=18
  End If
 
Last edited:
oh groan…
cross posted without links:
Filter a Date column using VBA | MrExcel Message Board
https://www.mrexcel.com/board/threads/filter-a-date-column-using-vba.1123285/
Filter a Date column using VBA - Excel VBA / Macros - OzGrid Free Excel/VBA Help Forum
https://www.ozgrid.com/forum/index.php?thread/1226640-filter-a-date-column-using-vba/
Filter a Date column using VBA
https://www.excelforum.com/excel-pr...lter-a-date-column-using-vba.html#post5273824

holograful, for your information, you should always provide links to your cross posts.
This is a requirement, not just a request.
If you have cross posted at other places, please add links to them too.
Why? Have a read of http://www.excelguru.ca/content.php?184
 
Back
Top