How to add data into particular row in excel

Question

I want to ask how to add data into into a particular row in an Excel spreadsheet using a Userform.

When I add a new product A to the sheet, I want the new data to be located under the last row of product A, and the same for other products B and C.

Is there any way to sort the data exactly when I enter the new data into the existing table?

The code I have below adds data row by row, no matter what product I enter, making the table unsorted and appearing scattered.

Private Sub CommandButton1_Click()
    Dim LastRow As Object

    Set LastRow = Sheet1.Range("a65536").End(xlUp)

    LastRow.Offset(1, 0).Value = TextBox1.Text
    LastRow.Offset(1, 1).Value = TextBox2.Text
    LastRow.Offset(1, 2).Value = TextBox3.Text

    MsgBox "One record written to Sheet1"

    If MsgBox("Do you want to continue entering data?", vbYesNo + vbCritical, "Caution") = vbYes Then
        TextBox1.Text = ""
        TextBox2.Text = ""
        TextBox3.Text = ""
    Else
        End
        TextBox1.SetFocus
        Unload Me
    End If
End Sub

Welcome to StackOverflow! Most likely you'll want to use the Range.Sort method, triggered after you enter the new row. But I'll leave it to someone more familiar with the command to respond with an answer. — chuff, Feb 14 '13 at 03:22

score 0 · Accepted Answer · edited May 23 '17 at 12:12

You can add the data to the Last Row and then Sort the data.

1) To get the last row see this link

2) To Sort the data you can use this simple code. The below sample code will sort Col A which has headers

With Sheets("Sheet1")
    .Columns("A:A").Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
End With

3) One Tip about using End: See Point 6 in this thread.

4) So If I combine both codes then your code will look something like this

Option Explicit

Private Sub CommandButton1_Click()
    Dim LastRow As Long

    '~~> Change this to the releavnt sheet name
    With Sheets("Sheet1")
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1

        .Range("A" & LastRow) = TextBox1.Text
        .Range("B" & LastRow) = TextBox2.Text
        .Range("C" & LastRow) = TextBox3.Text

        '~~> Sort the Data. I am assuming that Row 1 has Headers
        .Columns("A:C").Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    End With

    If MsgBox("One record written to Sheet1. Do you want to continue entering data?", vbYesNo + vbCritical, "Caution") = vbYes Then
        TextBox1.Text = ""
        TextBox2.Text = ""
        TextBox3.Text = ""
        TextBox1.SetFocus
    Else
      Unload Me
    End If
End Sub

How to add data into particular row in excel

1 Answers1