Sort Date with Drop Down
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Sort Date with Drop Down
Because the intention is to not provide the user with feedback about which columns are filtered. Instead create a database environment.
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 know my opinion about that, so I won't repeat it.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Sort Date with Drop Down
Nevermind. I've solved it. Anyways, thanks for the reply.
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
Re: Sort Date with Drop Down
Sure.
By the way this code does not hide the column 2. Any help to hide column 2 would be appreciated.
Code: Select all
Sub HideArrows()
Dim c As Range
Dim i As Integer
i = Cells(1, 1).End(xlToRight).Column
Application.ScreenUpdating = False
For Each c In Range(Cells(1, 1), Cells(1, i))
If c.Column <> 2 Then
c.AutoFilter Field:=c.Column, _
Visibledropdown:=False
End If
Next
Application.ScreenUpdating = True
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 have
If c.Column <> 2 Then
and the corresponding End If?
If c.Column <> 2 Then
and the corresponding End If?
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sort Date with Drop Down
OK, I see why - you copied the code from Hide Excel AutoFilter Arrows on the Contextures website without any modification. There are more examples on that web page; you have to try to understand them so that you can adapt them for your situation.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Sort Date with Drop Down
Yeah. It's good that you too had found out where I had taken the code. Anyways I've modified it now accordingly. Thanks for the reply.
Best Regards,
Adam
Adam
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Sort Date with Drop Down
One more query.
When the dates from first drop down box are selected, Visual Basic shows a debug message. When the user clicks the “End†button and selects the second date from the remaining drop down box, the sheet gets filtered showing data rows between the selected dates.
If this is the situation, what is the job of the macro Sub ApplyFilter() in here?
When the dates from first drop down box are selected, Visual Basic shows a debug message. When the user clicks the “End†button and selects the second date from the remaining drop down box, the sheet gets filtered showing data rows between the selected dates.
If this is the situation, what is the job of the macro Sub ApplyFilter() in here?
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
Re: Sort Date with Drop Down
Attached; please find the workbook with the query.
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 problem occurs if one of the date combo boxes is blank. In the attached version, the code will work even if one or both of the date combo boxes are blank.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Sort Date with Drop Down
Thank you, Hans. That’s exactly what I was wanting.
Still I guess the code Sub ApplyFilter() has no use. But it has the function to copy the data rows in column “C†of Sheet CustomerList and paste them in column “C†of the sheet DateList by deleting the duplicate rows.
With this situation how could it be changed as a worksheet event code so that when ever a new date is written in column "C" of the sheet CustomerList, the code automatically copies the unique date and paste in column “C†of the sheet “Datelist?
Still I guess the code Sub ApplyFilter() has no use. But it has the function to copy the data rows in column “C†of Sheet CustomerList and paste them in column “C†of the sheet DateList by deleting the duplicate rows.
With this situation how could it be changed as a worksheet event code so that when ever a new date is written in column "C" of the sheet CustomerList, the code automatically copies the unique date and paste in column “C†of the sheet “Datelist?
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 don't need ApplyFilter if you change the Worksheet_Change event procedure for the CustomerList sheet:
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("C11:C" & Rows.Count), Target) Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Range("AllDates").AdvancedFilter _
Action:=xlFilterCopy, CriteriaRange:="", _
CopyToRange:=Sheets("DateList").Range("C1"), Unique:=True
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Sort Date with Drop Down
Thank You, Hans. That's exactly what I was asking for. As you've mentioned, the Worksheet_Change event procedure for the CustomerList sheet does the job of the ApplyFilter.
Best Regards,
Adam
Adam
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Sort Date with Drop Down
I've unchecked the function to show grid lines in the active sheet. With this situation how could this code be made to insert dotted vertical lines in each row after data entry starting from row 11 & onwards. but within the columns C to K
Currently this is adding row lines to the rows 2 & 1 in column 1.
Currently this is adding row lines to the rows 2 & 1 in column 1.
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Or Target.Row < 2 Then Exit Sub
If Target.Value <> Target.Offset(-1, 0).Value Then
Target.Offset(-1, 0).Borders(xlEdgeBottom).LineStyle = xlContinuous
End If
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
In which cells of each row do you want vertical lines? Just the cells that are filled, or ...?
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Sort Date with Drop Down
Sorry, It should be horizontal lines instead of vertical.
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 which cells of each row do you want vertical horizontal lines? Just the cells that are filled, or ...?
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Sort Date with Drop Down
Only the cells that are filled. Meaning from column "C" to Column "K" of each row, as they get filled.
Best Regards,
Adam
Adam