VBA - Select Range for Pivot Table (Not including Last Row)

[jhlim]

Hi Excel Experts,

I would like to have your advise on how to select range for a pivot table that not including the last row because it is the "Total". I tried below methods but not worked.

1) This method will including the row 6 (Final row-Total)
Selection.End(xlDown).Select
Range("A2:S2" & Range("B" & Rows.Count)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

2) A5 may not always being the last 2 row data, sometime we could have until A7,A8
Range("A1").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range("A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlUp)).Select

From the screenshot, I would like to select the range (C2:R5), the R5 would be variable (Sometime could be R6 or R7).

Thank you for your helps!!

 

[MARK858]

Not knowing what is in column B maybe...

Sub chgRng()
Range("c2").CurrentRegion.Resize(Range("c2").CurrentRegion.Rows.Count - 1).Select
End Sub

 

[jhlim]

Not knowing what is in column B maybe...

Sub chgRng()
Range("c2").CurrentRegion.Resize(Range("c2").CurrentRegion.Rows.Count - 1).Select
End Sub

Thank you Mark! It is works to select the range.