I'm trying to create a macro to filter a table so it only shows rows that have today's date as well as the blank rows.
I can easily get it to filter one or the other, but when I try to get it to show both, it just shows the blanks right now.
I have a sheet called "Daily Goal and Sale Tracker"
Within that sheet is a table called "Customer_Tally_Table"
Within that table is a column for "Date", which is column 1
Here is what I tried:
Sub FilterTableByCurrentDateAndBlanks()
Dim tbl As ListObject
Dim ws As Worksheet
Dim currentDate As Date
'set the worksheet and table variables
Set ws = ThisWorkbook.Sheets("Daily Goal and Sale Tracker")
Set tbl = ws.ListObjects("Customer_Tally_Table")
'set the current date variable
currentDate = Date
'filter the table to show only the current date and blanks in column 1
With tbl.Range
.AutoFilter Field:=1, Criteria1:="=" & Format(currentDate, "m/d/yyyy")
.AutoFilter Field:=1, Criteria1:=""
End With
End Sub
The above code did not work, but I figured maybe it was because the cells that have the date are also time-stamped, in the following format: 3/14/12 1:30PM
So, I tried to change it so it would only look at the date, and not get "Confused " by the time, and this is the code I tried:
Sub FilterTableByCurrentDateAndBlanks()
Dim tbl As ListObject
Dim ws As Worksheet
Dim currentDate As Date
'set the worksheet and table variables
Set ws = ThisWorkbook.Sheets("Daily Goal and Sale Tracker")
Set tbl = ws.ListObjects("Customer_Tally_Table")
'set the current date variable
currentDate = Date
'format the current date without time component
Dim formattedDate As String
formattedDate = Format(currentDate, "m/d/yyyy")
'filter the table to show only the current date and blanks in column 1
With tbl.Range
.AutoFilter Field:=1, Criteria1:="=" & formattedDate, Operator:=xlOr, Criteria2:="="
End With
End Sub
Both of these codes only showed me the blank rows, but I desperately need it to show both.
Any suggestions?
Thanks!
Bookmarks