Hello Bigmedear friends,
kindly help me how to show data in range of column into listbox but only cells with value, thank you.
regards,
bigMe
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
Sub showList()
UserForm1.Show
End Sub
Hello 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.View attachment 610
regards,
bigMe
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
Hi 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.View attachment 610
regards,
bigMe
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
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
Dear Bigme,wow...amazing....
thank you very much, what you share beyond my expectation
best regards,
bigMe