Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting a number of rows based on the number of columns filled bytext values | Excel Programming | |||
Fill Cells with same number in three rows then skip to next number | Excel Worksheet Functions | |||
Get number of rows that data uses, including blank rows | Excel Discussion (Misc queries) | |||
Get number of rows that data takes up, including blank rows | Excel Worksheet Functions | |||
flexible paste rows function that inserts the right number of rows | Excel Discussion (Misc queries) |