Pivot Table - Last Row

[JoeSalmi]

How can I get VB to get me to the last row of a pivot table as soon as I open up that worksheet?

 

[Misca]

Try this in your worksheet module:

Private Sub Worksheet_Activate()

'Takes you to the last cell of the pivot table:
With ActiveSheet.PivotTables(1).TableRange2
    .Cells(.Cells.Count).Select
End With

End Sub

 

[JoeSalmi]

that does work but I'm such an idiot...

What I really need is.. hahaha

I need to activate the first blank cell in column B AFTER B8.

In my case I have data in B8:B19 but B20 is blank. I want it to find the first blank cell.

 

[Misca]

Try one of these:

'This takes you to the cell below the last cell with data (or B2 if the column is empty)
Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Select

'This takes you to the first empty cell after B8:
If Not IsEmpty(Range("B9")) Then
    Range("B8").End(xlDown).Offset(1, 0).Select
Else
    Range("B9").Select
End If

The first one finds the first blank cell from the bottom up (= usually the safest way if you're adding data to the sheet). The second one finds the first empty blank cell after B8 but doesn't make sure the rest of the cells in that row are empty.

The second one can be changed to

Range("B8:B" & Rows.Count).SpecialCells(xlCellTypeBlanks).Cells(1).Select

The difference is you don't need the IF sentence.

None of the lines checks if B8 is actually empty. The SpecialCells-solution takes you to B8 if it's empty. If you want to skip that, change the range to start from B9.

 

[JoeSalmi]

They both work like a charm THANK YOU SO MUCH.

Using that same method I would also like to have it automatically do that along with a refresh as soon as the sheet is selected. But only do it once after the sheet is opened.

What can I add to this to make it stop looping?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    ActiveSheet.PivotTables("PivotTable6").PivotCache.REFRESH
    
    'Takes you to the first blank cell
    Range("B8:B" & Rows.Count).SpecialCells(xlCellTypeBlanks).Cells(1).Select

End Sub

 

[Misca]

When working with events macros it's usually a good practice to add "Application.EnableEvents=False" to the beginning of the macro and turn the events back on at the very end of the macro.