Everchanging number of rows

[JohnUK]

Hi,
Where am I going wrong on this piece of code below
What I am trying to achieve in simple terms is this:
Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
Selection.Copy
Range("A2:A10000").Select
ActiveSheet.Paste

But I need it to size with the amount of rows the rest of the data uses
My try:
Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
Selection.Copy
Range("A2:A & Range("Count")").Select
ActiveSheet.Paste

I have already created a Range called ‘Count’ that adds up the rows. For
example, if my data spans 100 rows, I want the formula to also reach 100 rows.
I know this may not make any sense, because I could use formulas etc, but is
the above possible
Many thanks

 

[Jacob Skaria]

Try the below..

Range("A2").Resize(Range("Count").Value) = 1

 

[joel]

to get the last row use this. Note I'm using column B to get the las
row.

LastRow = ("B" & rows.count).end(xlup).row
'if you are just putting a one in the column then do this

Range("A2:A" & LastRow) = 1


To copy the 1 down the column
Range("A2").Copy _
destination:=Range("A2:A" & LastRow)

 

[JohnUK]

Hi Joel,
Many thanks for your help, but I had a message come up saying that
Object_Global Failed. Any ideas
John

to get the last row use this. Note I'm using column B to get the last
row.

LastRow = ("B" & rows.count).end(xlup).row
'if you are just putting a one in the column then do this

Range("A2:A" & LastRow) = 1


To copy the 1 down the column
Range("A2").Copy _
destination:=Range("A2:A" & LastRow)

Hi,
Where am I going wrong on this piece of code below
What I am trying to achieve in simple terms is this:
Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
Selection.Copy
Range("A2:A10000").Select
ActiveSheet.Paste

But I need it to size with the amount of rows the rest of the data
uses
My try:
Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
Selection.Copy
Range("A2:A & Range("Count")").Select
ActiveSheet.Paste

I have already created a Range called ‘Count’ that adds up the
rows. For
example, if my data spans 100 rows, I want the formula to also reach
100 rows.
I know this may not make any sense, because I could use formulas etc,
but is
the above possible
Many thanks

--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=175897

Microsoft Office Help

.

 

[Jacob Skaria]

Joel meant to say

LastRow = Range("B" & rows.count).end(xlup).row

--
Jacob

Hi Joel,
Many thanks for your help, but I had a message come up saying that
Object_Global Failed. Any ideas
John

to get the last row use this. Note I'm using column B to get the last
row.

LastRow = ("B" & rows.count).end(xlup).row
'if you are just putting a one in the column then do this

Range("A2:A" & LastRow) = 1


To copy the 1 down the column
Range("A2").Copy _
destination:=Range("A2:A" & LastRow)

Hi,
Where am I going wrong on this piece of code below
What I am trying to achieve in simple terms is this:
Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
Selection.Copy
Range("A2:A10000").Select
ActiveSheet.Paste

But I need it to size with the amount of rows the rest of the data
uses
My try:
Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
Selection.Copy
Range("A2:A & Range("Count")").Select
ActiveSheet.Paste

I have already created a Range called ‘Count’ that adds up the
rows. For
example, if my data spans 100 rows, I want the formula to also reach
100 rows.
I know this may not make any sense, because I could use formulas etc,
but is
the above possible
Many thanks

--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=175897

Microsoft Office Help

.

 

[joel]

The count method will not be accurate unless you offset the count by th
the start row number being 2. Count is 100 your last row will be 2
(100 - 1) = 101.

this statment is probably giving you the error

Range("A2:A & Range("Count")").Select

the correct way of writing this is

Range("A2:A" & Range("Count").Count).Select


I assume that your named range "Count" is a range of cells