Double Click Sorting in VBA

[mattadams84]

Hi,

I have a sheet where the headers i want to sort start in row 4 and the data is below. The first column with data is column B. Depending on the sheet there are different numbers of columns and a different number of rows. I read that it is possible to sort data by double clicking on the headers using VBA. I have found this example

[COLOR=#000000][FONT=inherit]Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
[/FONT][/COLOR]Dim KeyRange As Range
Dim ColumnCount As Integer
ColumnCount = Range("DataRange").Columns.Count
Cancel = False
If Target.Row = 1 And Target.Column <= ColumnCount Then
Cancel = True
Set KeyRange = Range(Target.Address)
Range("DataRange").Sort Key1:=KeyRange, Header:=xlYes
End If 
[COLOR=#000000][FONT=inherit]End Sub[/FONT][/COLOR]

In this code there is already a named range which i do not have. Ideally i dont want to do this with a named range, unless it is created dynamically with VBA. This code has to be placed in the sheet code.

Is it possible to modify this code to adapt to my sheets? I have attached an example of my workbook. I am looking to sort the FHSelections and SHSelections sheets.

https://www.dropbox.com/s/mlwss7s3ueqx6o7/Filters.xlsm?dl=0

 

[rpaulson]

try this.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

lr = Cells(Rows.Count, "B").End(xlUp).Row
lc = Cells(4, Columns.Count).End(xlToLeft).Column
If Target.Row = 4 And Target.Column <= lc Then Range(Cells(5, "B"), Cells(lr, lc)).Sort Key1:=Cells(4, Target.Column), Header:=xlYes 'Order1:=xlDescending

End Sub

Enjoy,
Ross

 

[mattadams84]

Thanks Ross,

This is great, it works perfectly however it only sorts from row 6 downwards. In row 4 i have my headers and Row 5 doesnt get sorted. Any ideas ?

 

[rpaulson]

change
If Target.Row = 4 And Target.Column <= lc Then Range(Cells(5, "B"), Cells(lr, lc)).Sort Key1:=Cells(4, Target.Column), Header:=xlYes 'Order1:=xlDescending
to
If Target.Row = 4 And Target.Column <= lc Then Range(Cells(4, "B"), Cells(lr, lc)).Sort Key1:=Cells(4, Target.Column), Header:=xlYes 'Order1:=xlDescending

 

[mattadams84]

Perfect. thanks v much