CODEWITHSUNDEEP
vbaexcel
TijmenBrx
TijmenBrx

Reputation: 52

Insert new row and sort automatic in Excel

I have a table in Excel with a list of names linked with some data. I would like to be able to create new rows (with data from something form-alike) and have them sorted into the existing rows.

I don't really know how to code VBA in Excel and can't find any examples that insert rows based on their data. Most examples insert new blank rows to style the table.

I've got this to insert the new row with input value of user: 

Sub Test_Function()
Dim val As String
val = InputBox("Enter your name", "May I know your Name!")

'Inserting a Row at at Row 2
Range("A3").EntireRow.Insert
Range("A3").Formula = val
'
End Sub

More information: The data is in this lay-out: Name - Money Provided W1 - Money ProvidedW2 - Drinks W1 - Drinks W2 - Total Money owed

When entering a new name, the row needs to copy the formulas like the other rows. At the bottom of the sheet is another row with the sum of 'money provided' and 'money owed'. (I think therefore I can't just add it to the last row, but rather insert a new one, not sure tho...)

Upvotes: 1

Views: 3758

Answers (1)

Matteo NNZ
Matteo NNZ

Reputation: 12665

I wouldn't insert a new row, but rather put the results of the insertion at the end of the sheet: 

lastRow = Range("A1").End(xlDown).Row + 1
Range("A" & lastRow) = data1
Range("B" & lastRow) = data2 'etc.

and then sorting the whole sheet by the key you prefer (this example is for sorting A-C in descending order by column A - recorded macro): 

    Columns("A:C").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1:A" & lastRow), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:C" & lastRow)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Upvotes: 1

Related Questions