How do I select only specific rows from a named range into a listbox

Massimo Scola
Massimo Scola used Ask the Experts™
on
Hi

I have a dynamic named range which contains data from 10 colums whose data I add to a listbox: I then add this data to the listbox

[b]=OFFSET(Shops!$B$4;;;COUNTA(Shops!$C:$C)-1;10)

Open in new window


I then add this data to the listbox:
Variable: Dim rngSourceShops As Range

Code to add values to listbox:    

With lbtarget
        'Determine number of columns
        .ColumnCount = 10
        'Set column widths
        .ColumnWidths = "0;130;0;110;0;70;80;80;80;10"
        'Insert the range of data supplied
        .List = rngSourceShops.Cells.Value
    End With

Open in new window


The last column contains a value of either "Yes" or "No". I need to display the shops with "yes" only. What code do you suggest I use?

Do I need to create a new named range or is there a way to filter the data with VBA?

I am unfortunately damned to use Excel. :-(

Thanks for your help. I really appreciate it.

Massimo

PS: Should I upload the spreadsheet to my question?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I seem to remember that there is a listbox.additem method. Referenced here: http://www.java2s.com/Code/VBA-Excel-Access-Word/Forms/AdditemtoListBox.htm

You could make a loop something like this:
dim i as integer

i = 1

for i = 1 to [Max number of items to add]
     if [variable representing column with yes/no value for each row] = "yes" then
          listbox.additem [information you want to add]
     end if
next i
Massimo ScolaSoftware Engineer

Author

Commented:
I've tried your formula.
I first count how many items are in the named range:

Dim x As Integer
x = Range("Shops").Count

Open in new window


In my case, there are 19 shops.

In your code, you wrote:

    
 if [variable representing column with yes/no value for each row] = "yes" then
          listbox.additem [information you want to add]
     end if

Open in new window


How do I get the value in the last column(10)?
It depends on the range. Since you have a counter, i, it would not be hard to name the first cell in the yes/no column and use the offset function to grab the value as you iterate through the loop.

For instance, rngYesNoStart.offset(i,0).value

You may have to adjust the counting range... for instance, the way it is now, i starts iterating through the loop at value 1 and we set it to 1 before the loop starts. As such, when you take the offset as written (simplified) above, you will get the yes/no value from the row beneath the row you are adding information from. You can do two things-- you can iterate with i starting at 0 and going to the total count minus 1 (for i = 0 to totalcount - 1) or you can change the offset to correct for the, er, offset. Like so: rngYesNoStart.offset(i-1,0).value

There are probably lots of other ways to do it, too, but that is one suggestion. If it doesn't suit your fancy, I encourage you to investigate the methods that are already available for ranges. There might be something in the .rows and .columns methods that allows you to reference a specific cell within a range. With a question like this, the best answer  you will get is the one you find yourself-- you're not likely to forget it!
Massimo ScolaSoftware Engineer

Author

Commented:
Here is an example

I need to display the cities with "yes" only.
Hope this helps. Have been trying to figure this out for several hours.

Example.xlsm
Have a look at this code. I believe it does what you requested. Example.xlsm
Massimo ScolaSoftware Engineer

Author

Commented:
that's great - thanks a lot!
I've got another question regarding items in a listbox: how do I change the values in the listbox..
I've created a new question - thanks a lot.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial