looking for macro code to create dynamic range

[deuxo]

I just learned I can not use OFFSET in VBA, I am therefore looking for macro code that will create a dynamic range for running a pivot table. thanks.

 

[Ricky Morris]

You can set up a dynamic range and assign it to a range name then used that range name in your macro.

See the following regarding setting up a dynamic range.

http://www.ozgrid.com/Excel/DynamicRanges.htm

 

[Ivan F Moala]

On 2002-04-18 12:00, deuxo wrote:
I just learned I can not use OFFSET in VBA, I am therefore looking for macro code that will create a dynamic range for running a pivot table. thanks.

Yes you can use OFFSET in VBA.
If you explain further what you are trying
to do then perhaps someone can help you.

 

[nisht]

i think poster wants to say offset function avialable in worksheet...to be used in vba code.

Yes you can put formula with offset function by vba code..but i think poster does not want that.

i think that feature is not avialable in vba code.. ofcourse you can use standard offset function available to move around the cell.

well for your dynamic range if you want to use in vba code may you can get some idea.. from my file nos. 16 'populating combobox'

http://www.pexcel.com/download.htm

here i have used dynamic range to populate all the combobox.

if you want to use in formula you can ofcourse use offset function..

=SUM(OFFSET(G3,0,0,COUNTA(G:G)))

you modify above formula..to your need.

 

[RET79]

I don;t know if this is the sort of thing you want to be doing, but, I am in the middle of developing pivot tables based on dynamic ranges too, take a look at this. Here I have actually made the dynamic range into an array and looped to create the table, take a look


Set rngcolhead = Range(Cells(1, p), [IV1].End(xlToLeft))
z = rngcolhead.Cells.count
arrcolhead = rngcolhead

For k = 1 To z Step 1
Debug.Print arrcolhead(1, k)


Application.StatusBar = "Calculating pivot field " & k & " of " & z

Application.Calculation = xlManual
Sheets("PivotSheet").Select

With ActiveSheet.PivotTables("PivotTable1").PivotFields(arrcolhead(1, k))
.Orientation = xlDataField
.Function = xlSum
.Caption = "Sum of " & arrcolhead(1, k)
.Position = k

End With

Next k


Hope this helps,

RET79