Filtering and sorting columns based upon cell click

  • 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:



    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.

  • 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.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • 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.


    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.

  • Re: Filtering and sorting columns based upon cell click


    Change code to


    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]

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • 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.


  • 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.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • 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.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Filtering and sorting columns based upon cell click



    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.

  • 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

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!