Reference to worksheet range in R1C1

[JohanF]

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!

[aidan.heritage@virgin.net]

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.

[Dave Peterson]

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

[JohanF]

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

[Dave Peterson]

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

[JohanF]

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]

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

[Dave Peterson]

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").Cells(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

[JohanF]

Thanks Dave!

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

[Tom Ogilvy]

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" <JohanF.26tknc_1145967613.3781@excelforum-nospam.com> wrote in
message news:JohanF.26tknc_1145967613.3781@excelforum-nospam.com...
>
> 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
>