VBA code for end of sheet (Excel 2000)

Topic

New Reply

I wrote a macro to create a pivot table. The length of the pivot table will vary every month. Currently the range is A1:P1000. What would be a generic code for the end of the sheet? Thank you.

Reply | Quote

Replies

The maximum number of rows is: 65,536 (or 2^16)

Reply | Quote

If you want to get a reference to the table containing A1, you can use Range(“A1”).CurrentRegion.

Alternatively, if column A will always be filled, you can use

Dim lngMaxRow As Long
Dim rng As Range
lngMaxRow = Range(“A65536”).End(xlUp).Row
Set rng = Range(“A1:P” & lngMaxRow)

Reply | Quote

Another approach that I used was to change the pivot table range in Step 2 of 3 of the Wizard. I selected the range to be A1:P65536. This allowed for growth, but the second to the last line of the Pivot table says “(blank)” on every cell. Is there a way to program the pivot to delete a line that has “(blank)”. The blanks throw off the Grand Totals. Thank you.

Reply | Quote

Interactively, you can right-click (blank) and click Hide in the popup menu. In VBA, it would look like this:

ActiveSheet.PivotTables(1).PivotFields(“MyField”).PivotItems(“(blank)”).Visible = False

where MyField is the name of the relevant pivot field.

Reply | Quote

The interactive is great for the macro. thank you.

Reply | Quote

The .DataBodyRange of a pivot table gives you the range that is occupied by the data area of the PT.

Reply | Quote