From: JohnUK on
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

From: joel on

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/showthread.php?t=175897

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

From: Jacob Skaria on
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
>
From: JohnUK on
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/showthread.php?t=175897
>
> [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
>
> .
>
From: Jacob Skaria on
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/showthread.php?t=175897
> >
> > [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
> >
> > .
> >