Overall I'm trying to use a variable to set a range so that I can
autofill based on the number of rows in the spreadsheet.

I've set SprdshtPosition as an interger.


Range("I40000") = "=COUNT(R[-39999]C:R[-1]C)"
SprdshtPosition = (Range("I40000") + "1")
'SprdshtPosition will equal only a number ex:3456

Sprdsht_No_Of_Rows = ("A2:A") & SprdshtPosition
'Sprdsht_No_Of_Rows will = A2:A3456


Why doesn't one of the following work?

Selection.AutoFill Destination:=Range("A2:A") & SprdshtPosition
-or-
Selection.AutoFill Destination:=Range Sprdsht_No_Of_Rows

Are you sure that A2 is the selected cell?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Pete K" wrote in message
om...
Overall I'm trying to use a variable to set a range so that I can
autofill based on the number of rows in the spreadsheet.

I've set SprdshtPosition as an interger.


Range("I40000") = "=COUNT(R[-39999]C:R[-1]C)"
SprdshtPosition = (Range("I40000") + "1")
'SprdshtPosition will equal only a number ex:3456

Sprdsht_No_Of_Rows = ("A2:A") & SprdshtPosition
'Sprdsht_No_Of_Rows will = A2:A3456


Why doesn't one of the following work?

Selection.AutoFill Destination:=Range("A2:A") & SprdshtPosition
-or-
Selection.AutoFill Destination:=Range Sprdsht_No_Of_Rows

Did not look real closely at it, but I think
Selection.AutoFill Destination:=Range("A2:A") &
SprdshtPosition

needs to be:
Selection.AutoFill Destination:=Range("A2:A" &
(SprdshtPosition))


-----Original Message-----
Are you sure that A2 is the selected cell?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Pete K" wrote in message
. com...
Overall I'm trying to use a variable to set a range so
that I can
autofill based on the number of rows in the
spreadsheet.

I've set SprdshtPosition as an interger.


Range("I40000") = "=COUNT(R[-39999]C:R[-1]C)"
SprdshtPosition = (Range("I40000") + "1")
'SprdshtPosition will equal only a number ex:3456

Sprdsht_No_Of_Rows = ("A2:A") & SprdshtPosition
'Sprdsht_No_Of_Rows will = A2:A3456


Why doesn't one of the following work?

Selection.AutoFill Destination:=Range("A2:A") &
SprdshtPosition
-or-
Selection.AutoFill Destination:=Range
Sprdsht_No_Of_Rows


.

Thank you, this is the problem. I needed () around the variable.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

You may be looking for the RESIZE property of a range.
Assuming that Sprdsht_No_Of_Rows returns the row number of the bottom of the
range
you want to fill and you want to fill starting at Cell A2, then

Selection.Autofill Destination=Range("A2").Resize(SprtshtPosition-1, 1)

or

Selection.Autofill Destination=Range(Range("A2"),Cells(SprtshtPositio n, 1))
might also work.

Stephen Rasey
WiserWays, LLC
Houston, TX

"Pete K" wrote in message
om...
Overall I'm trying to use a variable to set a range so that I can
autofill based on the number of rows in the spreadsheet.

I've set SprdshtPosition as an interger.


Range("I40000") = "=COUNT(R[-39999]C:R[-1]C)"
SprdshtPosition = (Range("I40000") + "1")
'SprdshtPosition will equal only a number ex:3456

Sprdsht_No_Of_Rows = ("A2:A") & SprdshtPosition
'Sprdsht_No_Of_Rows will = A2:A3456


Why doesn't one of the following work?

Selection.AutoFill Destination:=Range("A2:A") & SprdshtPosition
-or-
Selection.AutoFill Destination:=Range Sprdsht_No_Of_Rows