had1015
10-03-2016, 04:27 PM
Hi,
I would like to get assistance in creating a macro which would filter my data based on dates listed in a column. I have around 15,000 records and I'm trying to have a macro which would loop through dates listed in one column. For instance column Z has a list of weekly dates (Mondays) developed from the earliest and latest dates listed from column Y. I want the macro to look at the first date starting in cell Z4 and filter using column Y as the field for all items equal to or greater than Z4 and less than Z4 plus 7. This gives me a one week window. I'd like the count of visible cells to be written to cell AA4. This should run continuously until the last date has completed in column Z. My data ranges from a1 thru column z with variable numbers and rows and with headers in row one. I have tried using this macro I found on the internet:
Sub FilterByDateTime()
Dim dDate As Date Dim dbDate As Double
If IsDate(Range("z4")) Then
dbDate = Range("z4")
dbDate = DateSerial(Year(dbDate), Month(dbDate), Day(dbDate))
Range("A1").AutoFilter Range("A1").AutoFilter Field:=1, Criteria1:=">=" & dbDate,Operator:=xland criteria1:="<" & dbDate + 7
End If
End Sub
Any help that you can provide would be greatly appreciated.
I would like to get assistance in creating a macro which would filter my data based on dates listed in a column. I have around 15,000 records and I'm trying to have a macro which would loop through dates listed in one column. For instance column Z has a list of weekly dates (Mondays) developed from the earliest and latest dates listed from column Y. I want the macro to look at the first date starting in cell Z4 and filter using column Y as the field for all items equal to or greater than Z4 and less than Z4 plus 7. This gives me a one week window. I'd like the count of visible cells to be written to cell AA4. This should run continuously until the last date has completed in column Z. My data ranges from a1 thru column z with variable numbers and rows and with headers in row one. I have tried using this macro I found on the internet:
Sub FilterByDateTime()
Dim dDate As Date Dim dbDate As Double
If IsDate(Range("z4")) Then
dbDate = Range("z4")
dbDate = DateSerial(Year(dbDate), Month(dbDate), Day(dbDate))
Range("A1").AutoFilter Range("A1").AutoFilter Field:=1, Criteria1:=">=" & dbDate,Operator:=xland criteria1:="<" & dbDate + 7
End If
End Sub
Any help that you can provide would be greatly appreciated.