Not showing blank cells in user form list box

  • Thread starter Thread starter rob nobel
  • Start date Start date
R

rob nobel

The following bit of code populates a UserForm. I would like that none of
the blank rows in C5:D14 be shown in the uf. (The cells in column D will
always be blank if the corresponding row in column C is blank.)

Q1. Can anyone suggest how I might achieve this?
Q2. Is it possible to also have another range, namely ("C21:D25") added to
the list box so that the list box is populated by ("C21:D25") and ("C5:D14")
without any blank rows showing? I have generally used PivotTables to sort
the ranges and eliminate blanks, but I wonder if there is a better way.

If ActiveCell = Sheet7.Range("E24").Value Then
ufSpecialProjects.ListBox1.List _
= Sheet14.Range("C5:D14").Resize(, 2).Value
ufSpecialProjects.Show
 
Instead of picking up the range all at once, just add the items you want:

Option Explicit
Private Sub UserForm_Initialize()

Dim myRng As Range
Dim myCell As Range
Set myRng = Sheet7.Range("c21:d25,c5:d14")

For Each myCell In myRng.Cells
If Trim(myCell.Value) = "" Then
'do nothing
Else
ufSpecialProjects.ListBox1.AddItem myCell.Value
End If
Next myCell

End Sub
 
Thanks Dave, I guessed it would need a loop of some sort which I'm having a
bit of trouble inventing myself.
Rob
 
Back
Top