Autofit rows based on Slicer selection

[Ex_Newbie]

Hello Excel Gurus!

I am trying to create an interactive dashboard using Slicers, Pivot tables/charts and Index/Vlookup functions. There is a section where I am using INDEX to lookup text based on a Slicer selection (which I have linked in my INDEX formula). The text varies in length based on my Slicer selection so I selected Wrap Text in the cell and Autofit Row Height but the the row height doesn't change as my Slicer selection changes. I spent hours (maybe days) googling how to fix that problem and the only thing I came up with was using VBA to do this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Range("V4:V7").Rows.AutoFit


End Sub

The problem with this is the row heights don't change unless I click on a cell on the spreadsheet to trigger the event. Now since this is a dashboard that will be sent to other users and I don't want to have them do that after filtering with Slicer. I am totally not versed with VBA at all. So I want to know what can I add to the code to have the row heights change automatically once the selection is made with the Slicer?

Thanks so much for any advice!!!

 

[mrshl9898]

Changing the slicer should refresh the pivot. So run the event off that.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
 [COLOR=#252C2F][FONT=Courier]End Sub
[/FONT][/COLOR]

 

[Ex_Newbie]

Thanks @mrshl9898. I actually got it to work with the Calculate event:

Private Sub Worksheet_Calculate()


Range("V4:V8").Rows.AutoFit


End Sub

But now the rows look kind of tight, what is the code to add some extra height to the rows after autofit is done?

Thanks.

 

[mrshl9898]

Something like this should fit your purpose

With Worksheets("Sheet1").Rows(4)
 .RowHeight = .RowHeight + 2
End With