[Solved] show cells with value in listbox

[bigme]

dear friends,
kindly help me how to show data in range of column into listbox but only cells with value, thank you.

regards,
bigMe

 

[Lutfor Rahman Shimanto]

dear friends,
kindly help me how to show data in range of column into listbox but only cells with value, thank you.

regards,
bigMe

Hello Bigme

Thank you for posting an exciting problem. To achieve your requirement, you must insert a UserForm and a ListBox. However, I add some extra features to decorate the UserForm you may like.

Steps:
Consider a dataset >> insert a button.

​

Open the VBA editor >> insert a UserForm.
Create and design a Label and a ListBox within UserForm.

​

Private Sub UserForm_Initialize()

    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim lstRow As Long
    Dim columnRange As String

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lstRow = Cells(Rows.Count, 2).End(xlUp).Row

    columnRange = "B2:B" & lstRow

    Set rng = ws.Range(columnRange)

    Me.Label1.Caption = ws.Range("B1").Value
   
    For Each cell In rng
        If Not IsEmpty(cell.Value) Then
            Me.ListBox1.AddItem cell.Value
        End If
    Next cell

End Sub

Double-click on the UserForm area and paste the below code.
Now, insert a Module and paste the below code.

Sub showList()
   
    UserForm1.Show
   
End Sub

Later, assign the procedure stored in the module.

​

Output: You can see an overview of the whole feature by clicking the link below.

OUTPUT VIDEO​

I have attached the solution workbook to help you understand better. Feel free to contact us again if you have more queries. Good luck!

Regards
Lutfor Rahman Shimanto

 

[bigme]

Dear Lutfor,
thank you very much for your help...., can i ask for little more favor ?
if i change the source become 2 column and only show into list box the complete one only, which part of the code needs modification? thank you.

regards,
bigMe

 

[Lutfor Rahman Shimanto]

Dear Lutfor,
thank you very much for your help...., can i ask for little more favor ?
if i change the source become 2 column and only show into list box the complete one only, which part of the code needs modification? thank you.View attachment 610

regards,
bigMe

Hello Bigme,

Thanks for your query. You want to show a list of two columns. I can give a modified version of my previous code. That will fulfil your goal. All you need is to replace the UserForm initialize event with the following code.

Sub UserForm_Initialize()
    
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    
    With Me.ListBox1
        .ColumnCount = 2
        .ColumnWidths = "75, 50"
        
        For i = 2 To lastRow
            If ws.Cells(i, "B").Value <> "" And ws.Cells(i, "C").Value <> "" Then
                .AddItem
                .List(.ListCount - 1, 0) = ws.Cells(i, "B").Value
                .List(.ListCount - 1, 1) = ws.Cells(i, "C").Value
            End If
        Next i
    End With

End Sub

However, I will introduce an advanced idea to reach the same idea. When I am done, I will share the idea in this thread.

Regards
Lutfor Rahman Shimanto

 

[Lutfor Rahman Shimanto]

Dear Lutfor,
thank you very much for your help...., can i ask for little more favor ?
if i change the source become 2 column and only show into list box the complete one only, which part of the code needs modification? thank you.View attachment 610

regards,
bigMe

Hi Bigme,

My previous modified code has a limitation. To be more specific, there is no column header in ListBox. To get the output you have shown, I have implemented three sub-procedures named ShowList, FilterAndCopyData and DeleteSheetTemp. All you need is to paste these in module1. Later, insert a button and assign the ShowList macro to it.

Module1:

Sub ShowList()
    
    UserForm1.Show
    
End Sub


Sub FilterAndCopyData()

    Dim ws As Worksheet
    Dim newWs As Worksheet
    Dim lastRow As Long
    Dim filterRange As Range
    Dim copyRange As Range

    Set ws = ThisWorkbook.Sheets("Sheet1")

    Set newWs = ThisWorkbook.Sheets.Add(After:=ws)
    newWs.Name = "sheetTemp"

    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    Set filterRange = ws.Range("B1:C" & lastRow)

    filterRange.AutoFilter Field:=1, Criteria1:="<>", Operator:=xlAnd
    filterRange.AutoFilter Field:=2, Criteria1:="<>"

    filterRange.SpecialCells(xlCellTypeVisible).Copy newWs.Range("A1")

    filterRange.AutoFilter

    newWs.Visible = xlSheetHidden

End Sub

Sub DeleteSheetTemp()

    Dim ws As Worksheet
    
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets("sheetTemp")
    On Error GoTo 0
    
    If Not ws Is Nothing Then
        Application.DisplayAlerts = False
        ws.Delete
        Application.DisplayAlerts = True
    End If

End Sub

Likewise, create an UserForm and insert a ListBox in it. Now, double-click on the UserForm area and paste the following code.

UserForm1:

Sub UserForm_Initialize()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long

    Call Module1.FilterAndCopyData

    Set ws = ThisWorkbook.Sheets("sheetTemp")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    With Me.ListBox1
        .ColumnCount = 2
        .ColumnHeads = True
        .ColumnWidths = "75, 50"
        .rowSource = ws.Name & "!A2:B" & lastRow
    End With

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
 
    Call Module1.DeleteSheetTemp

End Sub

Output:

OUTPUT VIDEO​

I am attaching the solution workbook to help you understand better. If you have any more questions, don't hesitate to contact us.

Regards
Lutfor Rahman Shimanto

 

[bigme]

wow...amazing....
thank you very much, what you share beyond my expectation


best regards,
bigMe

 

[Lutfor Rahman Shimanto]

wow...amazing....
thank you very much, what you share beyond my expectation


best regards,
bigMe

Dear Bigme,

Thank you so much for your kind words! I'm thrilled you found the information shared in the thread very helpful. Your appreciation truly means a lot to me.

Best regards,
Lutfor Rahman Shimanto