Select PivotTable DataBodyRange, exclude last row

Question

Below is a macro that I use to select the DataBodyRange of a pivot table, excluding the last row (the Grand Total row).

Sub SelectDataBodyRange()
    Dim rng As Range
    Dim rows, cols As Integer

    Set rng = ActiveSheet.PivotTables("PivAuswertung").DataBodyRange

    'Now get the number of rows and columns
    rows = rng.rows.Count
    cols = rng.Columns.Count

    'Resize selection, exclude last row
    Set rng = rng.Resize(Rowsize:=rows - 1, ColumnSize:=cols)

    'Select the new range (with excluded last row)
    rng.Select
End Sub

Is there an easier way to exclude the last row in the selction?

Pᴇʜ · Answer 1 · 2020-02-26T10:58:30.857

Probably not really an easier way, looks good so far, but you can omit the column-size if it doesn't change: rng.Resize(Rowsize:=rows - 1). And therefore you don't need cols = rng.Columns.Count too.

If possible avoid using ActiveSheet this can easily be changed by a user's click. Better use ThisWorkbook.Worksheets("NameOfSheet").PivotTables…

Option Explicit

Sub SelectDataBodyRange()
    Dim rng As Range
    Set rng = ActiveSheet.PivotTables("PivAuswertung").DataBodyRange
    'if possible name your sheet and don't use ActiveSheet. Better ThisWorkbook.Worksheets("NameOfSheet").PivotTables…

    'Resize selection, exclude last row
    Set rng = rng.Resize(RowSize:=rng.rows.Count - 1)
    rng.Select
End Sub

Or even that should work

Option Explicit

Sub SelectDataBodyRange()
    With ActiveSheet.PivotTables("PivAuswertung").DataBodyRange
        .Resize(RowSize:=.Rows.Count - 1).Select
    End With
End Sub

If you need to do this more often in your code on more ranges then I suggest to use a function for it:

Option Explicit

Public Function RemoveLastRowsFromRange(ByVal FromRange As Range, Optional ByVal RowCount As Long = 1) As Range
    Set RemoveLastRowsFromRange = FromRange.Resize(RowSize:=FromRange.Rows.Count - RowCount)
End Function

Sub SelectDataBodyRange()
    'removes 1 rows (1 is default)
    RemoveLastRowsFromRange(ActiveSheet.PivotTables("PivAuswertung").DataBodyRange).Select
    'removes 5 rows
    RemoveLastRowsFromRange(ActiveSheet.PivotTables("PivAuswertung").DataBodyRange, 5).Select
End Sub

Just one more note: If you declare Dim rows, cols As Integer only cols is Integer but rows is Variant. In VBA you need to declare a type for every variable or it is Variant by default. Also there are more rows in Excel than fit into an Integer so you must use Long instead: Dim rows As Long, cols As Long. Since there is no benefit in using Integer at all in VBA I recommend always to use Long instead.

@Ugur Note that if you don't have any issues/errors with your code and just want someone to review it or tips for improvement, better post it at https://codereview.stackexchange.com — Pᴇʜ, Feb 26 '20 at 10:20

Select PivotTable DataBodyRange, exclude last row

1 Answers1