VBA define region

Mar 7th, 2002, 12:39 AM

Hi fellas,

Set LastCell = Range("B65536").End(xlUp)
Set CodeRange = Range(A2, LastCell).

ListBox1.RowSource = "'Officer Code'!a2:b33"

Need help with this... How do I define a range such that it starts from A2 and ends at LastCell ie the Set CodeRange line..

Then I want to set it as the RowSource to the ListBox... Any ideas guys??

Thanks

Ivan F Moala · Mar 7th, 2002, 01:22 AM

Set Lastcell = Range("B65536").End(xlUp)
Set CodeRange = Range("A2", Lastcell.Address)

ListBox1.RowSource = CodeRange.Address

Ivan

Dave Hawley · Mar 7th, 2002, 01:26 AM

Not eaxctly sure I have understood you but, try:

Sheet2.Range("A2", Sheet2.Range("B65536").End(xlUp)).Name = "MyRange"
ListBox1.RowSource = "MyRange"

Mar 7th, 2002, 01:46 AM

Cheers guys

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 7th, 2002, 12:39 AM	#1
Guest Posts: n/a	Hi fellas, Set LastCell = Range("B65536").End(xlUp) Set CodeRange = Range(A2, LastCell). ListBox1.RowSource = "'Officer Code'!a2:b33" Need help with this... How do I define a range such that it starts from A2 and ends at LastCell ie the Set CodeRange line.. Then I want to set it as the RowSource to the ListBox... Any ideas guys?? Thanks

Mar 7th, 2002, 01:22 AM	#2
Ivan F Moala MrExcel MVP Join Date: Feb 2002 Location: Auckland, New Zealand Posts: 4,209	Set Lastcell = Range("B65536").End(xlUp) Set CodeRange = Range("A2", Lastcell.Address) ListBox1.RowSource = CodeRange.Address Ivan

Mar 7th, 2002, 01:26 AM	#3
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Not eaxctly sure I have understood you but, try: Sheet2.Range("A2", Sheet2.Range("B65536").End(xlUp)).Name = "MyRange" ListBox1.RowSource = "MyRange"