Home 
Search 
Today's Posts 
#1




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




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




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




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




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




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) 