sort, remove duplicates and blanks, return numbers only in an excel vba array
In a userform, I have a textbox with multiline enabled . I want the user to enter numbers in the textbox with each number being in a new line and then click a commandbutton. The commandbutton should store the text into an array, sort in ascending order, remove duplicates and blanks and non numbers and then return the data to the excel sheet starting at range I3.
I tried coding it but failed to sort, remove blanks and non numbers. Moreover, the output in the excel sheet is not recognized as numbers :(
In my simple code, when the following text is entered into the textbox
1 2 3 4 6 5
The output on the excel sheet is
5 1 2 3 4 6
Here is my trial .. any help will be appreciated
Private Sub CommandButton1_Click() Dim strText() As String Dim i As Long, k As Long k = 3 strText = Split(TextBox3.Text, Chr(10)) For i = 0 To UBound(strText) Sheet3.Cells(k, 9).Value = strText(i) k = k + 1 Next i With Sheet3 .Range("I3:I" & k).RemoveDuplicates Columns:=1, Header:=xlNo .Range("I3:I" & k).Sort Key1:=.Range("I3"), Order1:=xlAscending End With End Sub
Try following code:
Private Sub CommandButton1_Click() Dim strText() As String Dim rng As Range Dim c As Range strText = Split(TextBox3.Text, Chr(10)) With Sheet1 'clear previous content .Range(.Cells(3, 9), .Cells(.Rows.Count, 9).End(xlUp)).ClearContents Set rng = .Cells(3, 9).Resize(UBound(strText)+1) End With rng = Application.Transpose(strText) rng.Replace Chr(13), "" For Each c In rng If Not IsNumeric(c) Then c = "" Next With rng .NumberFormat = "0" .Value = .Value .RemoveDuplicates Columns:=1, Header:=xlNo .Sort Key1:=.Cells(1, 1), Order1:=xlAscending End With End Sub
- Line .Range(.Cells(3, 9), .Cells(.Rows.Count, 9).End(xlUp)).ClearContents removes previous data from column I
- Line rng = Application.Transpose(strText) writes all data in column I without loop
- If value is not numeric we replace it with empty string: If Not IsNumeric(c) Then c = "" and then removes using RemoveDuplicates
- Line .NumberFormat = "0" sets number format in range. Line .Value = .Value converts all "numbers stored as text" to "numbers stored as numbers"