none
VBA Get Unique values from Excel Range RRS feed

  • Question

  • Hi All,

    My code below works perfect to get unique value from a range but instead of looping through entire row it loops entire column first. For e.g my define range is E2:L20. Code list uniques values from column E first, than F and till L. I want code to look for values in E2, F2 till L2 and than go to E3, L3 and so on.

    Sub GetUniqueCodeLst()
        Dim uniques As Collection
        Dim source As Range
        Dim it
        Dim i As Integer
        
        Set source = sColumns.Range("E2:L20")
        Set uniques = GetUniqueValues(source.Value)
        i = 2
        
        For Each it In uniques
            sColumns.Cells(i, 1) = it
            i = i + 1
        Next
    End Sub
    
    Public Function GetUniqueValues(ByVal values As Variant) As Collection
        Dim result As Collection
        Dim cellValue As Variant
        Dim cellValueTrimmed As String
    
        Set result = New Collection
        Set GetUniqueValues = result
    
        On Error Resume Next
    
        For Each cellValue In values
            cellValueTrimmed = Trim(cellValue)
            If cellValueTrimmed = "" Then GoTo NextValue
            result.Add cellValueTrimmed, cellValueTrimmed
    NextValue:
        Next cellValue
    
        On Error GoTo 0
    End Function
    

    Thanks,

    Zav

    Tuesday, July 10, 2018 5:57 PM

Answers

  • Hello Zav,

    Please try to use Transpose to change the source values' row and column.

     Set uniques = GetUniqueValues(WorksheetFunction.Transpose(source.Value))

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by zaveri cc Wednesday, July 11, 2018 2:36 PM
    Wednesday, July 11, 2018 2:50 AM

All replies

  • Hello Zav,

    Please try to use Transpose to change the source values' row and column.

     Set uniques = GetUniqueValues(WorksheetFunction.Transpose(source.Value))

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by zaveri cc Wednesday, July 11, 2018 2:36 PM
    Wednesday, July 11, 2018 2:50 AM
  • Works perfectly.

    Thanks,

    Zav


    • Edited by zaveri cc Wednesday, July 11, 2018 2:36 PM
    Wednesday, July 11, 2018 2:26 PM