combobox list of sheet2

[tkraju via OfficeKB.com]

this code is giving active sheet list.
LastRow = Range("H3").End(xlDown).Row
myList = Range(Cells(3, 8), Cells(LastRow, 14)).Address
what change is to be done to the above code if I want "sheet2 " list with
same parameters of above code.

 

[joel]

Use Address with external = true. this gives the workbook as well so I added
code to remove the workbook.

LastRow = Range("H3").End(xlDown).Row
MyList = Range(Cells(3, 8), Cells(LastRow, 14)).Address(external:=True)
'remove workbook
MyList = Mid(MyList, InStr(MyList, "]") + 1)

 

[Patrick Molloy]

with worksheets("sheet2")
LastRow = .Range("H3").End(xlDown).Row
myList = .Range(.Cells(3, 8), .Cells(LastRow, 14)).Address
end with

fyi
this is the same as

LastRow = worksheets("sheet2").Range("H3").End(xlDown).Row
myList = worksheets("sheet2").Range(.Cells(3, 8),
worksheets("sheet2").Cells(LastRow, 14)).Address

but from a code perspective, its much cleaner IMHO

 

[Dave Peterson]

Dim myList as string
Dim LastRow as string

With worksheets("Sheet2")
lastrow = .range("H3").end(xldown).row
mylist = .range("H3:H" & lastrow).address(external:=true)
end with

I'd leave the workbook name, worksheet name, and address in that string. It
won't ever hurt and without it, you may have trouble.

 

[tkraju via OfficeKB.com]

Thank you,joel.its great,worked perfectly.

Use Address with external = true. this gives the workbook as well so I added
code to remove the workbook.

LastRow = Range("H3").End(xlDown).Row
MyList = Range(Cells(3, 8), Cells(LastRow, 14)).Address(external:=True)
'remove workbook
MyList = Mid(MyList, InStr(MyList, "]") + 1)

this code is giving active sheet list.
LastRow = Range("H3").End(xlDown).Row
myList = Range(Cells(3, 8), Cells(LastRow, 14)).Address
what change is to be done to the above code if I want "sheet2 " list with
same parameters of above code.

 

[tkraju via OfficeKB.com]

Thank you Dave.perfectly worked.

Dim myList as string
Dim LastRow as string

With worksheets("Sheet2")
lastrow = .range("H3").end(xldown).row
mylist = .range("H3:H" & lastrow).address(external:=true)
end with

I'd leave the workbook name, worksheet name, and address in that string. It
won't ever hurt and without it, you may have trouble.

this code is giving active sheet list.
LastRow = Range("H3").End(xlDown).Row

[quoted text clipped - 5 lines]

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200905/1