Filter a Date column using VBA

holograful

New Member
Joined
Apr 19, 2017
Messages
20
Hi,

I need to filter a Date column using VBA code and 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:
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?

Thank you.
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,053
Office Version
365, 2010
Platform
Windows, Mobile
What is the code to use to filter a column that contain date data?
Depends exactly what you mean. Do you mean a single date or a range of dates? if a range of dates what is the criteria that you are filtering by.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,098
Office Version
365
Platform
Windows
Cross-posted here: Filter a Date column using VBA - OzGrid Free Excel/VBA Help Forum
and here: Filter a Date column using VBA

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 

holograful

New Member
Joined
Apr 19, 2017
Messages
20
Hi Mark858,

The user will type a single date in the text box.
The code that I posted works with columns that have text data type, but not with columns that have date data type.
My guess is that the Criteria in the code needs to be somehow different.

Thank you.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,053
Office Version
365, 2010
Platform
Windows, Mobile
Depends if the text matches the exact format in the cell.
 

holograful

New Member
Joined
Apr 19, 2017
Messages
20
By using the code above, in a text data type column, you can type any character in the text box, and if it is in any of the cells in that column, it will filter the column. This is what I expect to happen in a date data type column.

But, if the user needs to follow a certain format for filtering a date column (e.g. the user needs to type in the text box "Feb 05, 20"), this can be "enforced" by adding a comment, data validation, etc.

Is this what you are looking for?

Thanks.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,053
Office Version
365, 2010
Platform
Windows, Mobile
Actually they need to type in the format of whatever is their default Windows regional setting (normally the short date setting) if it is an actual date column (also the column width has to display the full date), if it not in this format then we are looking to use CDate for a single date.
 
Last edited:

holograful

New Member
Joined
Apr 19, 2017
Messages
20
Hi Mark,

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. Thanks.
 

Attachments

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,053
Office Version
365, 2010
Platform
Windows, Mobile
What are your Windows regional date settings? are your dates in dd-mm-yyyy format in your cells?
 

Forum statistics

Threads
1,089,518
Messages
5,408,755
Members
403,224
Latest member
rholmesa

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top