Why doesn't this range work?

P

Pete K

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
 
B

Bob Phillips

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)
 
S

Stephen Rasey

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(SprtshtPosition, 1))
might also work.

Stephen Rasey
WiserWays, LLC
Houston, TX
 
D

David

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 said:
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
Click to expand...
Sprdsht_No_Of_Rows


.
Click to expand...
 
Top