Filter a Date column using VBA

[holograful]

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.

 

[alansidman]

Deleted. Wrong Answer

 

[MARK858]

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]

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]

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]

Depends if the text matches the exact format in the cell.

 

[holograful]

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]

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.

 

[holograful]

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.

 

[MARK858]

What are your Windows regional date settings? are your dates in dd-mm-yyyy format in your cells?