variable range
 

Hi
Does anyone know how to put a variable into a range?

The range is a list in column A which is refreshed when
the workbook is opened.
This list is then loaded into a combobox on a form.

using the range notation
Range("x:y")
x is a constant of A2
and y is the variable mylastcell which has a maximum value
of A138 ie range("A2:A138")

However I only want to display the cells that have
something in them in the combobox, in order not to have
blank spaces at the bottom.

I'm able to return the address of the last cell with
something in it by using
mylastcell = Range("A2").End(xlDown).Address
(ROWABSOLUTE:=False)

However, having obtained a value for mylastcell, I'm
unable to put it into range notation
eg range("A2:mylastcell")

I need your help!

variable range
 

Jeff,

Range("A2:" & mylastcell)

--

HTH

Bob Phillips

"JEFF" wrote in message
...
Hi
Does anyone know how to put a variable into a range?

The range is a list in column A which is refreshed when
the workbook is opened.
This list is then loaded into a combobox on a form.

using the range notation
Range("x:y")
x is a constant of A2
and y is the variable mylastcell which has a maximum value
of A138 ie range("A2:A138")

However I only want to display the cells that have
something in them in the combobox, in order not to have
blank spaces at the bottom.

I'm able to return the address of the last cell with
something in it by using
mylastcell = Range("A2").End(xlDown).Address
(ROWABSOLUTE:=False)

However, having obtained a value for mylastcell, I'm
unable to put it into range notation
eg range("A2:mylastcell")

I need your help!

variable range
 

try a dynamic range name - there's been a lot of info on this.

select A2
then Insert/Names/Define
type something like MyList in the name and in the reference try

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$1000 ),1)

In data validation, set the type to list and the source to =MyList

the dynamic list assumes no gaps and says use A2 down to however many items
are in the range A2:a1000
so if there are 15 altogether you get back A2:A16
adding or removing to/from the end will cause the list to adjust
automatically assuming you have auto calc set.

HTH
--
Patrick Molloy
Microsoft Excel MVP
----------------------------------
"JEFF" wrote in message
...
Hi
Does anyone know how to put a variable into a range?

The range is a list in column A which is refreshed when
the workbook is opened.
This list is then loaded into a combobox on a form.

using the range notation
Range("x:y")
x is a constant of A2
and y is the variable mylastcell which has a maximum value
of A138 ie range("A2:A138")

However I only want to display the cells that have
something in them in the combobox, in order not to have
blank spaces at the bottom.

I'm able to return the address of the last cell with
something in it by using
mylastcell = Range("A2").End(xlDown).Address
(ROWABSOLUTE:=False)

However, having obtained a value for mylastcell, I'm
unable to put it into range notation
eg range("A2:mylastcell")

I need your help!

variable range
 

The solutions outlined here works for me, but I also need to make this a variable range a
NAMED range, and that's where I am having trouble. Can anyone tell me how
(programmatically) to make a highlighted range into0 a named range?

I am a bit marginal on code in Excel, and use recorded materials a lot. THis is what i
have so far, but I am darned if I can make it into a named range.

I know I need something like:

ActiveWorkbook.Names.Add Name:="piggy", RefersToR1C1:= _
"='Alt Flat'!R1C27:R38C38"

But don't appear to know the syntax to make it work!

Sub set_output()
'
' set_output Macro
' Macro recorded 11/20/2003 by John H Baker
'

'
Range("A1:L44").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"N1:N2"), CopyToRange:=Range("AA1:AL51"), Unique:=False
Range("AA1").Select
mylastcell = Range("Al1").End(xlDown).Address(ROWABSOLUTE:=Fals e)
Range("AA1:" & mylastcell).Select
End Sub


Thanks

John Baker

"JEFF" wrote:

Hi
Does anyone know how to put a variable into a range?

The range is a list in column A which is refreshed when
the workbook is opened.
This list is then loaded into a combobox on a form.

using the range notation
Range("x:y")
x is a constant of A2
and y is the variable mylastcell which has a maximum value
of A138 ie range("A2:A138")

However I only want to display the cells that have
something in them in the combobox, in order not to have
blank spaces at the bottom.

I'm able to return the address of the last cell with
something in it by using
mylastcell = Range("A2").End(xlDown).Address
(ROWABSOLUTE:=False)

However, having obtained a value for mylastcell, I'm
unable to put it into range notation
eg range("A2:mylastcell")

I need your help!

variable range
 

Selection.Name = "piggy"


is the easiest.

--
Regards,
Tom Ogilvy


John Baker wrote in message
...
The solutions outlined here works for me, but I also need to make this a
variable range a
NAMED range, and that's where I am having trouble. Can anyone tell me how
(programmatically) to make a highlighted range into0 a named range?

I am a bit marginal on code in Excel, and use recorded materials a lot.
THis is what i
have so far, but I am darned if I can make it into a named range.

I know I need something like:

ActiveWorkbook.Names.Add Name:="piggy", RefersToR1C1:= _
"='Alt Flat'!R1C27:R38C38"

But don't appear to know the syntax to make it work!

Sub set_output()
'
' set_output Macro
' Macro recorded 11/20/2003 by John H Baker
'

'
Range("A1:L44").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range( _
"N1:N2"), CopyToRange:=Range("AA1:AL51"), Unique:=False
Range("AA1").Select
mylastcell = Range("Al1").End(xlDown).Address(ROWABSOLUTE:=Fals e)
Range("AA1:" & mylastcell).Select
End Sub


Thanks

John Baker

"JEFF" wrote:

Hi
Does anyone know how to put a variable into a range?

The range is a list in column A which is refreshed when
the workbook is opened.
This list is then loaded into a combobox on a form.

using the range notation
Range("x:y")
x is a constant of A2
and y is the variable mylastcell which has a maximum value
of A138 ie range("A2:A138")

However I only want to display the cells that have
something in them in the combobox, in order not to have
blank spaces at the bottom.

I'm able to return the address of the last cell with
something in it by using
mylastcell = Range("A2").End(xlDown).Address
(ROWABSOLUTE:=False)

However, having obtained a value for mylastcell, I'm
unable to put it into range notation
eg range("A2:mylastcell")

I need your help!

variable range
 

Thank you..that was just what I needed.


"Tom Ogilvy" wrote:


Selection.Name = "piggy"