There is a table:

enter image description here

You need to set up automatic sorting by column B, namely how you understand "B2". And here is one of the options, as I try to implement it using VBA methods:

Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("B3:B4")) Is Nothing Then Exit Sub [B2].CurrentRegion.Sort [B2], xlAscending, Key2:=[C2], Order2:=xlAscending, Header:=xlYes End Sub

And look what he does to me when you type in cell B4 -> 2:

enter image description here

I tried various options, but it does not work out for me at all. What to do?

    2 answers 2

    But did not try to check the address of the region over which the sorting takes place? In vain, for in it lies the answer.

     Debug.Print [B2].CurrentRegion.Address > $A$2:$C$4

    Those. The first column also falls into the area being sorted, and it is this column that is assigned as the heading, and everything below is sorted. So you need to cut this line out of the sort area.

     With [B2].CurrentRegion .Range(.Cells(2, 1), .Cells(.Rows.Count, .Columns.Count)).Sort [B1], xlAscending, Key2:=[C1], Order2:=xlAscending, Header:=xlYes End With

      If you highlight the range to be sorted, click data> sort, and in the sort menu, select "My data contains headers", then your macro will sort as intended.

      PS: I could be wrong, but in my vague memories it says that software sorting does not respond to the option "Header = xlYes". And despite this, MS Excel is the best :).