Sort Date with Drop Down

[adam]

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.

[HansV]

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:

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

Either use the same for DropDown2_Change, or assing DropDown1_Change to the second dropdown too and remove DropDown2_Change.

[adam]

Thanks for the help, Hans.

[adam]

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

Why is this code giving me the debug message when I try to update the date list in the sheet "DateList", after adding a new row of date to the sheet "CustomerList"?

[HansV]

AllDates is a named range on the sheet CustomerList, not Sheet6.

[adam]

Yeah. But in doing so the column C of the sheet "CustomerList" gets cleared.

[HansV]

Which sheet is active when you run this macro?

[adam]

It's not working with either sheet activation.

[HansV]

Does column C on the CustomerList sheet get cleared whichever sheet is active when you run the macro?

[adam]

It does not get cleared when I run the macro on the sheet "DateList" but the column in Sheet "DateList" gets cleared.

[HansV]

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.

[adam]

Actually I had missed a part from the code. The code now works fine when the Apply filter code is run.

[adam]

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?

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

[HansV]

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.

[adam]

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?

[HansV]

You must move

Worksheets("Stafflist").

so that it qualifies the cells O3 and P3, not the range DateList.

[adam]

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.

[HansV]

The Category dropdown doesn't filter the first column but the ninth column. So you should use

Code: Select all

Sub DropDown6_Change()
  Range("C10").AutoFilter Field:=9, Criteria1:=Range("MyList")(Range("N4"))
End Sub

And you have somehow managed to turn off AutoFilter for the first column. So use the ribbon to turn it on again.

[adam]

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

[HansV]

Why do you want to hide the filter arrows? Keeping them visible provides the user with feedback about which columns are filtered.