PDA

View Full Version : [SOLVED] need help with filtered table



werafa
07-14-2019, 04:07 AM
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

Paul_Hossler
07-14-2019, 07:03 AM
Try this in the right place after filtering



application.Goto range("A1"),true

werafa
07-14-2019, 02:16 PM
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?

Paul_Hossler
07-14-2019, 02:29 PM
Not as far as I know

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

werafa
07-14-2019, 02:33 PM
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?

Paul_Hossler
07-14-2019, 03:39 PM
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 (https://docs.microsoft.com/en-us/office/vba/api/excel.range.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 (https://docs.microsoft.com/en-us/office/vba/api/excel.application.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.

werafa
07-14-2019, 04:45 PM
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?

Paul_Hossler
07-14-2019, 05:11 PM
Not sure. I probably needed it and Googled, or I saw that someone else used it and got curious about what it was doing