VBA Date Filtering Issue

[telobamipada]

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!


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

[Paul_Hossler]

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



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/office/vba/api/excel.range.autofilter

Parameters section has a good writeup

[Jan Karel Pieterse]

To add blank cells to the same column criteria, update your Autofilter line:

ActiveSheet.Range("A1").AutoFilter Field:=6, Criteria1:=">=" & lstartDate, Operator:=xlOr, Criteria2:="="

[telobamipada]

This works perfectly, thank you!