Link to home
Create AccountLog in
Avatar of EBocchieri

asked on

filtered data as source of a listbox with VBA

I have a Range containg data. Using Excel VBA, I would filter data accordling a criteria and display only the filtered data in a listbox . Can you help me?
Avatar of paisleym
Flag of Australia image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of EBocchieri


Hi Marcelle, thanks for your replay. I haven't try your code but it seems properly working. My request was, specifically, for use a range filtered in place, where height of the rows is =0 and are not "visible" ,when data are not according with the criteria selected:
Your replay obviously was suffucient for my request, but if you know a code for the above request, I please you to send me it, Thank you very mauch for yor courtesy
Sincerely, Emanuele
Ok, "where height of the rows is =0 and are not "visible"" was not in the original request. I have not tried to do this myself, but

Me.lstLines.Visible = false

should work.
Hi Marcelle,
I have tried this code and is properly working, many thanks
Have you a nice day
Sincerely, Emanuele

Private Sub CommandButton1_Click()                          'is a button in Sheet("DB")
'assuming duplicate values are possible.
'Use macro record to sort the area be the listbox value.
oldValue = ""
For r = 2 To 65536 ' declare r as long, assuming focus is already on Activesheet
  If IsEmpty(Cells(r, 1)) = True Then ' where 1 is the column sorted on
     Exit For
  End If
  If Cells(r, 1) <> oldValue Then
     If Cells(r, 1).Height > 0 Then  'Autofilter gives Height = 0 for record not matching
       Me.lstLines.AddItem (Cells(r, 3).Value) ' Cells(r.., not s) lstLines is the name of the listbox.
       oldValue = Cells(r, 1)
     End If
  End If
Next r
End Sub