Populate Listbox from Named Range No Blanks

citycreek

New Member
Joined
Jan 17, 2005
Messages
3
I am brand new to Excel VBA...or VBA altogether, so please bear with me. I am trying to populate a (forms toolbar) listbox with data from a list in worksheet 1 (currently 156 long, but always changing) that is named sub_list. The named range is A2:A300, but I only want to display the nonblank cells. As I mentioned, the length of the list changes daily. All the blank cells are always at the end of the named range. I think this is a very simple task, but can't seem to make it happen. Any help would be appreciated...
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

P Sitaram

Well-known Member
Joined
Jun 24, 2003
Messages
1,974
Try:

Set sub_list = sub_list.Resize(Application.Count(sub_list),1)

If the entries are text use CountA instead of Count
 

citycreek

New Member
Joined
Jan 17, 2005
Messages
3
Can't get anything to work in the Forms Listbox but, this is what my code looks like in a user form:

Private Sub ListBox1_Click()
Set sub_list = sub_list.Resize(Application.CountA(sub_list), 2)
End Sub

When I run the code the listbox is empty. I am so new that I am probably missing something very obvious. Thanks for your help.

:oops: What a newbie who needs sub-100 level training feels like.
 

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
haha, it's not only with your form it stays empty, I tried it too :biggrin: only don't know why :confused: tried to get it working, but I didnt succeed, I'll take a further look at it tomorrow.
 

P Sitaram

Well-known Member
Joined
Jun 24, 2003
Messages
1,974

ADVERTISEMENT

I assumed that sub_list is a variable in your code.

As I understand now, sub_list is a defined name referring to a range in your worksheet. If so, paste this code for your Userform_Activate event:

Code:
Private Sub UserForm_Activate()
    Dim SourceRg As Range
    Set SourceRg = Sheets(1).Range("sub_list")
    Set SourceRg = SourceRg.Resize(Application.Count(SourceRg), 1)
    Me.ListBox1.RowSource = SourceRg.Address
End Sub

Notes:
1. Remove all other code that you have earlier entered.
2. You can get to the Userform_Activate event by:
i. Right click the userform
ii. Choose View Code
iii. Choose Activate event from the right hand drop-down box.
3. If the range has text entries, replace `Count' in the third line with `CountA'.
 

d0wnt0wn

Well-known Member
Joined
Oct 28, 2002
Messages
771
unless you are dead set on coding it you could always use a validation list like this....

name your range... lets say its in column a on sheet 1 ...
go to insert.... name... define..... and type in any name.. lets say myrange

in the refers to box below type in this formula

=offset(sheet1!$a$2,0,0,counta(sheet1!$a:$a),1)


then go to any cell and click on data... validation.....in the allow box select list..... in the source box type in =myrange


this will show you your range with one space at the bottom.... and every time you add to your list the validation list changes with it
 

Chitosunday

Well-known Member
Joined
Jul 14, 2003
Messages
1,017
Psitaram

I think its more appropriate to use counta instead of count as the data could be text so your code should like this.

Private Sub UserForm_Initialize()
Dim SourceRg As Range
Set SourceRg = Sheets(1).Range("sub_list")
Set SourceRg = SourceRg.Resize(WorksheetFunction.CountA(SourceRg), 1)
ListBox1.RowSource = SourceRg.Address
End Sub
 

citycreek

New Member
Joined
Jan 17, 2005
Messages
3
Thanks everyone for the help. I tried the userform code and it worked the first time, then I tried it again and I ended up with a huge amount of blank space at the end of my list??? But, I think I can work through that.

The validation method works also. Then I can use the vlookup function to carry the rest of the information throughout my workbook. Thanks.

As it turns out I spent most the day yesterday researching Mr. Excel's book "VBA andMacros for Microsoft Excel". I found some code there that worked for me with some minor tweaking, but you all performed the same task with fewer lines. Here it is if you're curious:


Code:
Private Sub CancelButton_Click()
    Sheets("User Interface").Select
    Unload Me
End Sub

Private Sub OKButton_Click()
    Sheets("Fax Form").Select
    Unload Me
End Sub

Private Sub UserForm_Initialize()

    Sheets("Fax Form").Select
    Range("J1").Select
    Selection.ClearContents
    Sheets("Customers").Select
    
    Dim IRange As Range
    Dim ORange As Range
    
    'Find the size of today's dataset
    FinalRow = Cells(65536, 1).End(xlUp).Row
    NextCol = Cells(1, 255).End(xlToLeft).Column + 2
    
    'Set up output range.  Copy heading from D1 there
    Range("E1").Copy Destination:=Cells(1, NextCol)
    Set ORange = Cells(1, NextCol)
    
    'Define the Input Range
    Set IRange = Range("A1").Resize(FinalRow, NextCol - 2)
    
    'Do the Advanced Filter to get unique list of customers
    IRange.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ORange, Unique:=True
    
    'Determine how many unique customers we have
    LastRow = Cells(65536, NextCol).End(xlUp).Row
    
    'Sort the data
    Cells(1, NextCol).Resize(LastRow, 1).Sort Key1:=Cells(1, NextCol), _
        Order1:=xlAscending, Header:=xlYes
        
With Me.lbCust
    .RowSource = ""
    .List = Cells(2, NextCol).Resize(LastRow - 1, 1).Value
    'Place the ListIndex into cell a10

End With
    Sheets("Fax Form").Select
    Range("J1").Select
    lbCust.ControlSource = "j1"
    lbCust.BoundColumn = 0
    Sheets("Customers").Select

    'Erase the temporary list of customers
    Cells(1, NextCol).Resize(LastRow, 1).Clear
End Sub

I then used a module to show the userform. In order to add and delete items from my list, I recorded a macro opening the Data Form.

Needless to say the reading was a little heavy for a beginner but I made it through. Thanks again everyone for the help, I sure appreciate it.
[/code]
 

Forum statistics

Threads
1,147,510
Messages
5,741,577
Members
423,668
Latest member
Audorin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top