Range Names

  • Thread starter Thread starter Gerry
  • Start date Start date
G

Gerry

I want to create a Range Name which will have a different
number of rows in each sheet. I can get the number of rows
but can I store that in a variable and then use the
variable to create a Range name.
 
Create the range name like

=OFFSET($A$1,,,COUNTA($A:$A))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
-----Original Message-----
Create the range name like

=OFFSET($A$1,,,COUNTA($A:$A))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)



Bob
Click to expand...

This is the macro I am trying to use:
Range("I2").Select
Selection.Copy
Range("NewName").Select
ActiveSheet.Paste
Application.CutCopyMode = False
.
I want to copy contects of Cell I2 to a range which will
Click to expand...
be created once I know the last row. I will be able to
get the last row figure but how do I create the range in
the macro.
 
Hi


Gerry said:
This is the macro I am trying to use:
Range("I2").Select
Selection.Copy
Range("NewName").Select
ActiveSheet.Paste
Application.CutCopyMode = False
be created once I know the last row. I will be able to
get the last row figure but how do I create the range in
the macro.
Click to expand...


I think all you need is a code row:
ActiveSheet.Cells(2,9).Copy(ActiveSheet.Cells(ActiveSheet.Cells.Find("*",sea
rchdirection:=xlPrevious).Row+1,9))
 
-----Original Message-----
Hi





I think all you need is a code row:
ActiveSheet.Cells(2,9).Copy(ActiveSheet.Cells (ActiveSheet.Cells.Find("*",sea
rchdirection:=xlPrevious).Row+1,9))


--
Arvi Laanemets
(When sending e-mail, use address arvil<At>tarkon.ee)

Arvi
Click to expand...
Here is what I have, A spreadsheet from A1 to ab8xxxx
(the number of rows can vary). The number and positions of
columns always stay the same.
The number of rows is contained column E in a footer
record denoted by 99 in col A (this is the only row with
99 in col A). So I get the number of rows using lookup
(99,A:,E:E) and this returns the number of rows. So now I
want to insert a new column and copy contents of cell J2
to all the rows in the new column. Problem is when I
select the column to paste in to I do not know how to say
paste for 8xxxx rows. Here is code that works when I
manually user macro recorder:
Columns("J:J").Select
Selection.Insert Shift:=xlToRight
Range("J2").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],4)"
Range("J2").Select
Selection.Copy
Range("J3:J8143").Select
ActiveSheet.Paste
 
Hi

Columns("J:J").Select
Selection.Insert Shift:=xlToRight
Range("J2").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],4)"
ActiveSheet.Range("J2").Copy(ActiveSheet.Range("J3:J" &
ActiveSheet.Cells.Find("*",searchdirection:=xlPrevious).Row))


--
Arvi Laanemets
(When sending e-mail, use address arvil<At>tarkon.ee)




Gerry said:
-----Original Message-----
Hi
Click to expand...
Selection.Insert Shift:=xlToRight
Range("J2").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],4)"
Range("J2").Select
Selection.Copy
Range("J3:J8143").Select
ActiveSheet.Paste
I think all you need is a code row:
ActiveSheet.Cells(2,9).Copy(ActiveSheet.Cells (ActiveSheet.Cells.Find("*",sea
rchdirection:=xlPrevious).Row+1,9))


--
Arvi Laanemets
(When sending e-mail, use address arvil<At>tarkon.ee)

Arvi
Click to expand...
Here is what I have, A spreadsheet from A1 to ab8xxxx
(the number of rows can vary). The number and positions of
columns always stay the same.
The number of rows is contained column E in a footer
record denoted by 99 in col A (this is the only row with
99 in col A). So I get the number of rows using lookup
(99,A:,E:E) and this returns the number of rows. So now I
want to insert a new column and copy contents of cell J2
to all the rows in the new column. Problem is when I
select the column to paste in to I do not know how to say
paste for 8xxxx rows. Here is code that works when I
manually user macro recorder:

.
Click to expand...
Click to expand...
 
-----Original Message-----
Hi

Columns("J:J").Select
Selection.Insert Shift:=xlToRight
Range("J2").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],4)"
ActiveSheet.Range("J2").Copy(ActiveSheet.Range ("J3:J" &
ActiveSheet.Cells.Find ("*",searchdirection:=xlPrevious).Row))
Click to expand...
Excellent Arvi this works fine, many thanks
-----Original Message-----
Hi



-----Original Message-----
Create the range name like> Columns("J:J").Select
Click to expand...
Selection.Insert Shift:=xlToRight
Range("J2").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],4)"
Range("J2").Select
Selection.Copy
Range("J3:J8143").Select
ActiveSheet.Paste
=OFFSET($A$1,,,COUNTA($A:$A))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

message
I want to create a Range Name which will have a
different
number of rows in each sheet. I can get the number of
rows
but can I store that in a variable and then use the
variable to create a Range name.

Bob

This is the macro I am trying to use:
Range("I2").Select
Selection.Copy
Range("NewName").Select
ActiveSheet.Paste
Application.CutCopyMode = False
.
I want to copy contects of Cell I2 to a range which will
be created once I know the last row. I will be able to
get the last row figure but how do I create the range in
the macro.


I think all you need is a code row:
ActiveSheet.Cells(2,9).Copy(ActiveSheet.Cells (ActiveSheet.Cells.Find("*",sea
rchdirection:=xlPrevious).Row+1,9))


--
Arvi Laanemets
(When sending e-mail, use address arvil<At>tarkon.ee)

Arvi
Click to expand...
Here is what I have, A spreadsheet from A1 to ab8xxxx
(the number of rows can vary). The number and positions of
columns always stay the same.
The number of rows is contained column E in a footer
record denoted by 99 in col A (this is the only row with
99 in col A). So I get the number of rows using lookup
(99,A:,E:E) and this returns the number of rows. So now I
want to insert a new column and copy contents of cell J2
to all the rows in the new column. Problem is when I
select the column to paste in to I do not know how to say
paste for 8xxxx rows. Here is code that works when I
manually user macro recorder:

.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top