Results 1 to 3 of 3

Thread: Filter a Date column using VBA

  1. #1
    Neophyte holograful's Avatar
    Join Date
    Feb 2020
    Posts
    1
    Articles
    0
    Excel Version
    Excel 2016

    Filter a Date column using VBA



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Panel filter.jpg 
Views:	7 
Size:	28.9 KB 
ID:	9623   Click image for larger version. 

Name:	Panel Date No filter.jpg 
Views:	6 
Size:	14.3 KB 
ID:	9624  

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,691
    Articles
    0
    Excel Version
    365
    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 by p45cal; 2020-02-16 at 09:46 AM.

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,691
    Articles
    0
    Excel Version
    365
    oh groan…
    cross posted without links:
    Filter a Date column using VBA | MrExcel Message Board
    https://www.mrexcel.com/board/thread...g-vba.1123285/
    Filter a Date column using VBA - Excel VBA / Macros - OzGrid Free Excel/VBA Help Forum
    https://www.ozgrid.com/forum/index.p...umn-using-vba/
    Filter a Date column using VBA
    https://www.excelforum.com/excel-pro...ml#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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •