Sort Date with Drop Down
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Sort Date with Drop Down
Hi,
I would be happy if anyone could let me know why the drop down boxes assigned to the sheet does not filter the dates instead of the data validation cells.
Thanks in advance.
I would be happy if anyone could let me know why the drop down boxes assigned to the sheet does not filter the dates instead of the data validation cells.
Thanks in advance.
You do not have the required permissions to view the files attached to this post.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sort Date with Drop Down
In the first place, both dropdowns are linked to the same cell O3, so if you select a date in one dropdown, the same date is selected in the other dropdown, so it is impossible to select a date range. You should link the second dropdown to P3 instead of O3.
Change the code as follows:
Either use the same for DropDown2_Change, or assing DropDown1_Change to the second dropdown too and remove DropDown2_Change.
Change the code as follows:
Code: Select all
Sub DropDown1_Change()
Dim rng As Range
Set rng = Range(Cells(10, 3), Cells(Rows.Count, 3).End(xlUp))
rng.AutoFilter Field:=1, _
Criteria1:=">=" & CLng(Range("DateList")(Range("O3"))), _
Criteria2:="<=" & CLng(Range("DateList")(Range("P3")))
End Sub
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Sort Date with Drop Down
Code: Select all
Sub UpdateDateList()
Dim wsDL As Worksheet
Dim wsS As Worksheet
'
Sheets("Sheet6").Range("AllDates").AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Range("C1"), Unique:=True
Range("C2").Select
ActiveCell.FormulaR1C1 = "1/1/2005"
Range("C2").Select
Range("C1:C36").Sort Key1:=Range("C2"), Order1:=xlAscending, header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
End Sub
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sort Date with Drop Down
AllDates is a named range on the sheet CustomerList, not Sheet6.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Sort Date with Drop Down
Yeah. But in doing so the column C of the sheet "CustomerList" gets cleared.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sort Date with Drop Down
Does column C on the CustomerList sheet get cleared whichever sheet is active when you run the macro?
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Sort Date with Drop Down
It does not get cleared when I run the macro on the sheet "DateList" but the column in Sheet "DateList" gets cleared.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sort Date with Drop Down
Advanced Filter will always clear all cells below the field names (column headings) of the "Copy to" range to the bottom of the worksheet. There is no way to prevent that. So you must use a range where it doesn't cause problems if that happens.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Sort Date with Drop Down
Actually I had missed a part from the code. The code now works fine when the Apply filter code is run.
Best Regards,
Adam
Adam
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Sort Date with Drop Down
As the sheet does not seem to filter when having two filtered ranges I’ve moved the ranges O1:P3 to the sheet “StaffList†O1:P3.
Having that I’ve changed the code as following. But this setup does not work. What may be the reason?
Having that I’ve changed the code as following. But this setup does not work. What may be the reason?
Code: Select all
Sub DropDown1_Change()
Dim rng As Range
Set rng = Range(Cells(10, 3), Cells(Rows.Count, 3).End(xlUp))
rng.AutoFilter Field:=1, _
Criteria1:=">=" & CLng(Worksheets("Stafflist").Range("DateList")(Range("O3"))), _
Criteria2:="<=" & CLng(Worksheets("Stafflist").Range("DateList")(Range("P3")))
End Sub
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sort Date with Drop Down
You moved O3 and P3 to Stafflist, so you should tell the code that Range("O3") and Range("P3") are on that sheet.
Did you also move the DateList range there? If not, you should *not* tell the code that DateList is on the Stafflist sheet.
Did you also move the DateList range there? If not, you should *not* tell the code that DateList is on the Stafflist sheet.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Sort Date with Drop Down
No. I didn't move the date list. Only O3 & P3. Does this mean I should keep the above code as same with the code you had provided?
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sort Date with Drop Down
You must move
Worksheets("Stafflist").
so that it qualifies the cells O3 and P3, not the range DateList.
Worksheets("Stafflist").
so that it qualifies the cells O3 and P3, not the range DateList.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Sort Date with Drop Down
To understand my problem better I've attached the current workbook.
If the range N1:N2 is removed from the customer sheet, the range O1:P2 works fine. But if the range N1:N2 is present, the range O1:P2 does not work.
How could it be made for both the ranges to work?
In short to filter dates and to select categories.
If the range N1:N2 is removed from the customer sheet, the range O1:P2 works fine. But if the range N1:N2 is present, the range O1:P2 does not work.
How could it be made for both the ranges to work?
In short to filter dates and to select categories.
You do not have the required permissions to view the files attached to this post.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sort Date with Drop Down
The Category dropdown doesn't filter the first column but the ninth column. So you should use
And you have somehow managed to turn off AutoFilter for the first column. So use the ribbon to turn it on again.
Code: Select all
Sub DropDown6_Change()
Range("C10").AutoFilter Field:=9, Criteria1:=Range("MyList")(Range("N4"))
End Sub
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Sort Date with Drop Down
Thanks for the help. But, how could the sheet be filterd without keeping the filter arrows visible?
The following adjustments does not work simulateneously.
Sub DropDown6_Change()
Range("C10").AutoFilter Field:=9, Criteria1:=Range("MyList")(Range("N4")), VisibleDropDown:=False
End Sub
Sub DropDown3_Change()
Dim rng As Range
Set rng = Range(Cells(10, 3), Cells(Rows.Count, 3).End(xlUp))
rng.AutoFilter Field:=1, _
Criteria1:=">=" & CLng(Range("DateList")(Range("O3"))), _
Criteria2:="<=" & CLng(Range("DateList")(Range("P3"))), VisibleDropDown:=False
End Sub
The following adjustments does not work simulateneously.
Sub DropDown6_Change()
Range("C10").AutoFilter Field:=9, Criteria1:=Range("MyList")(Range("N4")), VisibleDropDown:=False
End Sub
Sub DropDown3_Change()
Dim rng As Range
Set rng = Range(Cells(10, 3), Cells(Rows.Count, 3).End(xlUp))
rng.AutoFilter Field:=1, _
Criteria1:=">=" & CLng(Range("DateList")(Range("O3"))), _
Criteria2:="<=" & CLng(Range("DateList")(Range("P3"))), VisibleDropDown:=False
End Sub
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sort Date with Drop Down
Why do you want to hide the filter arrows? Keeping them visible provides the user with feedback about which columns are filtered.
Best wishes,
Hans
Hans