Reference to worksheet range in R1C1
 

Hi,

This is a stupid question or at least, I'm sure it must have a simpl
answer, yet I cannot find it in the help files:

A want to reference a worksheet range from Visual Basic and load
listbox with it. The records in the range varies and I don't want t
load the blank lines at the bottom of the range into the listbox.

Thanx for any help

--
Johan
-----------------------------------------------------------------------
JohanF's Profile: http://www.excelforum.com/member.php...fo&userid=3377
View this thread: http://www.excelforum.com/showthread.php?threadid=53551

Reference to worksheet range in R1C1
 

You could use special cells xllastcell to get the last cell, or if you
can be certain that you don't have blanks in your data you could use
the COUNTA function to count the entries - which gives you the
necessary numeric part of the reference. You could even create a
dynamic named range and reference this.

Reference to worksheet range in R1C1
 

I like to do something like:

dim LastRow as long
with worksheets("sheet1")
lastrow = .cells(.rows.count,"A").end(xlup).row
....

Then I can use that for the range I want to use:

dim myRng as range

....

set myrng = .range("a1:e" & lastrow)
or
set myrng = .range("a1:a" & lastrow)
or even
set myrng = .range("a1",.cells(.rows.count,"A").end(xlup))

.....


JohanF wrote:

Hi,

This is a stupid question or at least, I'm sure it must have a simple
answer, yet I cannot find it in the help files:

A want to reference a worksheet range from Visual Basic and load a
listbox with it. The records in the range varies and I don't want to
load the blank lines at the bottom of the range into the listbox.

Thanx for any help!

--
JohanF
------------------------------------------------------------------------
JohanF's Profile: http://www.excelforum.com/member.php...o&userid=33779
View this thread: http://www.excelforum.com/showthread...hreadid=535513

--

Dave Peterson

Reference to worksheet range in R1C1
 

Ok, thanks guys!

That gives me some ideas to work with!

I thought there should be some syntex like: .range(r,c : rr,cc) so that
one don't need the .range("") method, but if you say so, I'll try it the
long way round.

Cheers,

Johan


--
JohanF
------------------------------------------------------------------------
JohanF's Profile: http://www.excelforum.com/member.php...o&userid=33779
View this thread: http://www.excelforum.com/showthread...hreadid=535513

Reference to worksheet range in R1C1
 

You could use:
..range(.cells(r,c),.cells(rr,cc))




JohanF wrote:

Ok, thanks guys!

That gives me some ideas to work with!

I thought there should be some syntex like: .range(r,c : rr,cc) so that
one don't need the .range("") method, but if you say so, I'll try it the
long way round.

Cheers,

Johan

--
JohanF
------------------------------------------------------------------------
JohanF's Profile: http://www.excelforum.com/member.php...o&userid=33779
View this thread: http://www.excelforum.com/showthread...hreadid=535513

--

Dave Peterson

Reference to worksheet range in R1C1
 

I'll try that! [range(.cells(r,c),.cells(rr,cc))]

Why do I get a "Could not set the list property. Invalid property array
index" when I use:

Set UserRng = Worksheets("Macros").Range("a29:b" & lastrow)
cmbUserList.List() = UserRng

Is there not a double quote missing somewhere?


--
JohanF
------------------------------------------------------------------------
JohanF's Profile: http://www.excelforum.com/member.php...o&userid=33779
View this thread: http://www.excelforum.com/showthread...hreadid=535513

Reference to worksheet range in R1C1
 

I get the same error message "Could not set the list property. Invali
property array index" when I use:

Set UserRng = Worksheets("Macros").Range(Cells(29, 1), Cells(lastrow
2))
cmbUserList.List() = UserRng

I see it is actually a combobox and not a listbox, but I don't thin
that's got anything to do with it?? Is there perhaps something I misse
in the settings of the combobox? (ColumnCount = 2)

Thanks again,

Joha

--
Johan
-----------------------------------------------------------------------
JohanF's Profile: http://www.excelforum.com/member.php...fo&userid=3377
View this thread: http://www.excelforum.com/showthread.php?threadid=53551

Reference to worksheet range in R1C1
 

This worked ok for me:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim UserRng As Range
Dim LastRow As Long

With Worksheets("macros")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set UserRng = .Range(.Cells(29, 1), .Cells(LastRow, 2))
End With

With Me.ComboBox1
.ColumnCount = 2
.List = UserRng.Value
End With
End Sub

One of the problems you have is this line:

Set UserRng = Worksheets("Macros").Range(Cells(29, 1), Cells(lastrow, 2))

You have two unqualified range references in that line.
Cells(29,1) and cells(lastrow,2)
don't belong to worksheets("Macros"). They belong to whatever sheet was active.

You could be explicit:

Set UserRng = Worksheets("Macros").Range(worksheets("macros").Ce lls(29, 1), _
worksheets("Macros").Cells(lastrow, 2))

Or you could use the with/end with structure.

And .list is looking for an array of values--not a range object.

JohanF wrote:

I get the same error message "Could not set the list property. Invalid
property array index" when I use:

Set UserRng = Worksheets("Macros").Range(Cells(29, 1), Cells(lastrow,
2))
cmbUserList.List() = UserRng

I see it is actually a combobox and not a listbox, but I don't think
that's got anything to do with it?? Is there perhaps something I missed
in the settings of the combobox? (ColumnCount = 2)

Thanks again,

Johan

--
JohanF
------------------------------------------------------------------------
JohanF's Profile: http://www.excelforum.com/member.php...o&userid=33779
View this thread: http://www.excelforum.com/showthread...hreadid=535513

--

Dave Peterson

Reference to worksheet range in R1C1
 

If I use

Worksheets("Macros").Range(.Cells(29, 1), .Cells(lastrow, 2))

I get a sytax type of error??


--
JohanF
------------------------------------------------------------------------
JohanF's Profile: http://www.excelforum.com/member.php...o&userid=33779
View this thread: http://www.excelforum.com/showthread...hreadid=535513

Reference to worksheet range in R1C1
 

If I use

Worksheets("Macros").Range(.Cells(29, 1), .Cells(lastrow, 2))

I get a sytax type of error??


--
JohanF
------------------------------------------------------------------------
JohanF's Profile: http://www.excelforum.com/member.php...o&userid=33779
View this thread: http://www.excelforum.com/showthread...hreadid=535513

Reference to worksheet range in R1C1
 

Thanks Dave!:)

I've learned a couple of things now and I'm sure it should work. I'l
test it an post another message if I still don't come righ

--
Johan
-----------------------------------------------------------------------
JohanF's Profile: http://www.excelforum.com/member.php...fo&userid=3377
View this thread: http://www.excelforum.com/showthread.php?threadid=53551

Reference to worksheet range in R1C1
 

Dim rng as Range
With Worksheets("Macros")
set rng = .Range(.Cells(29, 1), .Cells(lastrow, 2))
End with

msgbox rng.Address(0,0,xlA1,True)

--
Regards,
Tom Ogilvy

"JohanF" wrote in
message ...

If I use

Worksheets("Macros").Range(.Cells(29, 1), .Cells(lastrow, 2))

I get a sytax type of error??


--
JohanF
------------------------------------------------------------------------
JohanF's Profile:
http://www.excelforum.com/member.php...o&userid=33779
View this thread: http://www.excelforum.com/showthread...hreadid=535513