VBA macro to extra unique values in column

Posted on 2012-09-18
Last Modified: 2012-09-20
Dear experts,

Could any one please provide me a vba query which can extract the unique values in a column.

The vba will be in the file on which the query has to be run.

the vba should be enable for prompting for selection of sheet and column.

Thank you
Question by:Excellearner
    LVL 10

    Expert Comment

    Try this:
    Sub test()
      'Dim wsList() As Strings
      Dim i As Long
      Dim wsname As String
      Dim col As String
      Dim ncol As Integer
      Dim c As Range
      Dim R As Range
      Dim result As String
      wsname = InputBox("Please enter the sheet name", "Sheet Name")
      col = InputBox("Please enter the Column name or number", "Column Name")
      If IsNumeric(col) Then
        Set R = Sheets(wsname).Columns(CInt(col))
        Set R = Sheets(wsname).Columns(col)
      End If
      i = R.Rows(R.Rows.Count).End(xlUp).Row
      For Each c In Range(R.Rows(1), R.Rows(i))
        If WorksheetFunction.CountIfs(R, "=" & c.Value) = 1 Then
            result = result & c.Value & vbCrLf
        End If
      Next c
      MsgBox result
    End Sub

    Author Comment


    Thank you for the comment,

    The Macro (MSwindows 2007 with Excel 2007 version) gave error for the below code ' as not supported by vba'

      'Dim wsList() As Strings

    Kindly help.
    LVL 10

    Accepted Solution

    The workbook is updated with code in (Module1). The macro can be troiggered by Ctrl+Shift+u
    The results are written in "UniqueValues" worksheet in the workbook.
    To Test:
    1. Open the workbook
    2. Press Ctrl+Shift+u

    Author Closing Comment

    The macro is giving the expected result.

    Thank you very much

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now