Everchanging number of rows
 

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

Everchanging number of rows
 

Try the below..

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

--
Jacob


"JohnUK" wrote:

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

Everchanging number of rows
 

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)


JohnUK;631804 Wrote:
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/sh...d.php?t=175897

Microsoft Office Help

Everchanging number of rows
 

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

"joel" wrote:


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)


JohnUK;631804 Wrote:
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/sh...d.php?t=175897

Microsoft Office Help

.

Everchanging number of rows
 

Joel meant to say

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

--
Jacob


"JohnUK" wrote:

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

"joel" wrote:


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)


JohnUK;631804 Wrote:
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/sh...d.php?t=175897

Microsoft Office Help

.

Everchanging number of rows
 

The count method will not be accurate unless you offset the count by the
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.


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

Microsoft Office Help