Sort Date with Drop Down

[adam]

Because the intention is to not provide the user with feedback about which columns are filtered. Instead create a database environment.

[HansV]

You know my opinion about that, so I won't repeat it.

[adam]

Nevermind. I've solved it. Anyways, thanks for the reply.

[HansV]

It would be nice if you shared your solution here.

[adam]

Sure.

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 

By the way this code does not hide the column 2. Any help to hide column 2 would be appreciated.

[HansV]

Why do you have

If c.Column <> 2 Then

and the corresponding End If?

[HansV]

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.

[adam]

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.

[adam]

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?

[HansV]

Could you post the current version of the workbook?

[adam]

Attached; please find the workbook with the query.

[HansV]

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.

Fil.xlsm

[adam]

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?

[HansV]

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

Fil.xlsm

[adam]

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.

[adam]

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.

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

[HansV]

In which cells of each row do you want vertical lines? Just the cells that are filled, or ...?

[adam]

Sorry, It should be horizontal lines instead of vertical.

[HansV]

In which cells of each row do you want vertical horizontal lines? Just the cells that are filled, or ...?

[adam]

Only the cells that are filled. Meaning from column "C" to Column "K" of each row, as they get filled.