Using input box to store unique values

Question

I am working on some data from which I want to extract unique values from a column and storing them in an array and later using it for other calculations.

Sub A_Unique_B()
Dim X
Dim objDict As Object
Dim lngRow As Long

Set objDict = CreateObject("Scripting.Dictionary")
X = Application.Transpose(Range([E1], Cells(Rows.Count, "E").End(xlUp)))

For lngRow = 1 To UBound(X, 1)
objDict(X(lngRow)) = 1
Next
Range("K1:K" & objDict.Count) = Application.Transpose(objDict.keys)
End Sub

The Data set is found here. Now I want the code to take the input using an input box which column to search([E1] here) for unique values and where the output is stored ("K1:K" here).

Wiktor Stribiżew · Accepted Answer · 2015-07-09 08:09:05Z

1

Add the InputBox code with a variable to hold its value:

Dim col As String
Dim output_col As String
col = InputBox("Type the column letter to search in", "Data Input")
output_col = InputBox("Type the column letter to write results to", "Data Input")

And add some logic, e.g., if the column letter length is not 0, process.

Sub A_Unique_B()
Dim X
Dim objDict As Object
Dim lngRow As Long

Dim col As String
Dim output_col As String
col = InputBox("Type the column letter to search in", "Data Input")
output_col = InputBox("Type the column letter to write results to", "Data Input")
If Len(col) > 0 And Len(output_col) > 0 Then
  Set objDict = CreateObject("Scripting.Dictionary")
  X = Application.Transpose(Range(col & CStr(1), Cells(Rows.Count, col).End(xlUp)))
  For lngRow = 1 To UBound(X, 1)
    objDict(X(lngRow)) = 1
  Next
  Range(output_col & CStr(1) & ":" & output_col & objDict.Count) = Application.Transpose(objDict.keys)
End If
End Sub

answered Jul 9, 2015 at 8:09

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Curious Lad Over a year ago

Thanks a lot. It worked so well. I am new at excel vba. So, if you won't mind, can you tell me what you mean by process?

Wiktor Stribiżew Over a year ago

I mean perform the transposition, etc. Continue with the code execution.

Curious Lad Over a year ago

Thanks a lot. I have one more question if you could help me with. Suppose I choose the result to be posted in 'K' column using inputbox, can I use output_col and Cstr to put some calculations in the next column of my output.

Wiktor Stribiżew Over a year ago

Yes, you can use Range.Offset(): Range(output_col & CStr(1) & ":" & output_col & objDict.Count).Offset(ColumnOffset:=1) - it will refer to the next column on the right. Or use -1 to start adding data to the column on the left. See reference here

Wiktor Stribiżew Over a year ago

No, you cannot sum "K" and 1 to get "L". You should use GetNextCol("K", 1).

|

Collectives™ on Stack Overflow

Using input box to store unique values

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related