confused sort range based on names in column

[abdelfattah]

hi experts
I try sorting data based on column B
this is what I want

a.xlsx
	A	B	C	D	E	F	G
7	1	alla1	274	22/05/2006	15	4	9
8	2	alla2	266	01/07/2006	15	3	0
9	3	alla3	275	20/08/2006	15	1	11
10	4	alla4	276	08/02/2007	14	7	23
11	5	alla5	271	20/01/2007	14	8	11
12	6	alla6	266	13/05/2006	15	4	18
13	7	alla7	271	18/06/2006	15	3	13
14	8	alla8	269	29/09/2005	16	0	2
15	9	alla9	276	02/07/2006	15	2	29
16	10	alla10	269	01/07/2006	15	3	0
ss

Cell Formulas
Range	Formula
E7:E16	E7	=IF($D7="","",DATEDIF($D7,DATE(YEAR(NOW()),10,1),"y"))
F7:F16	F7	=IF($D7="","",DATEDIF($D7,DATE(YEAR(NOW()),10,1),"ym"))
G7:G16	G7	=IF($D7="","",DATEDIF($D7,DATE(YEAR(NOW()),10,1),"md"))

but suddenly this is what I got

a.xlsx
	A	B	C	D	E	F	G
7	1	alla1	274	22/05/2006	15	4	9
8	2	alla10	269	01/07/2006	15	3	0
9	3	alla11	275	24/07/2006	15	2	7
10	4	alla12	276	01/06/2006	15	4	0
11	5	alla13	271	25/09/2005	16	0	6
12	6	alla14	272	01/08/2006	15	2	0
13	7	alla15	269	28/09/2006	15	0	3
14	8	alla16	270	02/05/2006	15	4	29
15	9	alla17	271	27/04/2006	15	5	4
16	10	alla18	271	14/01/2006	15	8	17
17	11	alla2	266	01/07/2006	15	3	0
18	12	alla3	275	20/08/2006	15	1	11
19	13	alla4	276	08/02/2007	14	7	23
20	14	alla5	271	20/01/2007	14	8	11
21	15	alla6	266	13/05/2006	15	4	18
22	16	alla7	271	18/06/2006	15	3	13
ss

Cell Formulas
Range	Formula
E7:E22	E7	=IF($D7="","",DATEDIF($D7,DATE(YEAR(NOW()),10,1),"y"))
F7:F22	F7	=IF($D7="","",DATEDIF($D7,DATE(YEAR(NOW()),10,1),"ym"))
G7:G22	G7	=IF($D7="","",DATEDIF($D7,DATE(YEAR(NOW()),10,1),"md"))

how should fix,please?

 

[Joe4]

Is there anything currently in columns H and I, or are those empty and OK to use those for our "helper" columns?

 

[Joe4]

If columns H and I are available, here is an update to your current code that should do what you want:

Private Sub WorkSheet_Change(ByVal Target As Range)
    
    Dim lastrow As Long

'   Only run if data updated under cell B6
    If Target.Column = 2 And Target.Row > 6 Then
'       Temporarily disable events
        Application.EnableEvents = False
'       Find last row
        lastrow = Cells(Rows.Count, 2).End(xlUp).Row
'       Populate columns H and I with formulas
        Range("H7:H" & lastrow).FormulaR1C1 = _
            "=IF(ISERROR(RIGHT(RC[-6],2)+0),LEFT(RC[-6],LEN(RC[-6])-1),LEFT(RC[-6],LEN(RC[-6])-2))"
        Range("I7:I" & lastrow).FormulaR1C1 = _
            "=IF(ISERROR(RIGHT(RC[-7],2)+0),RIGHT(RC[-7],1),RIGHT(RC[-7],2))+0"
'       Sort data based on helper columns
        ActiveSheet.Sort.SortFields.Clear
        Range("B7:I" & lastrow).Sort key1:=Range("H7:H" & lastrow), order1:=xlAscending, _
            key2:=Range("I7:I" & lastrow), order2:=xlAscending, Header:=xlNo
'       Make sure columns H and I are hidden
        Range("H7:I7").EntireColumn.Hidden = True
'       Re-enable events
        Application.EnableEvents = True
    End If
    
End Sub

 

[abdelfattah]

it's very excellent ! thanks so much master

 

[Joe4]

You are welcome!
I am glad it works for you.

I added lots of comments to my code so you can follow along and see what each step is doing.