-
VBA Date Filtering Issue
I am using the below vba code to filter a spreadsheet on open to show the current month and all future dates and I can't for the life of me get it to show null values... where the date isn't populated. Any help would be appreciated!
Code:
Private Sub Workbook_Open()
Sheets("Sheet1").Select
'startdate as date
Dim startDate As Date
'startdate as long for excel
Dim lstartDate As Long
'get start date, day = always 1
startDate = DateSerial(Year(Now), Month(Now), 1)
lstartDate = startDate
'filter everything before current month
'you see this month + future
ActiveSheet.Range("A1").AutoFilter Field:=6, Criteria1:=">=" & lstartDate
End Sub
-
Welcome to the forums, please take a minute to look at the FAQ in my signature
I added CODE tags for you this time, you can use the [#] icon to insert them
I think you're looking for the Bold below. The macro recorder usually does a reasonablely good job of giving you a starting point for syntax, but always requires cleaning up
Code:
Private Sub Workbook_Open()
Sheets("Sheet1").Select
'startdate as date
Dim startDate As Date
'startdate as long for excel
Dim lstartDate As Long
'get start date, day = always 1
startDate = DateSerial(Year(Now), Month(Now), 1)
lstartDate = startDate
'filter everything before current month
'you see this month + future
ActiveSheet.Range("A1").AutoFilter Field:=6, Criteria1:=">=" & lstartDate, Operator:=xlOr, Criteria2:="="
End Sub
https://learn.microsoft.com/en-us/of...nge.autofilter
Parameters section has a good writeup
-
To add blank cells to the same column criteria, update your Autofilter line:
Code:
ActiveSheet.Range("A1").AutoFilter Field:=6, Criteria1:=">=" & lstartDate, Operator:=xlOr, Criteria2:="="
-
Thank you Paul and Jan
This works perfectly, thank you!