How to make zero to not appear in list box?

HYKE

Active Member
Joined
Jan 31, 2010
Messages
373
Hi,

This code makes me see the list in the userform, however it is also showing the zeroes (as a result of formula).I desire not to see the zeroes in the list as it crowds the list and is pains the eye. Can somebody re-write the code for me to just hide the zeroes from view?

Thanks!
Code:
Private Sub CommandButton3_Click()
Dim lbtarget As MSForms.ListBox
    Dim rngSource As Range
   
    'Set reference to the range of data to be filled
    Set rngSource = Sheet3.Range("A11:BG350")
    
    'Fill the listbox
    Set lbtarget = UserForm3.ListBox2
    With lbtarget
        .ColumnHeads = False
        'Determine number of columns
        .ColumnCount = 59
        'Set column widths
        .ColumnWidths = "40;60;140;0;65;0;0;30;0;0;0;0;0;0;0;0;40;0;0;30;0;0;0;0;0;0;0;0;40;0;0;30;0;0;0;0;0;0;0;0;40;0;0;30;0;0;0;0;0;0;0;40;30;0;35;0;35;35;35;35"
                         
        .List = rngSource.Cells.Value
    End With
   End Sub
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,484
Here's some code to Extract only non-zero values from a range (assign to an array) then pasteing back to a Worksheet range -- maybe instead to as your Listbox source...(??)


Code:
Sub GetNonZeroCells()
Dim Arr()
Dim C As Range
Dim Rng As Range
Dim i As Integer
i = 1
Set Rng = Range("H10:H20") 'The Range under examination
With Rng
For Each C In Rng
    If C<> 0 Then
        ReDim Preserve Arr(i)
        Arr(i) = C.Value
        i = i + 1
    End If
Next C
End With
i = i - 1
ActiveSheet.Range("J10:J" & i + Range("J10").Row() - 1) = Application.Transpose(Arr)
End Sub
Excel Workbook
ABCDEFGHIJK
1
2
3
4
5
6
7
8All MyThe Non-Zero
9ValuesValues Only
10456
113456
120
134567
140
155687
160
176789
180
19
206789
21
Sheet1
Excel 2007
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,534
Maybe...

Code:
Option Explicit

Private Sub CommandButton3_Click()

    Dim lbtarget As MSForms.ListBox
    Dim rngSource As Range
    Dim i As Long
    Dim j As Long
   
    'Set reference to the range of data to be filled
    Set rngSource = Sheet3.Range("A11:BG350")
    
    'Fill the listbox
    Set lbtarget = UserForm3.ListBox2
    
    With lbtarget
        .RowSource = ""
        .ColumnHeads = False
        'Determine number of columns
        .ColumnCount = 59
        'Set column widths
        .ColumnWidths = "40;60;140;0;65;0;0;30;0;0;0;0;0;0;0;0;40;0;0;30;0;0;0;0;0;0;0;0;40;0;0;30;0;0;0;0;0;0;0;0;40;0;0;30;0;0;0;0;0;0;0;40;30;0;35;0;35;35;35;35"
        j = 0
        For i = 1 To rngSource.Rows.Count
            'Check for 0 values in the second column
            If rngSource(i, 2) <> 0 Then
                'Add the value from the first column to the listbox
                .AddItem rngSource(i, 1)
                'Add the value from the second column to the listbox
                .List(j, 1) = rngSource(i, 2)
                j = j + 1
            End If
        Next i
    End With
    
   End Sub
 

HYKE

Active Member
Joined
Jan 31, 2010
Messages
373
Hi,

Thanks for the reply and the codes. However,the zeroes still appears in the list box. I want to show my listbox (with the userform) but I don't know how to attach it. Can you guide me to do it?
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,534
Which column contains the zero values?

Can you post the exact code that you're using?
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,993
Perhaps this will work for you.

Code:
Private Sub CommandButton3_Click()
    Dim lbtarget As MSForms.ListBox
    Dim rngSource As Range
    Dim i As Long, colNum As Long, ListItem As Variant
    
    Set rngSource = Sheet3.Range("A11:BG350")
    Set lbtarget = UserForm3.ListBox2

    With lbtarget
        .ColumnHeads = False
        'Determine number of columns
        .ColumnCount = 59
        'Set column widths
        .ColumnWidths = "40;60;140;0;65;0;0;30;0;0;0;0;0;0;0;0;40;0;0;30;0;0;0;0;0;0;0;0;40;0;0;30;0;0;0;0;0;0;0;0;40;0;0;30;0;0;0;0;0;0;0;40;30;0;35;0;35;35;35;35"
        
        For i = 1 To rngSource.Rows.Count
            .AddItem ListItem:Rem add dummy item to new line in listbox
            For colNum = 1 To .ColumnCount
                ListItem = CStr(rngSource.Cells(i, colNum).Value)
                If ListItem = "0" Then ListItem = vbNullString
                .List(.ListCount - 1, colNum - 1) = ListItem
            Next colNum
        Next i
        
    End With
    
End Su
b
 

Watch MrExcel Video

Forum statistics

Threads
1,130,346
Messages
5,641,598
Members
417,225
Latest member
LukiiMaxim96

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top