Auto-Select Range

[Ben Allen]

Hi,
I have the following code:
tourref.RowSource = "PriceListsandDestinations!b3:d27"

I want this to read
tourref.RowSource = "PriceListsandDestinations!b3Last Cell with Data in
Column D)"

Any ideas on how to acomplish this?
Thanks
--
Cheers,
Ben

Remove your.tonsils to reply
"You only live once but if you do it right once is enough!"

 

[Ron de Bruin]

Use a dynamic range name Ben

Debra show you how
http://www.contextures.com/xlNames01.html#Dynamic

 

[Ben Allen]

Use a dynamic range name Ben

Debra show you how
http://www.contextures.com/xlNames01.html#Dynamic

Thanks, i did that and used th following formula
=OFFSET(PriceListsandDestinations!$B$4,0,0,COUNTA(PriceListsandDestinations!
$B:$B),3)

But i always got two more rows than i needed so i did:

=OFFSET(PriceListsandDestinations!$B$4,0,0,(COUNTA(PriceListsandDestinations
!$B:$B))-2,3)

But now for some reason, the drop down list will not work, even when i go
back to the B427 formula. This is the code i have:

'On Change of Tourref Drop-Down
Private Sub tourref_Change()
CountryText.Value = tourref.Column(1)
PlaceText.Text = tourref.Column(2)
End Sub

'Loading Form
Private Sub UserForm_Initialize()
tourref.RowSource = "PriceListsandDestinations!b4:d27"
AdultsText.Value = ""
ChildrenText.Value = ""
CoachesText.Value = ""
MinibusesText.Value = ""
TourbusesText.Value = ""
tourref.Value = "Select"
tourref.SetFocus
End Sub

Cheers,
Ben

Remove your.tonsils to reply
"You only live once but if you do it right once is enough!"

 

[Dave Peterson]

how about:

dim myRowSource as Range
with worksheets("pricelistanddestinations")
set myRowsource = .range("b3:b" & .cells(.rows.count,"d").end(xlup).row)
end with

'msgbox myrowsource.address 'just to check

tourref.rowsource = myrowsource.range(external:=true)

 

[Ron de Bruin]

Hi Ben

Use this then(change the sheet name)

=OFFSET(Sheet1!$B$3,0,0,COUNTA(Sheet1!$D:$D)-2,3)

Dave's example is also a good one for you

 

[Ben Allen]

how about:

dim myRowSource as Range
with worksheets("pricelistanddestinations")
set myRowsource = .range("b3:b" &
.cells(.rows.count,"d").end(xlup).row) end with

'msgbox myrowsource.address 'just to check

tourref.rowsource = myrowsource.range(external:=true)

Thanks dave, i've tried this but have encountered a number of problems.
Firstly, the msg box dispays b3:b28 instead of b3:b28 and that only works
after deleting tourref.rowsource = myrowsource.range(external:=true)
into
tourref.rowsource = myrowsource

Then i get an error saying 'Type Mismatch'
Any Ideas?
Thanks Again
--
Cheers,
Ben

Remove your.tonsils to reply
"You only live once but if you do it right once is enough!"

 

[Ben Allen]

set myRowsource = .range("b3:d" &Now displays b3:d3 as needed but i still have the error.
Thanks Again

Cheers,
Ben

Remove your.tonsils to reply
"You only live once but if you do it right once is enough!"

 

[Dave Peterson]

Oops. Typo.

tourref.rowsource = myrowsource.range.address(external:=true)

(sorry)

 

[Dave Peterson]

And glad you got the column stuff fixed. (B vs. D)

 

[Ben Allen]

Oops. Typo.

tourref.rowsource = myrowsource.range.address(external:=true)

(sorry)

Thanks, dont worry im glad for your help, only trouble now is that i get an
error saying 'Compile Error: Argument not Optional' and '.range' is
highlighted, any ideas?

Thanks again for all your help, much appriciated.
--
Cheers,
Ben

Remove your.tonsils to reply
"You only live once but if you do it right once is enough!"

 

[Tom Ogilvy]

you have made a variable name range in your code. You need to
removeit/rename it to something else.

If you put option explicit at the top of your module, this will help you
find it, particularly if you didn't intend to create such a variable name.

 

[Ben Allen]

you have made a variable name range in your code. You need to
removeit/rename it to something else.

If you put option explicit at the top of your module, this will help
you find it, particularly if you didn't intend to create such a
variable name.

Ok thanks, im not really sure how but i got it working anyway thanks though.
I believe i just deleted '.range'.
Thanks everyone, your help has been much appriciated.
--
Cheers,
Ben

Remove your.tonsils to reply
"You only live once but if you do it right once is enough!"

 

[Dave Peterson]

Glad you got it working.

I only had one typo (per post). But each post got worse!

Sorry.