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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,141
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Deleted. Wrong Answer
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,966
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. 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
56,502
Office Version
  1. 365
Platform
  1. 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

ADVERTISEMENT

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
13,966
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Depends if the text matches the exact format in the cell.
 

holograful

New Member
Joined
Apr 19, 2017
Messages
20

ADVERTISEMENT

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
13,966
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. 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

  • Panel Date No filter.jpg
    Panel Date No filter.jpg
    14.3 KB · Views: 1
  • Panel filter.jpg
    Panel filter.jpg
    28.9 KB · Views: 1

MARK858

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

Watch MrExcel Video

Forum statistics

Threads
1,127,040
Messages
5,622,343
Members
415,894
Latest member
silverhaze

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