Sort Dynamic Table with Condition

[zinah]

Hi,

I have the below table, what I need is to sort the numbering by ascending order, it's easy if I have a fixed table, but the challenge is this table is dynamic, it changes whenever I change the EE ID. Can you help me with the formula that solve this issue?

[TABLE="width: 512"]
<tbody>[TR]
[TD="width: 64"]EE ID[/TD]
[TD="width: 64"]AAAA[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]KEY_EEID[/TD]
[TD]Goal#[/TD]
[TD]Goal Overall Progress[/TD]
[TD]Objective[/TD]
[TD]Category[/TD]
[TD]Metric/Outcomes[/TD]
[TD]Active/Deleted[/TD]
[TD]Numbering[/TD]
[/TR]
[TR]
[TD]AAAA1[/TD]
[TD="align: right"]1[/TD]
[TD]NO[/TD]
[TD]BBBBBB[/TD]
[TD]OE[/TD]
[TD]nnnnn[/TD]
[TD]Active[/TD]
[TD="align: right"]3.2[/TD]
[/TR]
[TR]
[TD]AAAA2[/TD]
[TD="align: right"]2[/TD]
[TD]NO[/TD]
[TD]CCCCC[/TD]
[TD]OE[/TD]
[TD]jjjjj[/TD]
[TD]Active[/TD]
[TD="align: right"]3.1[/TD]
[/TR]
[TR]
[TD]AAAA3[/TD]
[TD="align: right"]3[/TD]
[TD]YES[/TD]
[TD]KKKK[/TD]
[TD]BE[/TD]
[TD]eee[/TD]
[TD]Active[/TD]
[TD="align: right"]4.2[/TD]
[/TR]
[TR]
[TD]AAAA4[/TD]
[TD="align: right"]4[/TD]
[TD]YES[/TD]
[TD]LLLL[/TD]
[TD]OE[/TD]
[TD]rrr[/TD]
[TD]Active[/TD]
[TD="align: right"]1.1[/TD]
[/TR]
</tbody>[/TABLE]



Thank you

 

[James006]

Hi,

It seems to me the dynamic part is in fact when you are changing the Key_EEID ....

If it is the case, an event macro could run your Sort macro as soon as you are changing anything in Column A ...

Hope this will help

 

[zinah]

Hi,

Thank you for your respond! Yes, you're right. Can you please share the suggested macro?

 

[James006]

Hi again,

In your sheet module, you could test following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 1 Then Exit Sub
If Target.Target > 1 Then Exit Sub
Dim last As Long
last = ActiveSheet.Cells(Application.Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Range("A1:H" & last).Sort [A1], xlAscending, Header:=xlYes
Range("A1").Select
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Hope this will help

 

[zinah]

I copied the macro to my sheet and when I tried to run it, it didn't show this macro in my drop-down list of macros, I even copied it to a new workbook and the same happened. Is there anything I can do to fix? I have named the range of each column if you want I can share them with you?

 

[James006]

Hi,

Proposal is an Event Macro ...

see link for an explanation :

https://www.excel-easy.com/vba/events.html#worksheet-change-event

 

[zinah]

Hi,

That's super great! thank you so much for your help and guidance.

 

[James006]

You are welcome ...