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.