Populate Listbox from Named Range No Blanks

[citycreek]

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...

 

[P Sitaram]

Try:

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

If the entries are text use CountA instead of Count

 

[citycreek]

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.

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

 

[Harvey]

haha, it's not only with your form it stays empty, I tried it too only don't know why tried to get it working, but I didnt succeed, I'll take a further look at it tomorrow.

 

[P Sitaram]

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:

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]

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]

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]

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:

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]