Excel VBA Course
Excel VBA Course - From Beginner to Expert

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

View Course

Slicer item


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

Please edit your question to attach a representative Excel file using the Add Files... button. Then we should be able to provide you with an answer. 
John_Ru (rep: 4937) Mar 5, '23 at 3:44 pm
Thanks for doing that, Paulus. I've given you my solution too (below Willie's answer). Kindly consider that too, since you only need to chnage the Slicer choice.
John_Ru (rep: 4937) Mar 6, '23 at 5:49 am
Add to Discussion


Selected Answer

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


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 Sub

When 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).



Thank you John.
It works great with the example workbook,  will try it on the actual workbook.
I don't think there will be no issues
Paulus de boskabouter (rep: 4) Mar 6, '23 at 2:10 pm
Sounds like that did the trick. Thanks for selecting my Answer, Paulus. 
John_Ru (rep: 4937) Mar 6, '23 at 2:10 pm
Add to Discussion

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 Sub

If this solves your problem, please mark my answer as "Selected"

Cheers   :-)


Answer the Question

You must create an account to use the forum. Create an Account or Login