How to autofilter by exact date?

LearnerExcel · #1 06-15-2018, 12:02 PM

How can I retrieve the data by filtering exact date by editing the following code?

.AutoFilter Field:=12, Criteria1:=">=" & STARTDATE

Example: If I enter 6/15/2018, it should show only the rows that contain the STARTDATE as 6/15/2018

NoSparks · #2 06-17-2018, 01:30 PM

What results do you get with the code you've posted ?
What results do you get if you use Criteria1:=STARTDATE
What is STARTDATE, a variable, named range or something else ?
Where are you entering 6/15/2018 ?
What's in Field 12, "true dates", text that looks like dates, dates and times ?
What's the rest of the code ?

Providing a sample sheet indicative of what you're working with would be beneficial to getting the required solution.

Logit · #3 06-17-2018, 09:58 PM

.
I know you said FILTER ... but here is a macro that will do the same as requested. Your
variable STARTDATE can refer to a specific cell ?

Code:

Option Explicit

Sub HideRowsContainingValue()
'Description: This macro will loop through a col and
'hide the row if the cell in Col A does not the value of A1.
    
Dim c As Range
Application.ScreenUpdating = False
    For Each c In Range("A2:A100").Cells
        If c.Value < Range("A1").Value Then
            c.EntireRow.Hidden = True
        End If
    Next c
Application.ScreenUpdating = True

End Sub

Sub UnhideAllColumns()
'This macro will unhide all the columns in the
'specified range.
Application.ScreenUpdating = False

    Range("A1:A100").EntireRow.Hidden = False

Application.ScreenUpdating = True
End Sub

Debaser · #4 06-18-2018, 03:46 AM

It is generally safer with dates and filters to convert to numeric values - for example:

Code:

.AutoFilter Field:=12, Criteria1:=">=" & CDbl(STARTDATE), operator:=xlAnd, criteria2:="<=" & CDbl(STARTDATE)

This assumes that STARTDATE is a Date type variable. If not, you can convert that using CDate like this:

Code:

.AutoFilter Field:=12, Criteria1:=">=" & CDbl(CDate(STARTDATE)), operator:=xlAnd, criteria2:="<=" & CDbl(CDate(STARTDATE))

LearnerExcel · #5 06-19-2018, 01:59 PM

Quote:

Originally Posted by Logit

.
I know you said FILTER ... but here is a macro that will do the same as requested. Your
variable STARTDATE can refer to a specific cell ?

Code:

Option Explicit

Sub HideRowsContainingValue()
'Description: This macro will loop through a col and
'hide the row if the cell in Col A does not the value of A1.
    
Dim c As Range
Application.ScreenUpdating = False
    For Each c In Range("A2:A100").Cells
        If c.Value < Range("A1").Value Then
            c.EntireRow.Hidden = True
        End If
    Next c
Application.ScreenUpdating = True

End Sub

Sub UnhideAllColumns()
'This macro will unhide all the columns in the
'specified range.
Application.ScreenUpdating = False

    Range("A1:A100").EntireRow.Hidden = False

Application.ScreenUpdating = True
End Sub

Thanks for the code. But What I want is the result that is highlighted in YELLOW in the attached file. Not the ones which is strikethrough in red color font.

Logit · #6 06-19-2018, 03:30 PM

Code:

Option Explicit

Sub HideRowsContainingValue()
'Description: This macro will loop through a col and
'hide the row if the cell in Col A does not the value of A1.
    
Dim c As Range
Application.ScreenUpdating = False
    For Each c In Range("A2:A100").Cells
        If c.Value <> Range("A1").Value Then
            c.EntireRow.Hidden = True
        End If
    Next c
Application.ScreenUpdating = True

End Sub

The above macro will accomplish what you want in the workbook I provided.

Your original post indicated you wanted to view anything EQUAL TO or GREATER than the
STARTDATE, which is why the first macro was written as it was.

HTH

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to write if condition for AutoFilter to check ..?	LearnerExcel	Excel Programming	1	05-05-2018 08:53 AM
Autofilter on two Date Columns	OTPM	Excel Programming	3	04-29-2014 12:56 AM
How to count even exact date 90 days	redza	Excel	1	09-17-2013 03:36 AM
Sum on AutoFilter	Grasshopper	Excel	3	02-14-2013 01:41 AM
AutoFilter Criteria3	coxjamd	Excel Programming	2	01-17-2013 02:24 PM