ListBox Load Every Visible Cell

ptrussell2009 · November 22nd, 2008, 11:22 PM

After I filter the data in Column A, I need to load ListBox1 with all of the visible cells in Column B until there is an empty cell in Column B. Is this even possible?

This is what I have been trying so far, and the problems I am having are that it is still reading the hidden cells and it is also deleting the row that is active and ListBox1 does not fill with anything even though the MsgBox has an output.

Sub ListBoxLoad()

ActiveCell.Offset(1, 0).Select
Do
ActiveCell.EntireRow.Hidden = True
ActiveCell.Offset(1, 0).Select
MsgBox ActiveCell
UserForm1.ListBox1.Value = ActiveCell
Loop Until ActiveCell = ""

End Sub

Keith Smith · November 26th, 2008, 01:43 PM

Here's 1 possible approach. I created a simple Userform and added a list box. The following code goes in the form's module:

Code:

Private Sub GetFilteredList(ByRef CriteriaRange As Range, ByRef ListRange As Range)

    Dim r As Integer

    If CriteriaRange.Rows.Count <> ListRange.Rows.Count Then
        Debug.Print "The criteria and list ranges must be of equal length."
        Exit Sub
    End If

    For r = 1 To CriteriaRange.Rows.Count
        If CriteriaRange.Cells(r, 1).Value >= 10 Then
            ListBox1.AddItem ListRange.Cells(r, 1).Value
        End If
    Next

End Sub

Private Sub UserForm_Initialize()
    With Worksheets("Sheet1")
        GetFilteredList .Range("A1:A18"), .Range("B1:B18")
    End With
End Sub

The sub FilteredList accepts two range parameters, representing a criteria column and the resultant list column. After checking that the two range parameters passed are of equal height, it them loops through each row in the criteria column. Within the loop, I have added a simple condition, whereby if the current cell in the criteria column is >= 10, add the corresponding list column's cell value to the ListBox control.

The sub is called within the form's Initialize event, passing in a couple of ranges from Sheet1.

This is just one of many ways you could do this.

HTH

zafarjanjua · February 22nd, 2016, 05:42 AM

Quote:

Originally Posted by Keith Smith

Here's 1 possible approach. I created a simple Userform and added a list box. The following code goes in the form's module:

Code:

Private Sub GetFilteredList(ByRef CriteriaRange As Range, ByRef ListRange As Range)

    Dim r As Integer

    If CriteriaRange.Rows.Count <> ListRange.Rows.Count Then
        Debug.Print "The criteria and list ranges must be of equal length."
        Exit Sub
    End If

    For r = 1 To CriteriaRange.Rows.Count
        If CriteriaRange.Cells(r, 1).Value >= 10 Then
            ListBox1.AddItem ListRange.Cells(r, 1).Value
        End If
    Next

End Sub

Private Sub UserForm_Initialize()
    With Worksheets("Sheet1")
        GetFilteredList .Range("A1:A18"), .Range("B1:B18")
    End With
End Sub

The sub FilteredList accepts two range parameters, representing a criteria column and the resultant list column. After checking that the two range parameters passed are of equal height, it them loops through each row in the criteria column. Within the loop, I have added a simple condition, whereby if the current cell in the criteria column is >= 10, add the corresponding list column's cell value to the ListBox control.

The sub is called within the form's Initialize event, passing in a couple of ranges from Sheet1.

This is just one of many ways you could do this.

HTH

Thanks for code its realy helpful for me
i am neweb on this forum i need help
case is same

but i want to display 4 column A B C D in my list box while in the above case only one column result show in listbox

waiting for your reply

regard

Zafar Janjua