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

Answers


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

Notes:

  1. Line .Range(.Cells(3, 9), .Cells(.Rows.Count, 9).End(xlUp)).ClearContents removes previous data from column I
  2. Line rng = Application.Transpose(strText) writes all data in column I without loop
  3. If value is not numeric we replace it with empty string: If Not IsNumeric(c) Then c = "" and then removes using RemoveDuplicates
  4. Line .NumberFormat = "0" sets number format in range. Line .Value = .Value converts all "numbers stored as text" to "numbers stored as numbers"

Need Your Help

Hide XML documentation

.net visual-studio-2012 plugins visual-studio-extensions xml-documentation

I hate comments of any kind, esp. large chunks of XML documentation that are mixed up with code. It makes for a very difficult reading of code.

Hosting HTML/CSS site on Visual Studio hosting service

javascript c# visual-studio-2012 localhost cross-domain

I developed small Restful Web Service in VS2012 which should manipulate with database through entity framework.