What are a few unique Chinese values?

Vba for unique values

• Thanks Jan. That new version of the VBA for a selected range is working except that it is listing the values with blanks in between the numbers I am evaluating. So my list will look like this: 52 75 46 32 80 95 Is there a way of listing these with no blanks. I really appreciate your help this will make life much easier for me and my spreadsheet. Thanks Ursula I have used one of Damon Ostrander's VBA to find Unique Values and list then in a column. Works well. However I need to list these values in a user defined range not a column. Can you help with changeing the VBA to have a user defined range. His VBA was as shown: Sub ListUniqueValues() 'lists the unique values found in a user-defined range into a 'user-defined columnar range Dim SearchRng As Range Dim ResultRng As Range Dim Cel As Range Dim iRow As Long Set SearchRng = Application.InputBox("Select search range", _ "Find Unique Values", Type:=8) Do Set ResultRng = Application.InputBox("Select results columnar range", _ "Write Unique Values", Type:=8) Loop Until ResultRng.Columns.Count = 1 iRow = 0 For Each Cel In SearchRng If Application.WorksheetFunction.CountIf(ResultRng, Cel.Value) = 0 Then 'This value doesn't already exist iRow = iRow + 1 If iRow > ResultRng.Rows.Count Then MsgBox "Not enough rows in result range to write all unique values", _ vbwarning, "Run terminated" Exit Sub Else ResultRng(iRow).Value = Cel.Value End If End If Next Cel 'sort result range ResultRng.Sort ResultRng Would appreciate the help as then I can put my list of unique values anywhere in my spreadsheet. Ursula

• Answer:

  That is odd, it certainly does not do this for me and I did test the routine. Maybe you have cells with spaces in them in the range. Try this modified version: Sub ListUniqueValues() 'lists the unique values found in a user-defined range into a 'user-defined columnar range Dim SearchRng As Range Dim ResultRng As Range Dim Cel As Range Dim iRow As Long Set SearchRng = Application.InputBox("Select search range", _ "Find Unique Values", Type:=8) Do Set ResultRng = Application.InputBox("Select results columnar range", _ "Write Unique Values", Type:=8) Loop Until ResultRng.Columns.Count = 1 'Make sure result range is large enough! Set ResultRng = ResultRng.Resize(SearchRng.Rows.Count) iRow = 0 For Each Cel In SearchRng If Application.WorksheetFunction.CountIf(ResultRng, Trim(Cel.Value)) = 0 Then 'OMit cells with just spaces If Len(Replace(Cel.Value, " ", "")) > 0 Then 'This value doesn't already exist iRow = iRow + 1 ResultRng(iRow).Value = Cel.Value End If End If Next Cel 'sort result range ResultRng.Sort ResultRng End Sub What I would do is make sure the result range is equally sized as the source range, like so: Sub ListUniqueValues() 'lists the unique values found in a user-defined range into a 'user-defined columnar range Dim SearchRng As Range Dim ResultRng As Range Dim Cel As Range Dim iRow As Long Set SearchRng = Application.InputBox("Select search range", _ "Find Unique Values", Type:=8) Do Set ResultRng = Application.InputBox("Select results columnar range", _ "Write Unique Values", Type:=8) Loop Until ResultRng.Columns.Count = 1 'Make sure result range is large enough! Set ResultRng = ResultRng.Resize(SearchRng.Rows.Count) iRow = 0 For Each Cel In SearchRng If Application.WorksheetFunction.CountIf(ResultRng, Cel.Value) = 0 Then 'This value doesn't already exist iRow = iRow + 1 ResultRng(iRow).Value = Cel.Value End If Next Cel 'sort result range ResultRng.Sort ResultRng End Sub