Array of unique values in a Column range

[GeekyMeeks]

Trying to figure out the code to make an array of all unique values in a column.

So like say from C3:C30 I want an array named divisionNames of all unique values in that range. I intend to use the array later in the code. Trying to figure out a minimalist way of doing it so I don't add like 60 more lines of code to the macro.

Would be very appreciative of any suggestions

[Paul_Hossler]

With Office 365 it's easy.

I don't know if other versions have UNIQUE() worksheet function

Option Explicit


Sub test()
    Dim A As Variant
    Dim i As Long
    
    With Application.WorksheetFunction
        A = .Transpose(.Unique(ActiveSheet.Range("C3:C30")))
    End With


    For i = LBound(A) To UBound(A)
        MsgBox A(i)
    Next i


End Sub

[GeekyMeeks]

That worked amazingly, THANK YOU. Now the only thing I want to figure out (Which is just a nicety) is if I can make A sorted alphabetically.

[Paul_Hossler]

Option Explicit


Sub test2()
    Dim A As Variant
    Dim i As Long
    
    With Application.WorksheetFunction
        A = .Transpose(.Sort(.Unique(ActiveSheet.Range("C3:C30")), 1))
    End With




    For i = LBound(A) To UBound(A)
        MsgBox A(i)
    Next i




End Sub

[SamT]

With Office 365 it's easy.

In Legacy VBA, I would use a Dictionary to get the Uniques then set an arrayVar =Dictionary.Keys

[snb]

Or the extremely fast built-in Excel option 'advanced filter'.

Sub M_snb()
   sheet1.range("C3:C30").advancedfilter 2, , sheet1.cells(1,30), true
   sheet1.cells(1,30).currentregion.sort sheet1.cells(1,30),,,,,,,0
   sn = sheet1.cells(1,30).currentregion
   sheet1.cells(1,30).currentregion.clearcontents
End Sub

[GeekyMeeks]

I have one final question and I VERY much appreciate the help.

Using the code

Option Explicit


Sub test2()
    Dim A As Variant
    Dim i As Long
    
    With Application.WorksheetFunction
        A = .Transpose(.Sort(.Unique(ActiveSheet.Range("C3:C30")), 1))
    End With




    For i = LBound(A) To UBound(A)
        MsgBox A(i)
    Next i




End Sub

How would I go about adding error handling such that if I get for example a Subscript out of range error (or really any other error at all) I can throw a msgbox saying something like "You selected the wrong file"

[snb]

You'd better prevent that kind of error.

[GeekyMeeks]

I know right. I mean I could just leave as is and let people deal with it if they happen to select the wrong file =P (TBH I will prob be the only one using it, so I prob won't ever even see that error) but that's just not me

[Paul_Hossler]

I have one final question and I VERY much appreciate the help.
How would I go about adding error handling such that if I get for example a Subscript out of range error (or really any other error at all) I can throw a msgbox saying something like "You selected the wrong file"

Well ...

the potential errors that might occur most likely would be because of the Range, so if possible try and catch them first

Option Explicit




Sub test3()
    Dim A As Variant
    Dim i As Long
    
    'check first for empty range
    If Application.WorksheetFunction.CountA(ActiveSheet.Range("C3:C30")) = 0 Then
        MsgBox "Ooops"
        Exit Sub
    End If
    
    With Application.WorksheetFunction
        On Error Resume Next
        A = .Transpose(.Sort(.Unique(ActiveSheet.Range("C3:C30")), 1))
        On Error GoTo 0
    End With


    If IsEmpty(A) Then
        MsgBox "Ooops"
        Exit Sub
    End If




    For i = LBound(A) To UBound(A)
        MsgBox A(i)
    Next i
End Sub

[snb]

You can 'help' the user to select valid files exclusively. That's what automation is all about.

[GeekyMeeks]

Now that's an intriguing thought. I just did an

On Error GoTo eh

at the beginning of the function and

eh:
    If Err.Number = 9 Then
        MsgBox "It would appear you have selected  the wrong file"
        sparesWorkbook.Close False
        End
    Else
        MsgBox "The following error occurred: " & Err.Number
    End If

at the end of the function. Not sure if thats a good solution, but thats what I came up with. Now as far as selecting the file its just a

sparesFile = Application.GetOpenFilename(FileFilter:="Excel files (*.xlsx*), *.xlsx*", _
    Title:="Please choose Spares file you downloaded", MultiSelect:=False)

Once I start working with it, if the program errors, I know pretty quick the wrong file was selected.
Again not sure if its a good solution but its the one I came up with

The file by standard is currently named FieldCompliances.xlsx but that could change any day. You never know when they will mess with things. You go to a website, download it, then my macro takes the data on there and eliminates the stuff that doesn't pertain to me and makes the stuff that does look pretty. I mostly made it to make my life easier but share it on shared drives incase anyone else could find it useful. Well, thats not entirely true. I also make things like this because I enjoy programming, just not smart enough to do it for a career. (If I am being honest)

Is there a way to 'help' them select the right file?

[snb]

Have the 'valid' files something in common in their names ?
What determines if they are valid or not ?

[GeekyMeeks]

Well, as it stands the file will always be named FieldCompliances.xlsx but that could always change

I suppose as far as the file itself goes, it should pretty consistently have the same header on it. Infact, my macro is dependant on that header to work. While it doesn't have to be in the same order, it does need to have the same words up there so I suppose I could make it check for any one of those words up there and if it isn't found, bounce back to the user asking them to pick the correct file. Thank you

[SamT]

Function GoodHeader(FileName As String) As Boolean
Dim arrHeader As Variant
Dim HeaderString As String
Dim RequiredHeaders As Variant

HeaderString = ReadHeader(FileName)

RequiredHeaders = Array(Comma,separated,list,of,headers,you,use) 'All in ""
GoodHeader = True

For i = LBound(RequiredHeaders) to UBound(RequiredHeaders)
    If Not CBool(Instr(HeaderString, RequiredHeaders(i)) Then
        GoodHeader = False
        Exit Function
    End If
Next i
End Function

Function ReadHeader(FileName As String) As String
'Code to open filename and read header line back as a string

[Paul_Hossler]

I have one final question and I VERY much appreciate the help.
How would I go about adding error handling such that if I get for example a Subscript out of range error (or really any other error at all) I can throw a msgbox saying something like "You selected the wrong file"

Where did selecting a file come from? What does that have to do with sorting unique values?

More information will get a better answer

[GeekyMeeks]

The file is a internal file. The sorting unique value question was answered and the discussion had kind of morphed into something else

[snb]

You directed it in that direction yourself, not 'morphed into'.
Provide all necessary information, so the answers can be more concise.

[GeekyMeeks]

I wasn't complaining by any means and I am sorry if it sounded like I was. I was just answering Paul_Hossler's question about what the current discussion had to do with the original question. I honestly appreciate the info and really didn't mean to make that sound like a complaint.I absolutely 100% directed the conversation where it went and am incredibly grateful for those who came along for the ride because I got some very useful feedback and information from that.