asked on

VBA macro to extra unique values in column

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

SANTABABY

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))
Else
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

excel learner

ASKER

Hi SANTABABY,

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.
extract-unique-items-from-column.xlsx

ASKER CERTIFIED SOLUTION

SANTABABY

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

excel learner

ASKER

The macro is giving the expected result.

Thank you very much