200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
I have a slicer on a top row that is frozen.
Below is a table that is scrollable.
When i am at the bottom of the table and select a Slicer item , i have to scroll manually up to see all the results.
Is it possible to automatically scroll the table up to see the results when i select a slicer item?
Attached file is an example, orgininal contains links to xml and csv files
Hi again Paulus.
In the attached modified file (provided you're using Excel 2010 or later with macros enabled), you can change the Slicer selection (in row 1) from wherever you are and it will jump to the first visible rows for that choice. E.g from row 118 of the unfiltered list, choose SANDVIK and it will show from row 15 down (revealing 33 rows). Likewise for any other choice.
It works because there seem to be no calculations in your spreadsheet so I put this formula in cell I2 (in hidden column I):
=SUBTOTAL(3,Table1[Suplier])where the bold 3 is the Function_num argument (COUNTA) so it totals only the number of visible rows in that table column. That then forces a calculation event which triggers this macro in the sheet's code window:
Private Sub Worksheet_Calculate()
'this is forced by the hidden COUNTA subtotal in hidden cell I1
ActiveWindow.ScrollRow = 1
End SubWhen the selection changes, Excel does the calculation in cell I2 and the code above puts the view back to the start. If doesn't rely on that value changing either (e.g. LASAULEC and MAGISTOR both reveal 2 rows).
Hope this fixes your problem. If so, please remember to mark this Answer as Selected (or do the same for Willie's if that's better for you).
Hello Paulus,
Thanks for uploading a sample file.
I use Excel 2007 but this code might do what you are looking to achieve. Place this code in the code window for the worksheet. Just right click the file tab for the worksheet, select "View Code" and in the window that opens paste in the code. (I revised your file with this code included - file attached)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' macro written by WillieD24 Mar. 6/23 for teachexcel.com
' when a used cell in column "G" is selected, the sheet will be scrolled up to the top
Dim LRow As Long ' row number of last used row
' find row number of last used row in column G
LRow = Cells(Rows.Count, "G").End(xlUp).Row
' if selected cell is in the used range of column G
If Not Application.Intersect(Target, Range("G3:G" & LRow)) Is Nothing Then ActiveWindow.ScrollRow = 1
End SubIf this solves your problem, please mark my answer as "Selected"
Cheers :-)