Using PivotSelect method to find the top of a range

G

Geoff

Hi

I have the following code, which selects the range containing all the labels
of the row field "Combination" in the pivot table "Combinations". It then
sets the value of lngRowStart as the row number of the active cell - this
should be the top (i.e. first) of the labels in this range.

ActiveSheet.PivotTables("Combinations").PivotSelect "Combination[All]",
xlLabelOnly
lngRowStart = ActiveCell.Row
Do While ActiveCell.Value <> ""
lngRowEnd = ActiveCell.Row
ActiveCell.Offset(1, 0).Activate
Loop

The problem is that frequently the top of the range is not selected.
Sometimes the active cell is the bottom (i.e. last) of the labels in the
range, meaning that the lngRowStart is assigned the same value as lngRowEnd
(not ideal in the context of this macro).

Can anyone suggest either an alternative way to get the top and bottom row
numbers of this range, or an alternative argument for the PivotSelect method
to specify that the top cell has to be active in the selection?

Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Code not working in Excel 2007 1
Format column widths from one table to a separate table on same page 1
VBA word change keys 0
Trying to clean up code that I did through a record macro 1
macro to set bottom and top values for randbetween 6
check if value exists in pivot table 0
Code to Concat a row in loop 2
" Select method of range class failed" if sheet not on top 4

Top