Everchanging number of rows
in [Programming]
|
Prev: Getting the next higer value with Vlookup - True
Next: importing multiple text files into Excel with corresponding fi
From: JohnUK on 3 Feb 2010 05:58 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 3 Feb 2010 06:06 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 3 Feb 2010 06:05 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 3 Feb 2010 07:05 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 3 Feb 2010 07:09
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] > > > > . > > |