Filtering blank cells in column C and hide rows from A:B:C Columns before populate listbox

Alex Piotto

Board Regular
Joined
Jul 5, 2016
Messages
82
Office Version
  1. 2007
Platform
  1. Windows
Hi! Here we go again...
Ii have 3 column of data. A, B, C. From row 2 to 200.
I have also a listbox, where i can show the values from the 3 columns at easy.
But column C sometimes may not have a value. It will be blank.
And in the listbox I can see all the rows from 2 to 200 (actually until the last row with value) even if there is a blank in C.
What I would like to achieve is show in the listbox only the rows that have a value in column C.
Here is the code that shows everything. Is not entirely mine. Part scavenging, part modifying...

VBA Code:
Sub VERIFICA()
'Worksheets("INSERZIONE").ListBox1.Visible = True
'Worksheets("INSERZIONE").CommandButton1.Visible = True
'Worksheets("INSERZIONE").CommandButton2.Visible = True

Dim rngName As Range
Dim ws As Worksheet
Dim i As Integer
Set ws = Worksheets("INSERZIONE")
Worksheets("INSERZIONE").ListBox1.Clear
Worksheets("INSERZIONE").ListBox1.ColumnCount = 3

Dim LastRow As Long
LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
For i = 1 To LastRow

If ws.Cells(i, 1).Value <> vbNullString Then Worksheets("INSERZIONE").ListBox1.AddItem ws.Cells(i, 1).Value

If ws.Cells(i, 2).Value <> vbNullString Then Worksheets("INSERZIONE").ListBox1.List(i - 1, 1) = ws.Cells(i, 2).Value

If ws.Cells(i, 3).Value <> vbNullString Then Worksheets("INSERZIONE").ListBox1.List(i - 1, 2) = ws.Cells(i, 3).Value

Next i
  
Worksheets("INSERZIONE").ListBox1.Selected(0) = True

End Sub
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Alex Piotto

Board Regular
Joined
Jul 5, 2016
Messages
82
Office Version
  1. 2007
Platform
  1. Windows
Well... after some depressing waiting... and some deep thinking, I got my solution. Not very scientific perhaps...
I created a new sheet. It will be hidden from users. Like a scrapbook... It is named APPOGGIO.
I sent the 3 columns there. I ran a little macro there to delete the rows that had blanks in C.
I sent the result back to populate the listbox. YEAH!
@ the end, I also cleared all in the APPOGGIO sheet, to be ready for next time and avoid the error because there are no blank cells.
Giving up is not for me!
:))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,246
Messages
5,595,044
Members
413,963
Latest member
teggl97

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