Consulting

Results 1 to 8 of 8

Thread: need help with filtered table

  1. #1
    VBAX Mentor
    Joined
    Aug 2012
    Posts
    367
    Location

    need help with filtered table

    Hi all,

    I've got a rather large table that I filter using VBA. My problem is that the table data is often 'off screen', and the filter often appears to show no result.
    eg, the last row of the table is currently 7063. scroll to the end of the table, then filter to rows 2000 to 2010, and you must manually scroll them into view.

    how can I set the code to show the first visible row of the filtered table automatically?

    I have searched for a solution, but it appears I am using the wrong terminology
    Any suggestions are appreciated

    Thanks
    Werafa
    Remember: it is the second mouse that gets the cheese.....

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,785
    Location
    Try this in the right place after filtering

    application.Goto range("A1"),true
    
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Mentor
    Joined
    Aug 2012
    Posts
    367
    Location
    Thanks Paul,
    it works.

    I have never seen this method before. From what I can read, it is nearly the same as select. Are there any other 'magic' ways to use this command?
    Remember: it is the second mouse that gets the cheese.....

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,785
    Location
    Not as far as I know

    It is sort of like a .Select, but the sceond 'Scroll' parameter is what really make it different
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    VBAX Mentor
    Joined
    Aug 2012
    Posts
    367
    Location
    It does,

    the one other comment I've read is that it'll goto any range, on any sheet, in any workbook - in one simple command.
    Why then is Select more popular?
    Remember: it is the second mouse that gets the cheese.....

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,785
    Location
    My GUESS is that the macro recorder records .Select and not GoTo

    I rarely .Select something like a cell, range, or worksheet in my macros


    https://docs.microsoft.com/en-us/office/vba/api/excel.application.goto

    This method differs from the Select method in the following ways:
    • If you specify a range on a sheet that's not on top, Microsoft Excel will switch to that sheet before selecting. (If you use Select with a range on a sheet that's not on top, the range will be selected but the sheet won't be activated).
    • This method has a Scroll argument that lets you scroll through the destination window.
    • When you use the Goto method, the previous selection (before the Goto method runs) is added to the array of previous selections (for more information, see the PreviousSelections property). You can use this feature to quickly jump between as many as four selections.
    • The Select method has a Replace argument; the Goto method doesn't.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    VBAX Mentor
    Joined
    Aug 2012
    Posts
    367
    Location
    I agree with the 'rarely select' comment.
    The only reason I see to do this is debugging and user manipulation (sorry - meant 'assisting')

    as for the guess - this makes sense. this is the first time in 10 years of casual programming that I've actually needed this feature, and most of my learning comes from (early days) the macro recorder, and now google searches of posts from people who learnt on the macro recorder.

    out if interest, how did you originally learn of this?
    Remember: it is the second mouse that gets the cheese.....

  8. #8
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,785
    Location
    Not sure. I probably needed it and Googled, or I saw that someone else used it and got curious about what it was doing
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •