CODEWITHSUNDEEP
vbasortingexcelrow
Lauk-k
Lauk-k

Reputation: 90

Sort an entire row using excel VBA

I am creating a client register where clients can be added using a button. I also have a sort function that allows me to sort them on klantnr. But the problem is whenever I use the sort function it only sorts klantnr and the added information provided from the form keeps in place. For example, this is my userform:

(http://puu.sh/cw94k/ebf4510a24.png)

And this is my excel sheet:

(http://puu.sh/cw9nc/3a8e19a989.png)

When I add another row it sorts klantnr but it doesn't take the other values, such as Naam and Adres with it. So it needs to sort klantnr and take the other information with it

This is my code:

Private Sub btn_Toevoegen_Click()
   Dim laatsteKlantNummer As Integer

Range("B4:B13").End(xlDown).Select
laatsteKlantNummer = ActiveCell.Value
ActiveCell.Offset(1, 0).Value = txtKlant + 0
ActiveCell.Offset(1, 1).Value = txtNaam
ActiveCell.Offset(1, 2).Value = txtAdres
ActiveCell.Offset(1, 3).Value = txtWoonplaats
ActiveCell.Offset(1, 4).Value = txtContact
Me.Hide
Range("B4:B13").Sort Key1:=Range("B4:B13"), Order1:=xlAscending
End Sub

Upvotes: 1

Views: 5105

Answers (1)

cboden
cboden

Reputation: 823

use the Sort object of the sheet instead of the one from the range object:

ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("B4")
ActiveSheet.Sort.SetRange Range("B4:F13")
ActiveSheet.Sort.Apply

There you can use the SetRange method to define not only the key column but also the others you want to have sortet

Upvotes: 2

Related Questions