Filtering and sorting columns based upon cell click

y2trips · Dec 1st 2016

Hello,

I've been trying to figure out a way to turn a number of If statements into an efficient loop, but haven't been able to figure it out. What the code does is if a cell is clicked, it will filter rows out of the table if cells in that column have blanks. It will then sort the remaining data largest to smallest. Using if statements, this was very easy to achieve with the below code:

Code

If Not Intersect(Target, Range("H6")) Is Nothing Then
            On Error Resume Next
            CuCell = ActiveCell
            ActiveSheet.ShowAllData
            Range("$H$6:$H$500").AutoFilter Field:=3, Criteria1:="<>", Visibledropdown:=False
            ActiveSheet.Sort.SortFields.Clear
            ActiveSheet.Sort.SortFields.Add Key:=Range("H7:H500"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortTextAsNumbers
            With ActiveSheet.Sort
                .SetRange Range("C6:X500")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End If
        If Not Intersect(Target, Range("I6")) Is Nothing Then
        On Error Resume Next
            ActiveSheet.ShowAllData
            Range("$I$6:$I$500").AutoFilter Field:=4, Criteria1:="<>", Visibledropdown:=False
            ActiveSheet.Sort.SortFields.Clear
            ActiveSheet.Sort.SortFields.Add Key:=Range("I7:I500"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortTextAsNumbers
            With ActiveSheet.Sort
                .SetRange Range("C6:X500")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End If

Display More

I'm performing an if statement for all cells from H6:X6 however and would rather loop the request. the problem is the different variables needed for the loop:
- I need to dynamically account for the vertical range in question
- I need to account for the field in the filter call

I've been browsing this forum for some time, but haven't had much luck. Any guidance would be appreciated.

**KjBox** · Dec 2nd 2016

Re: Filtering and sorting columns based upon cell click

Try this, place it in the Worksheet Object Module for the sheet that contains the table.

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, [H6:X6]) Is Nothing Then
        Cells(6, Target.Column).Resize(494).AutoFilter 1, "<>", , , 0
        With [c6:x500]
            .Parent.Sort.SortFields.Clear
            .Sort .Columns(Target.Column - 2), , , , , , , xlYes
        End With
    End If
    
End Sub

Display More

y2trips · Dec 2nd 2016

Re: Filtering and sorting columns based upon cell click

Quote from KjBox;781706

Try this, place it in the Worksheet Object Module for the sheet that contains the table.

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, [H6:X6]) Is Nothing Then
        Cells(6, Target.Column).Resize(494).AutoFilter 1, "<>", , , 0
        With [c6:x500]
            .Parent.Sort.SortFields.Clear
            .Sort .Columns(Target.Column - 2), , , , , , , xlYes
        End With
    End If
    
End Sub

Display More

I gave it a shot and it's close, only 2 issues: It sorts lowest to highest, I need highest to lowest. Also, it's not filtering out blanks to that particular column. This is great though, with this start I should be able to get somewhere.

Much appreciated.

**KjBox** · Dec 3rd 2016

Re: Filtering and sorting columns based upon cell click

Change code to

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, [H6:X6]) Is Nothing Then
    Application.ScreenUpdating = 0
        Cells(6, Target.Column).Resize(494).AutoFilter 1, "<>", , , 0
        With [c6:x500]
            .Parent.Sort.SortFields.Clear
            .Sort .Columns(Target.Column - 2), 2, , , , , , xlYes
        End With
    End If
    
End Sub

Display More

It certainly does filter out the blanks and sort by the column of the selected cell.

Do you need it to filter and/or sort by a column other than the selected cell column?

Might be best if you attach your workbook and show exactly what you want to happen.
[sw]*[/sw]

y2trips · Dec 3rd 2016

Re: Filtering and sorting columns based upon cell click

To get the filtering to work, I needed to change the 1 to a 3 in the autofilter line. In a perfect world, if that was a variable that changed depending on which cell was clicked, it would be perfect. Meaning, if H6 was clicked, that variable = 3. If X6 was clicked, it would = 19.

I also took care of the ascending/descending issue as well, by adding xlDescending to the code. Here's what i have currently and it's working exactly as expected and much more streamlined.

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)




On Error GoTo ws_exit
Application.EnableEvents = False




' Sort Batting Stats (Descending)
    If Target.Count = 1 Then
        If Not Intersect(Target, [H6:N6]) Is Nothing Then
            On Error Resume Next
            ActiveSheet.ShowAllData
            Cells(6, Target.Column).Resize(494).AutoFilter 3, "<>", , , 0
            With [c6:x500]
                .Parent.Sort.SortFields.Clear
                .Sort .Columns(Target.Column - 2), xlDescending, , , , , , xlYes
            End With
        End If
    
' Sort Pitchers Stats (Descending)
        If Not Intersect(Target, [O6:U6]) Is Nothing Then
            On Error Resume Next
            ActiveSheet.ShowAllData
            Cells(6, Target.Column).Resize(494).AutoFilter 10, "<>", , , 0
            With [c6:x500]
                .Parent.Sort.SortFields.Clear
                .Sort .Columns(Target.Column - 2), xlDescending, , , , , , xlYes
            End With
        End If
        
' Sort Pitchers Stats (Ascending)
        If Not Intersect(Target, [V6:W6]) Is Nothing Then
            On Error Resume Next
            ActiveSheet.ShowAllData
            Cells(6, Target.Column).Resize(494).AutoFilter 10, "<>", , , 0
            With [c6:x500]
                .Parent.Sort.SortFields.Clear
                .Sort .Columns(Target.Column - 2), xlAscending, , , , , , xlYes
            End With
        End If
 
 ' Sort Auction Values (Descending)
        If Not Intersect(Target, [X6]) Is Nothing Then
            On Error Resume Next
            ActiveSheet.ShowAllData
            Cells(6, Target.Column).Resize(494).AutoFilter 19, "<>", , , 0
            With [c6:x500]
               .Parent.Sort.SortFields.Clear
               .Sort .Columns(Target.Column - 2), xlDescending, , , , , , xlYes
            End With
        End If
    End If
    
ws_exit:
    Application.EnableEvents = True
    
End Sub

Display More

**KjBox** · Dec 3rd 2016

Re: Filtering and sorting columns based upon cell click

Are you sure that the code is working as expected? The code and your comments in the code do not match.

In each case the table will be sorted using the column of the selected cell. The table will be filtered for non-empty cells in either the "Batting Stats", "Pitchers Stats" or "Auction Value" column depending on the column of the selected cell.

**KjBox** · Dec 3rd 2016

Re: Filtering and sorting columns based upon cell click

This is the first mention you have made of needing the filtering to be by 1 of 4 possible columns, depending on the column of the selected cell.

I took,

Quote

What the code does is if a cell is clicked, it will filter rows out of the table if cells in that column have blanks.

to mean both filtering and sorting would be by the column of the selected cell.

To give you your "perfect world", both column for filtering and sorting key can be assigned to variables.

In your code you set the error handling, but then use On Error Resume Next, that will negate the previously set error handling. I assume you add the On Error Resume next because the code was doing nothing.

If so, then the reason it was doing nothing was because the error handling (before Resume next was added) was causing the procedure to exit because of errors in the code. Adding On Error Resume Next is not the way to avoid code errors.

If you can clearly explain which table columns (which will be the sheet column minus 2 because your table starts in Column C) need to be filtered and sorted then I will do the code using the variabls.

y2trips · Dec 3rd 2016

Re: Filtering and sorting columns based upon cell click

Quote from KjBox;781763

This is the first mention you have made of needing the filtering to be by 1 of 4 possible columns, depending on the column of the selected cell.

I took,

to mean both filtering and sorting would be by the column of the selected cell.

To give you your "perfect world", both column for filtering and sorting key can be assigned to variables.

In your code you set the error handling, but then use On Error Resume Next, that will negate the previously set error handling. I assume you add the On Error Resume next because the code was doing nothing.

If so, then the reason it was doing nothing was because the error handling (before Resume next was added) was causing the procedure to exit because of errors in the code. Adding On Error Resume Next is not the way to avoid code errors.

If you can clearly explain which table columns (which will be the sheet column minus 2 because your table starts in Column C) need to be filtered and sorted then I will do the code using the variabls.

Display More

First, thank you for the extremely detailed follow up and attention to my thread. Very much appreciated. I'll do my best to answer all follow up questions.

1. "Perfect world" if i click H6, the code should search column H for blanks and filter them out. If I6 is clicked, the same for Column I, and so on. I understand my code 2 posts above doesn't reflect that, however the code still works because in the ranges defined (H6:N6) for example in the first If statement, each of those 7 columns would have the same blanks in the came cells constantly. That code is only looking for blanks in column H the whole time, do it's not perfect, but it works. This carries through the 3 other If statements.

2. The On error resume next handles something i can't articulate well (or have figured out yet), there are some scenarios where clicking one of the cells does nothing. Meaning, without that error handling, if i click X6, the code would sort as expected. I would then click column U, and nothing would happen. i'm not understanding why thats happening, however with the On Error Resume Next, it solves that problem.

Hopefully this explains my use case. If you really would like a copy of the sheet, i'd be happy to send a copy, even if it's just for curiosity.

Again, thank you for the multiple follow ups.

**KjBox** · Dec 4th 2016

Re: Filtering and sorting columns based upon cell click

On Error Resume Next tells the code to ignore any code errors, it might appear to work because of how your current data looks (i.e. the 7 columns having the same blanks), but that may not always be the case.

Seeing your workbook would enable me to properly write and test code that uses variables to set the filtering and sorting columns for a cell selected within the range H6:X6

Filtering and sorting columns based upon cell click

Participate now!

Share