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

Binino

New Member
Joined
Jul 3, 2015
Messages
3
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.
 

Attachments

  • Capture.PNG
    Capture.PNG
    7 KB · Views: 8

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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).
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.

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

Forum statistics

Threads
1,226,116
Messages
6,189,057
Members
453,523
Latest member
Don Quixote

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top