Solved

List box - only visible entries

Posted on 2012-03-21
4
187 Views
Last Modified: 2012-03-21
Hi,

I have filtered a list for unique entries only, but my code for bring that list into a list box still picks up the hidden rows

Can i change the below code to only import visible entries

Thanks
Seamus

Private Sub UserForm_Activate()

Dim j As String
   
    j = 2
    Do While Sheets("Sheet1").Cells(j, 1).value
        j = j + 1
    Loop
   
    Me.ListBox1.RowSource = "Sheet1!Q2:Q" & (j - 1)



End Sub
0
Comment
Question by:Seamus2626
  • 2
4 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 37746754
0
 
LVL 18

Accepted Solution

by:
p912s earned 500 total points
ID: 37746923
This will get you your unique list without the filter.

Private Sub UserForm_Activate()
    Dim cell As Range
    Dim tempList As Variant: tempList = ""
    For Each cell In Range("Q2:Q" & Range("Q65536").End(xlUp).Row)
        If cell.Value <> "" Then
            If InStr(1, tempList, cell.Value) = 0 Then
                If tempList = "" Then tempList = Trim(CStr(cell.Value)) Else tempList = tempList & "|" & Trim(CStr(cell.Value))
            End If
        End If
    Next cell
    Me.ListBox1.List = Split(tempList, "|")
End Sub

Open in new window

0
 

Author Closing Comment

by:Seamus2626
ID: 37746939
Cheers, thats the one i needed!

Seamus
0
 
LVL 18

Expert Comment

by:p912s
ID: 37746988
Glad to help!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now