Unique values from visible range to list box

[Senthil Murugan]

Good Afternoon everyone

Can anybody know the code for populating Unique Values from Visible Range ( result to be populated in User form List box1 )

Thanks in advance

 

[HighAndWilder]

Good Afternoon everyone

Can anybody know the code for populating Unique Values from Visible Range ( result to be populated in User form List box1 )

Thanks in advance

If you know how the data is filtered then it can be done quite simply in one line without even have to address
the issue of getting visible rows only.

What is your data range and filter criteria?

Me.ListBox1.List = Evaluate("UNIQUE(FILTER(Sheet2!$A$2:$A12,Sheet2!$A$2:$A$12>3))")

 

[Senthil Murugan]

Private Sub CommandButton1_Click()
Me.ListBox1.List = Evaluate("UNIQUE(FILTER(Sheet1!$A$2:$A50,Sheet1!$A$2:$A$50>3))")
End Sub

Dear Sir,

It is not working
I need result of visible unique like 10,12,20 from filtered

Thanks in advance

 

[HighAndWilder]

View attachment 118520

Private Sub CommandButton1_Click()
Me.ListBox1.List = Evaluate("UNIQUE(FILTER(Sheet1!$A$2:$A50,Sheet1!$A$2:$A$50>3))")
End Sub

Dear Sir,

It is not working
I need result of visible unique like 10,12,20 from filtered

Thanks in advance

But how is the data filtered to only show the visible rows?

See my question in Post 2.
What is your data range and filter criteria?

The same filter criteria that is used on the worksheet needs to be written into this line where highlighted.

 

[Fuji]

Can anybody know the code for populating Unique Values from Visible Range ( result to be populated in User form List box1 )

Private Sub CommandButton1_Click()
    Dim x, e
    [a1].CurrentRegion.Columns(1).Offset(1).Copy
    With CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .GetFromClipboard
        x = Split(.GetText, vbCrLf)
    End With
    Application.CutCopyMode = False
    With CreateObject("Scripting.Dictionary")
        For Each e In x
            If e <> "" Then .Item(e) = ""
        Next
        x = .keys
    End With
    Me.ListBox1.List = x
End Sub

 

[Senthil Murugan]

Thank you very much Fuji

You are genius

Can i know what is this "new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

Thanks

 

[Senthil Murugan]

Thank you HighandWilder

You are brilliant

Thanks

 

[Fuji]

You are welcome and thanks for the feedback.

It is using Data Object without setting the reference to [Microsoft Forms 2.0 Object Library] to control ClipBoard.
It is getting unique value from the copied range, so that it doesn't care about your filter criteria.

 

[JEC]

or

Private Sub CommandButton1_Click()
 Dim it
 With CreateObject("scripting.dictionary")
    For Each it In Cells(1).CurrentRegion.Offset(1)
      If it.RowHeight Then .Item(it.Value) = Empty
    Next
  ListBox1.List = .keys
 End With
End Sub

 

[HighAndWilder]

or

Private Sub CommandButton1_Click()
 Dim it
 With CreateObject("scripting.dictionary")
    For Each it In Cells(1).CurrentRegion.Offset(1)
      If it.RowHeight Then .Item(it.Value) = Empty
    Next
  ListBox1.List = .keys
 End With
End Sub

Can you please explain how this works JEC?

How is the dictionary populated?

Thanks