Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 3rd 10, 11:58 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 173
Default 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   Report Post  
Old February 3rd 10, 12:05 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
Posts: 8,520
Default 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   Report Post  
Old February 3rd 10, 12:06 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2010
Posts: 1
Default 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   Report Post  
Old February 3rd 10, 01:05 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 173
Default 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   Report Post  
Old February 3rd 10, 01:09 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
Posts: 8,520
Default 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   Report Post  
Old February 3rd 10, 02:19 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2010
Posts: 1
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Inserting a number of rows based on the number of columns filled bytext values zorakramone Excel Programming 4 August 3rd 09 08:21 AM
Fill Cells with same number in three rows then skip to next number Tracy Excel Worksheet Functions 2 November 7th 08 04:12 PM
Get number of rows that data uses, including blank rows Denham Coote Excel Discussion (Misc queries) 5 August 22nd 06 02:10 PM
Get number of rows that data takes up, including blank rows Denham Coote Excel Worksheet Functions 2 August 21st 06 09:18 AM
flexible paste rows function that inserts the right number of rows marika1981 Excel Discussion (Misc queries) 1 February 18th 05 03:40 AM


All times are GMT +1. The time now is 04:50 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017