Range in Last column and Last Row in excel VBA for pivot

[BMD44]

Hi,

I am trying to pivot a table in excel.

The pivot works when the data starts from cell(1,1).

But when the placement of data cells change, the code does not create pivot.

Could you suggest the data range that needs to be changed for the code. Attached the file.

Data source starts from Row 7 and Col R (Cols R,S T,U)

Here is the script:

'Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

 

[MARK858]

Try

LastRow = Cells(Rows.Count, "R").End(xlUp).Row
LastCol = Cells(7, Columns.Count).End(xlToLeft).Column
Set PRange = Cells(7, "R").Resize(LastRow - 6, LastCol - 17)

 

[BMD44]

Try

LastRow = Cells(Rows.Count, "R").End(xlUp).Row
LastCol = Cells(7, Columns.Count).End(xlToLeft).Column
Set PRange = Cells(7, "R").Resize(LastRow - 6, LastCol - 17)

Thanks much for the quick response. This works.

 

[MARK858]

You're welcome