By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,574 Members | 2,960 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,574 IT Pros & Developers. It's quick & easy.

I'd like to clear empty or blank rows in a listbox from my workbook. Can you help me?

P: 1
Hello, I saw that you helped a guy that got the same problema than me. So I guess that you can help me too :) could you?

I have a code to populate data to listbox and filter an specifict item by textbox.
But when it populate all data, blank rows came together and I want to clear them, IDK how can I do this! :X

Here's my code...

Expand|Select|Wrap|Line Numbers
  1. Sub FILTRO_TEXTO()
  2. Application.ScreenUpdating = False
  3. Application.DisplayAlerts = False
  4. Dim Linha, linhalistbox As Integer
  5. Dim valor_celula As String
  6. linhalistbox = 1
  7. Linha = 2
  8. ListBox1.Clear
  9.  
  10. Sheets("Plan1").Select
  11. With Sheets("Plan1")
  12.  
  13.         While .Cells(Linha, 1).Value <> ""
  14.             valor_celula = .Cells(Linha, 1).Value <> ""
  15.             If UCase(Left(valor_celula, Len(TextBox1.Text))) = UCase(TextBox1.Text) Then
  16.             valor_celula = .Cells(Linha, 2).Value <> ""
  17.             If UCase(Left(valor_celula, Len(TextBox2.Text))) = UCase(TextBox2.Text) Then
  18.             valor_celula = .Cells(Linha, 3).Value <> ""
  19.             If UCase(Left(valor_celula, Len(TextBox3.Text))) = UCase(TextBox3.Text) Then
  20.             valor_celula = .Cells(Linha, 4).Value <> ""
  21.             If UCase(Left(valor_celula, Len(TextBox4.Text))) = UCase(TextBox4.Text) Then
  22.             valor_celula = .Cells(Linha, 5).Value <> ""
  23.             If UCase(Left(valor_celula, Len(TextBox5.Text))) = UCase(TextBox5.Text) Then
  24.  
  25.     Me.ListBox1.ColumnWidths = "40;80;300;200"
  26.     Me.ListBox1.ForeColor = RGB(0, 0, 102)
  27.  
  28.     With ListBox1
  29.  
  30.        .AddItem
  31.        .List(0, 0) = "Ticket"
  32.        .List(0, 1) = "Projeto"
  33.        .List(0, 2) = "Descrição"
  34.        .List(0, 3) = "Responsável"
  35.        .List(0, 4) = "Status"
  36.  
  37.         End With
  38.  
  39.           With ListBox1
  40.           .AddItem
  41.             .List(linhalistbox, 0) = Sheets("Plan1").Cells(Linha, 1) 'Ticket
  42.             .List(linhalistbox, 1) = Sheets("Plan1").Cells(Linha, 2) 'Projeto
  43.             .List(linhalistbox, 2) = Sheets("Plan1").Cells(Linha, 3) 'Descrição
  44.             .List(linhalistbox, 3) = Sheets("Plan1").Cells(Linha, 4) 'Responsável
  45.             .List(linhalistbox, 4) = Sheets("Plan1").Cells(Linha, 5) 'Status
  46.             End With
  47.             linhalistbox = linhalistbox + 1
  48.                 End If
  49.                     End If
  50.                         End If
  51.                             End If
  52.                                 End If
  53.  
  54.             Linha = Linha + 1
  55.             Wend
  56.  
  57.    End With
  58.  
  59. Application.ScreenUpdating = True
  60. Application.DisplayAlerts = True
  61.  
  62. End Sub
Jul 24 '18 #1
Share this Question
Share on Google+
1 Reply


zmbd
Expert Mod 5K+
P: 5,397
1) Link to the other thread so that we have some context
2) What have you tried already to fix this issue. We're happy to help; however, we don't wade thru tons of code - you have to have already done the basic troubleshooting.
Jul 24 '18 #2

Post your reply

Sign in to post your reply or Sign up for a free account.