Adding a variable into a range declaration
 

Here is my defined variable:

Dim RowCount As Long, myRange As Range
Set myRange = Columns("A:A")
RowCount = Application.WorksheetFunction.CountA(myRange)

I am trying to apply the RowCount variable in two situations:

a) copying a formula down the entire length of column B to last row:

Range("B8").Select
ActiveCell.FormulaR1C1 = "The details of my formula"
Range("B8").Select
Selection.Copy
Range("B9:B1000").Select
ActiveSheet.Paste

I'm trying to replace B1000 with a reference to the variable RowCount.
I've tried... Range("B9:B(RowCount)").Select ... and it doesn't work.

b) creating a new pivot table:

Range("A7:BB1000").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Raw Data'!R7C1:R1000C54").CreatePivotTable TableDestination:="", TableName _
:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

I'm trying to replace "A7:BB1000" and "R7C1:R1000C54" with their VB counter-part that references the variable RowCount.

Somehow I'm thinking that I'm looking right past the obvious. :(

Please help me figure this out...

Adding a variable into a range declaration
 

a)

Range("B8:b" & rowcount).formula = "The details of my formula"

b)

Range("A7:BB" & rowcount).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Raw Data'!R7C1:R" & rowcount & "C54").CreatePivotTable
TableDestination:="", TableName _
:="PivotTable1", DefaultVersion:=xlPivotTableVersion10


Malvaro wrote:

Here is my defined variable:

Dim RowCount As Long, myRange As Range
Set myRange = Columns("A:A")
RowCount = Application.WorksheetFunction.CountA(myRange)

I am trying to apply the RowCount variable in two situations:

a) copying a formula down the entire length of column B to last row:

Range("B8").Select
ActiveCell.FormulaR1C1 = "The details of my formula"
Range("B8").Select
Selection.Copy
Range("B9:B1000").Select
ActiveSheet.Paste

I'm trying to replace B1000 with a reference to the variable RowCount.
I've tried... Range("B9:B(RowCount)").Select ... and it doesn't work.

b) creating a new pivot table:

Range("A7:BB1000").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
"'Raw Data'!R7C1:R1000C54").CreatePivotTable
TableDestination:="", TableName _
:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

I'm trying to replace "A7:BB1000" and "R7C1:R1000C54" with their VB
counter-part that references the variable RowCount.

Somehow I'm thinking that I'm looking right past the obvious. :(

Please help me figure this out...

--
Malvaro

--

Dave Peterson

Thanks for all your help!!! :)