Filter between 2 date ranges

Multi tool use
I would like to filter 1 lot of data between 2 date ranges.
Date range 1: Going back from yesterday's date to 28 days previous.
Date range 2: Going back one year from yesterday's date to 28 days previous.
To filter to one date range I have this code working:
Sub DateFilter()
Dim StartDateTY As Date
Dim EndDateTY As Date
StartDateTY = Date - 29
EndDateTY = Date - 1
Sheets("Main").Range("$A$4:$O$5000").AutoFilter Field:=2, _
Criteria1:=">=" & CDbl(StartDateTY), Operator:=xlAnd, _
Criteria2:="<=" & CDbl(EndDateTY)
End Sub
I was thinking I could use arrays to use 2 different date ranges but can't get it working.
Sub DateFilter2Ranges()
Dim StartDateTY As Date
Dim EndDateTY As Date
StartDateTY = Date - 29
EndDateTY = Date - 1
Dim StartDateLY As Date
Dim EndDateLY As Date
StartDateLY = Date - 29 - 365
EndDateLY = Date - 1 - 365
Sheets("Main").Range("$A$4:$O$5000").AutoFilter Field:=2, _
Criteria1:=Array(0, ">=" & CDbl(StartDateTY), 0, ">=" & CDbl(StartDateLY)), Operator:=xlAnd, _
Criteria2:=Array(1, "<=" & CDbl(EndDateTY), 1, "<=" & CDbl(EndDateLY))
End Sub
I also thought I could mix 'And' & 'Or' operators if this is possible.
excel vba filtering
add a comment |
I would like to filter 1 lot of data between 2 date ranges.
Date range 1: Going back from yesterday's date to 28 days previous.
Date range 2: Going back one year from yesterday's date to 28 days previous.
To filter to one date range I have this code working:
Sub DateFilter()
Dim StartDateTY As Date
Dim EndDateTY As Date
StartDateTY = Date - 29
EndDateTY = Date - 1
Sheets("Main").Range("$A$4:$O$5000").AutoFilter Field:=2, _
Criteria1:=">=" & CDbl(StartDateTY), Operator:=xlAnd, _
Criteria2:="<=" & CDbl(EndDateTY)
End Sub
I was thinking I could use arrays to use 2 different date ranges but can't get it working.
Sub DateFilter2Ranges()
Dim StartDateTY As Date
Dim EndDateTY As Date
StartDateTY = Date - 29
EndDateTY = Date - 1
Dim StartDateLY As Date
Dim EndDateLY As Date
StartDateLY = Date - 29 - 365
EndDateLY = Date - 1 - 365
Sheets("Main").Range("$A$4:$O$5000").AutoFilter Field:=2, _
Criteria1:=Array(0, ">=" & CDbl(StartDateTY), 0, ">=" & CDbl(StartDateLY)), Operator:=xlAnd, _
Criteria2:=Array(1, "<=" & CDbl(EndDateTY), 1, "<=" & CDbl(EndDateLY))
End Sub
I also thought I could mix 'And' & 'Or' operators if this is possible.
excel vba filtering
add a comment |
I would like to filter 1 lot of data between 2 date ranges.
Date range 1: Going back from yesterday's date to 28 days previous.
Date range 2: Going back one year from yesterday's date to 28 days previous.
To filter to one date range I have this code working:
Sub DateFilter()
Dim StartDateTY As Date
Dim EndDateTY As Date
StartDateTY = Date - 29
EndDateTY = Date - 1
Sheets("Main").Range("$A$4:$O$5000").AutoFilter Field:=2, _
Criteria1:=">=" & CDbl(StartDateTY), Operator:=xlAnd, _
Criteria2:="<=" & CDbl(EndDateTY)
End Sub
I was thinking I could use arrays to use 2 different date ranges but can't get it working.
Sub DateFilter2Ranges()
Dim StartDateTY As Date
Dim EndDateTY As Date
StartDateTY = Date - 29
EndDateTY = Date - 1
Dim StartDateLY As Date
Dim EndDateLY As Date
StartDateLY = Date - 29 - 365
EndDateLY = Date - 1 - 365
Sheets("Main").Range("$A$4:$O$5000").AutoFilter Field:=2, _
Criteria1:=Array(0, ">=" & CDbl(StartDateTY), 0, ">=" & CDbl(StartDateLY)), Operator:=xlAnd, _
Criteria2:=Array(1, "<=" & CDbl(EndDateTY), 1, "<=" & CDbl(EndDateLY))
End Sub
I also thought I could mix 'And' & 'Or' operators if this is possible.
excel vba filtering
I would like to filter 1 lot of data between 2 date ranges.
Date range 1: Going back from yesterday's date to 28 days previous.
Date range 2: Going back one year from yesterday's date to 28 days previous.
To filter to one date range I have this code working:
Sub DateFilter()
Dim StartDateTY As Date
Dim EndDateTY As Date
StartDateTY = Date - 29
EndDateTY = Date - 1
Sheets("Main").Range("$A$4:$O$5000").AutoFilter Field:=2, _
Criteria1:=">=" & CDbl(StartDateTY), Operator:=xlAnd, _
Criteria2:="<=" & CDbl(EndDateTY)
End Sub
I was thinking I could use arrays to use 2 different date ranges but can't get it working.
Sub DateFilter2Ranges()
Dim StartDateTY As Date
Dim EndDateTY As Date
StartDateTY = Date - 29
EndDateTY = Date - 1
Dim StartDateLY As Date
Dim EndDateLY As Date
StartDateLY = Date - 29 - 365
EndDateLY = Date - 1 - 365
Sheets("Main").Range("$A$4:$O$5000").AutoFilter Field:=2, _
Criteria1:=Array(0, ">=" & CDbl(StartDateTY), 0, ">=" & CDbl(StartDateLY)), Operator:=xlAnd, _
Criteria2:=Array(1, "<=" & CDbl(EndDateTY), 1, "<=" & CDbl(EndDateLY))
End Sub
I also thought I could mix 'And' & 'Or' operators if this is possible.
excel vba filtering
excel vba filtering
edited Jan 5 at 18:43
Community♦
11
11
asked Jan 2 at 11:32


Andrew WhittyAndrew Whitty
12
12
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
In order to work with dates in autofilter, you need to know one thing - autofilter recognizes only U.S. date format: month/day/year.
Sub FFF()
Dim dt1 As Date, dt2 As Date
Dim sDate1$, sDate2
'// Get dates
dt1 = Now - 29: dt2 = Now - 1
'// Format dates
sDate1 = Format(dt1, "MM/yy/yyyy")
sDate2 = Format(dt2, "MM/yy/yyyy")
With Sheets("Main").Range("A1").CurrentRegion
.AutoFilter
.AutoFilter Field:=2, Criteria1:=">=" & sDate1, Operator:=xlAnd, Criteria2:="<=" & sDate2
End With
End Sub
add a comment |
Simple code to filter the dates for your ranges, modify to meet your needs...
With ThisWorkbook.Sheets("Main").Cells(1, 1).CurrentRegion
.AutoFilter
.AutoFilter Field:=2, Criteria1:=">=" & (Now - 29), Operator:=xlAnd, Criteria2:="<=" & (Now - 1)
'Do something
.AutoFilter Field:=2, Criteria1:=">=" & (Now - 394), Operator:=xlAnd, Criteria2:="<=" & (Now - 365)
'Do something
End With
1
1)Range
doesn't haveAutoFilterMode
property. 2) Autofilter recognizes only U.S. date format. Did you test your code?
– JohnyL
Jan 5 at 19:33
@JohnyL, Thank you for the imput, but not for the markdown. Yes i did test my code and then added theAutoFilterMode
to the answer while in SO, my bad. The second part, i don't see anything that in the OPs code that identifies dates are not U.S. date format. Also, your answer is noot complete, since the primary question what how to filter for the privious 28 days and also filter the same 28 days from the previous year. But i won't down vote you.+1 for catching my error.
– GMalc
Jan 6 at 19:20
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54005579%2ffilter-between-2-date-ranges%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
In order to work with dates in autofilter, you need to know one thing - autofilter recognizes only U.S. date format: month/day/year.
Sub FFF()
Dim dt1 As Date, dt2 As Date
Dim sDate1$, sDate2
'// Get dates
dt1 = Now - 29: dt2 = Now - 1
'// Format dates
sDate1 = Format(dt1, "MM/yy/yyyy")
sDate2 = Format(dt2, "MM/yy/yyyy")
With Sheets("Main").Range("A1").CurrentRegion
.AutoFilter
.AutoFilter Field:=2, Criteria1:=">=" & sDate1, Operator:=xlAnd, Criteria2:="<=" & sDate2
End With
End Sub
add a comment |
In order to work with dates in autofilter, you need to know one thing - autofilter recognizes only U.S. date format: month/day/year.
Sub FFF()
Dim dt1 As Date, dt2 As Date
Dim sDate1$, sDate2
'// Get dates
dt1 = Now - 29: dt2 = Now - 1
'// Format dates
sDate1 = Format(dt1, "MM/yy/yyyy")
sDate2 = Format(dt2, "MM/yy/yyyy")
With Sheets("Main").Range("A1").CurrentRegion
.AutoFilter
.AutoFilter Field:=2, Criteria1:=">=" & sDate1, Operator:=xlAnd, Criteria2:="<=" & sDate2
End With
End Sub
add a comment |
In order to work with dates in autofilter, you need to know one thing - autofilter recognizes only U.S. date format: month/day/year.
Sub FFF()
Dim dt1 As Date, dt2 As Date
Dim sDate1$, sDate2
'// Get dates
dt1 = Now - 29: dt2 = Now - 1
'// Format dates
sDate1 = Format(dt1, "MM/yy/yyyy")
sDate2 = Format(dt2, "MM/yy/yyyy")
With Sheets("Main").Range("A1").CurrentRegion
.AutoFilter
.AutoFilter Field:=2, Criteria1:=">=" & sDate1, Operator:=xlAnd, Criteria2:="<=" & sDate2
End With
End Sub
In order to work with dates in autofilter, you need to know one thing - autofilter recognizes only U.S. date format: month/day/year.
Sub FFF()
Dim dt1 As Date, dt2 As Date
Dim sDate1$, sDate2
'// Get dates
dt1 = Now - 29: dt2 = Now - 1
'// Format dates
sDate1 = Format(dt1, "MM/yy/yyyy")
sDate2 = Format(dt2, "MM/yy/yyyy")
With Sheets("Main").Range("A1").CurrentRegion
.AutoFilter
.AutoFilter Field:=2, Criteria1:=">=" & sDate1, Operator:=xlAnd, Criteria2:="<=" & sDate2
End With
End Sub
answered Jan 5 at 19:32


JohnyLJohnyL
3,7231924
3,7231924
add a comment |
add a comment |
Simple code to filter the dates for your ranges, modify to meet your needs...
With ThisWorkbook.Sheets("Main").Cells(1, 1).CurrentRegion
.AutoFilter
.AutoFilter Field:=2, Criteria1:=">=" & (Now - 29), Operator:=xlAnd, Criteria2:="<=" & (Now - 1)
'Do something
.AutoFilter Field:=2, Criteria1:=">=" & (Now - 394), Operator:=xlAnd, Criteria2:="<=" & (Now - 365)
'Do something
End With
1
1)Range
doesn't haveAutoFilterMode
property. 2) Autofilter recognizes only U.S. date format. Did you test your code?
– JohnyL
Jan 5 at 19:33
@JohnyL, Thank you for the imput, but not for the markdown. Yes i did test my code and then added theAutoFilterMode
to the answer while in SO, my bad. The second part, i don't see anything that in the OPs code that identifies dates are not U.S. date format. Also, your answer is noot complete, since the primary question what how to filter for the privious 28 days and also filter the same 28 days from the previous year. But i won't down vote you.+1 for catching my error.
– GMalc
Jan 6 at 19:20
add a comment |
Simple code to filter the dates for your ranges, modify to meet your needs...
With ThisWorkbook.Sheets("Main").Cells(1, 1).CurrentRegion
.AutoFilter
.AutoFilter Field:=2, Criteria1:=">=" & (Now - 29), Operator:=xlAnd, Criteria2:="<=" & (Now - 1)
'Do something
.AutoFilter Field:=2, Criteria1:=">=" & (Now - 394), Operator:=xlAnd, Criteria2:="<=" & (Now - 365)
'Do something
End With
1
1)Range
doesn't haveAutoFilterMode
property. 2) Autofilter recognizes only U.S. date format. Did you test your code?
– JohnyL
Jan 5 at 19:33
@JohnyL, Thank you for the imput, but not for the markdown. Yes i did test my code and then added theAutoFilterMode
to the answer while in SO, my bad. The second part, i don't see anything that in the OPs code that identifies dates are not U.S. date format. Also, your answer is noot complete, since the primary question what how to filter for the privious 28 days and also filter the same 28 days from the previous year. But i won't down vote you.+1 for catching my error.
– GMalc
Jan 6 at 19:20
add a comment |
Simple code to filter the dates for your ranges, modify to meet your needs...
With ThisWorkbook.Sheets("Main").Cells(1, 1).CurrentRegion
.AutoFilter
.AutoFilter Field:=2, Criteria1:=">=" & (Now - 29), Operator:=xlAnd, Criteria2:="<=" & (Now - 1)
'Do something
.AutoFilter Field:=2, Criteria1:=">=" & (Now - 394), Operator:=xlAnd, Criteria2:="<=" & (Now - 365)
'Do something
End With
Simple code to filter the dates for your ranges, modify to meet your needs...
With ThisWorkbook.Sheets("Main").Cells(1, 1).CurrentRegion
.AutoFilter
.AutoFilter Field:=2, Criteria1:=">=" & (Now - 29), Operator:=xlAnd, Criteria2:="<=" & (Now - 1)
'Do something
.AutoFilter Field:=2, Criteria1:=">=" & (Now - 394), Operator:=xlAnd, Criteria2:="<=" & (Now - 365)
'Do something
End With
edited Jan 6 at 19:27
answered Jan 2 at 14:33


GMalcGMalc
1,1511411
1,1511411
1
1)Range
doesn't haveAutoFilterMode
property. 2) Autofilter recognizes only U.S. date format. Did you test your code?
– JohnyL
Jan 5 at 19:33
@JohnyL, Thank you for the imput, but not for the markdown. Yes i did test my code and then added theAutoFilterMode
to the answer while in SO, my bad. The second part, i don't see anything that in the OPs code that identifies dates are not U.S. date format. Also, your answer is noot complete, since the primary question what how to filter for the privious 28 days and also filter the same 28 days from the previous year. But i won't down vote you.+1 for catching my error.
– GMalc
Jan 6 at 19:20
add a comment |
1
1)Range
doesn't haveAutoFilterMode
property. 2) Autofilter recognizes only U.S. date format. Did you test your code?
– JohnyL
Jan 5 at 19:33
@JohnyL, Thank you for the imput, but not for the markdown. Yes i did test my code and then added theAutoFilterMode
to the answer while in SO, my bad. The second part, i don't see anything that in the OPs code that identifies dates are not U.S. date format. Also, your answer is noot complete, since the primary question what how to filter for the privious 28 days and also filter the same 28 days from the previous year. But i won't down vote you.+1 for catching my error.
– GMalc
Jan 6 at 19:20
1
1
1)
Range
doesn't have AutoFilterMode
property. 2) Autofilter recognizes only U.S. date format. Did you test your code?– JohnyL
Jan 5 at 19:33
1)
Range
doesn't have AutoFilterMode
property. 2) Autofilter recognizes only U.S. date format. Did you test your code?– JohnyL
Jan 5 at 19:33
@JohnyL, Thank you for the imput, but not for the markdown. Yes i did test my code and then added the
AutoFilterMode
to the answer while in SO, my bad. The second part, i don't see anything that in the OPs code that identifies dates are not U.S. date format. Also, your answer is noot complete, since the primary question what how to filter for the privious 28 days and also filter the same 28 days from the previous year. But i won't down vote you.+1 for catching my error.– GMalc
Jan 6 at 19:20
@JohnyL, Thank you for the imput, but not for the markdown. Yes i did test my code and then added the
AutoFilterMode
to the answer while in SO, my bad. The second part, i don't see anything that in the OPs code that identifies dates are not U.S. date format. Also, your answer is noot complete, since the primary question what how to filter for the privious 28 days and also filter the same 28 days from the previous year. But i won't down vote you.+1 for catching my error.– GMalc
Jan 6 at 19:20
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54005579%2ffilter-between-2-date-ranges%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
qJBgSEVvqYgw042BnvMhxB8ECeKPK3wA KTVlmyFKvPpd8HgvIVODZ,Eubgb0tZLf2zuGJFOq5nanvOBnfLwqA