Excel VBA count unique values in a column within a named range

[Binino]

I am trying to create a macro that looks at a named range of cells and counts the amount of unique values within that name. As follows:

Excel

The picture above is from cells A1 to E8. The range in red is a named range called "456." The range in blue is the range I need evaluated for unique values. As of right now, I have been able to get the code to work all the way to Excel selecting the area in blue, but I have not been able to write code that spits out the true amount of unique values within the blue-contoured range.

Right now, the code I have in place looks at the spreadsheet, sorts it by CUSTOMER number, creates a space in between the different customers and then selects the range I need to evaluate. The goal I am trying to accomplish is to have the code identify whether a single client has multiple, or just one POP # and then have it make decisions as to continue with the rest of the code in one direction or another.

Thank you so much!

I tried using the countA and unique functions within Excel but I much rather have VBA just make a decision and not have Excel calculate the output because I know multiple people will have access to this spreadsheet and they will for sure tamper with the formulas.

 

[HighAndWilder]

I am trying to create a macro that looks at a named range of cells and counts the amount of unique values within that name. As follows:

Excel

The picture above is from cells A1 to E8. The range in red is a named range called "456." The range in blue is the range I need evaluated for unique values. As of right now, I have been able to get the code to work all the way to Excel selecting the area in blue, but I have not been able to write code that spits out the true amount of unique values within the blue-contoured range.

Right now, the code I have in place looks at the spreadsheet, sorts it by CUSTOMER number, creates a space in between the different customers and then selects the range I need to evaluate. The goal I am trying to accomplish is to have the code identify whether a single client has multiple, or just one POP # and then have it make decisions as to continue with the rest of the code in one direction or another.

Thank you so much!

I tried using the countA and unique functions within Excel but I much rather have VBA just make a decision and not have Excel calculate the output because I know multiple people will have access to this spreadsheet and they will for sure tamper with the formulas.

I can't create a named range with a name of 456. How have you done it?

What version of Excel are you using? Can you please update your profile with the version. Thanks

Excel naming rules​

When creating a name in Excel, there are a few rules to remember:

• An Excel name should be under 255 characters long.
• Excel names cannot contain spaces and most punctuation characters.
• A name must begin with a letter, underscore (_), or backslash (\). If a name begins with anything else, Excel will throw an error.
• Excel names are case-insensitive. For example, "Apples", "apples" and "APPLES" will be treated as the same name.
• You cannot name ranges like cell references. That is, you can't give the name "A1" or "AA1" to a range.
• You can use a single letter to name a range like "a", "b", "D", etc. except for the letters "r" "R", "c", and "C" (these characters are used as shortcuts for selecting a row or column for the currently selected cell when you type them in the NameBox).

 

[Binino]

Hi HighAndWilder.

The REAL name is actually different (it's the client's first and last names). The Excel version I'm using is Microsoft Excel for Microsoft 365 MSO (version 2408 ) 32 bit.

Thanks.

 

[HighAndWilder]

I am trying to create a macro that looks at a named range of cells and counts the amount of unique values within that name. As follows:

Excel

The picture above is from cells A1 to E8. The range in red is a named range called "456." The range in blue is the range I need evaluated for unique values. As of right now, I have been able to get the code to work all the way to Excel selecting the area in blue, but I have not been able to write code that spits out the true amount of unique values within the blue-contoured range.

Right now, the code I have in place looks at the spreadsheet, sorts it by CUSTOMER number, creates a space in between the different customers and then selects the range I need to evaluate. The goal I am trying to accomplish is to have the code identify whether a single client has multiple, or just one POP # and then have it make decisions as to continue with the rest of the code in one direction or another.

Thank you so much!

I tried using the countA and unique functions within Excel but I much rather have VBA just make a decision and not have Excel calculate the output because I know multiple people will have access to this spreadsheet and they will for sure tamper with the formulas.

Are you able to send us more data using XL2BB so that we can see where this snap shot is on the sheet and where the customer number is. Any code will also be useful.

 

[Peter_SSs]

The Excel version I'm using is Microsoft Excel for Microsoft 365

Please update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if this does what you want (once you have edited for the correct range name of course).
I have assumed that the named range is on the active sheet when the code is run.

Sub Count_Unique()
  With Range("NamedRange")
    MsgBox "Unique values = " & Evaluate("rows(unique(" & .Columns(.Columns.Count).Address & "))")
  End With
End Sub

 

[Binino]

Please update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if this does what you want (once you have edited for the correct range name of course).
I have assumed that the named range is on the active sheet when the code is run.

Sub Count_Unique()
  With Range("NamedRange")
    MsgBox "Unique values = " & Evaluate("rows(unique(" & .Columns(.Columns.Count).Address & "))")
  End With
End Sub

This worked perfectly!

Thank you.

 

[Peter_SSs]

You're welcome. Glad it worked for you. Thanks for the confirmation.