vba for alphanumerically sorting column

[imisy3d]

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 1820"]
<colgroup><col span="2"><col><col><col><col><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]1[/TD]
[TD] [/TD]
[TD="align: left"]How Have You Been? [/TD]
[TD="align: left"]Receptive communication[/TD]
[TD="align: left"]Dialogue (2)[/TD]
[TD="align: left"]Unit 1, Lesson 5[/TD]
[TD] [/TD]
[TD="align: left"]Michelle Lapides, Kenneth Ness, Andrew Weidig[/TD]
[TD]x[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]168[/TD]
[TD] [/TD]
[TD="align: left"]Role-Shifting Three Characters: Me explain do-do to 3 different people[/TD]
[TD="align: left"]ASL Up Close (role shifting 3 characters)[/TD]
[TD="align: left"]Example[/TD]
[TD="align: left"]Where? (Unit, Lesson)[/TD]
[TD] [/TD]
[TD="align: left"]Andrew Weidig[/TD]
[TD]Not needed[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]169[/TD]
[TD] [/TD]
[TD="align: left"]Role-Shifting Three Characters: Marathon winners[/TD]
[TD="align: left"]ASL Up Close (role shifting 3 characters)[/TD]
[TD="align: left"]Example[/TD]
[TD="align: left"]Where? (Unit, Lesson)[/TD]
[TD] [/TD]
[TD="align: left"]Andrew Weidig[/TD]
[TD]Not needed[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]170[/TD]
[TD] [/TD]
[TD="align: left"]Planning a Party[/TD]
[TD="align: left"]Action Communication 15[/TD]
[TD="align: left"]Monologue[/TD]
[TD="align: left"]Unit 3, Lesson 3[/TD]
[TD] [/TD]
[TD="align: left"]Monique Holt[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]171[/TD]
[TD] [/TD]
[TD="align: left"]Four Days Left[/TD]
[TD="align: left"]Action Communication 18[/TD]
[TD="align: left"]Monologue[/TD]
[TD="align: left"]Unit 3, Lesson 5[/TD]
[TD] [/TD]
[TD="align: left"]Luisray Aguilar[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]172[/TD]
[TD] [/TD]
[TD="align: left"]A Day in My Life in 2040 1[/TD]
[TD="align: left"]Receptive communication[/TD]
[TD="align: left"]Monologue[/TD]
[TD="align: left"]Unit 10, Lesson 1[/TD]
[TD] [/TD]
[TD="align: left"]Colin Analco[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]173[/TD]
[TD] [/TD]
[TD="align: left"]A Day in My Life in 2040 2[/TD]
[TD="align: left"]Receptive communication[/TD]
[TD="align: left"]Monologue[/TD]
[TD="align: left"]Unit 10, Lesson 1[/TD]
[TD] [/TD]
[TD="align: left"]Pali Dacanay[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD] [/TD]
[TD="align: left"]Survey Perspective: Map - Home look like what. ******* on left, bedroom down hall. [/TD]
[TD="align: left"]ASL Up Close: Survey Perspective (map)[/TD]
[TD="align: left"]Example[/TD]
[TD="align: left"]Where? (Unit, Lesson)[/TD]
[TD] [/TD]
[TD="align: left"]Aimee Sever[/TD]
[TD]Not needed[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]484[/TD]
[TD] [/TD]
[TD="align: left"]Survey Perspective: Map - Restaurant there. Parking lot nearby. [/TD]
[TD="align: left"]ASL Up Close: Survey Perspective (map)[/TD]
[TD="align: left"]Example[/TD]
[TD="align: left"]Where? (Unit, Lesson)[/TD]
[TD] [/TD]
[TD="align: left"]Aimee Sever[/TD]
[TD]Not needed[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]485[/TD]
[TD] [/TD]
[TD="align: left"]You're a Winner![/TD]
[TD="align: left"]ASL Up Close (surprise)[/TD]
[TD="align: left"]Dialogue (2)[/TD]
[TD="align: left"]Where? (Unit, Lesson)[/TD]
[TD] [/TD]
[TD="align: left"]Michelle Lapides, Katasha Walker[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]486[/TD]
[TD="align: left"]***[/TD]
[TD="align: left"]Introduction to The Candle[/TD]
[TD="align: left"]The Lion & Other Stories[/TD]
[TD="align: left"]Monologue[/TD]
[TD="align: left"]Where? (Unit, Lesson)[/TD]
[TD] [/TD]
[TD="align: left"]Robert Rivera[/TD]
[TD]Not needed[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]491[/TD]
[TD="align: left"]***[/TD]
[TD="align: left"]Introduction to The Hotel[/TD]
[TD="align: left"]The Lion & Other Stories[/TD]
[TD="align: left"]Monologue[/TD]
[TD="align: left"]Where? (Unit, Lesson)[/TD]
[TD] [/TD]
[TD="align: left"]Robert Rivera[/TD]
[TD]Not needed[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]493[/TD]
[TD] [/TD]
[TD="align: left"]Surprise: [surprised face, mouth open. Up close][/TD]
[TD="align: left"]ASL Up Close (surprise)[/TD]
[TD="align: left"]Example[/TD]
[TD="align: left"]Where? (Unit, Lesson)[/TD]
[TD] [/TD]
[TD="align: left"]Norma Moran[/TD]
[TD]Not needed[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]494[/TD]
[TD] [/TD]
[TD="align: left"]Surprise: Recent watch TV, click-remote-control++. See news. Orange-eyes why? Spot my friend win fs-lotto![/TD]
[TD="align: left"]ASL Up Close (surprise)[/TD]
[TD="align: left"]Example[/TD]
[TD="align: left"]Where? (Unit, Lesson)[/TD]
[TD] [/TD]
[TD="align: left"]Norma Moran[/TD]
[TD]Not needed[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1640[/TD]
[TD] [/TD]
[TD="align: left"]Trip to Mexico[/TD]
[TD="align: left"]ABC Story[/TD]
[TD="align: left"]Literature[/TD]
[TD="align: left"]Unit 1, Lesson 1[/TD]
[TD] [/TD]
[TD="align: left"]Dennise Scott[/TD]
[TD]x[/TD]
[TD="align: left"]Blurry. Looks odd. [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1641[/TD]
[TD] [/TD]
[TD="align: left"]Abbe de l’Epee[/TD]
[TD="align: left"]Receptive communication[/TD]
[TD="align: left"]Monologue[/TD]
[TD="align: left"]Unit 1, Focus[/TD]
[TD] [/TD]
[TD="align: left"]Michelle Lapides[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1642[/TD]
[TD] [/TD]
[TD="align: left"]Home Layout Description 1[/TD]
[TD="align: left"]ASL Up Close (layout)[/TD]
[TD="align: left"]Description[/TD]
[TD="align: left"]Unit 2, Lesson 2[/TD]
[TD] [/TD]
[TD="align: left"]Michelle Lapides[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1643[/TD]
[TD] [/TD]
[TD="align: left"]Jack and the Beanstalk[/TD]
[TD="align: left"]Story[/TD]
[TD="align: left"]Literature[/TD]
[TD="align: left"]Unit 10, Lesson 1[/TD]
[TD] [/TD]
[TD="align: left"]Robin Shannon[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1644[/TD]
[TD] [/TD]
[TD="align: left"]My Dream Vacation[/TD]
[TD="align: left"]Receptive communication[/TD]
[TD="align: left"]Monologue[/TD]
[TD="align: left"]Unit 1, Lesson 1[/TD]
[TD] [/TD]
[TD="align: left"]Thomas Welch[/TD]
[TD]x[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1645[/TD]
[TD] [/TD]
[TD="align: left"]Italian Story[/TD]
[TD="align: left"]Story[/TD]
[TD="align: left"]Monologue[/TD]
[TD="align: left"]Unit 8, Lesson 2[/TD]
[TD] [/TD]
[TD="align: left"]Joette Paulone[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1646[/TD]
[TD] [/TD]
[TD="align: left"]Flower Pot at Pennsylvania School for the Deaf[/TD]
[TD="align: left"]Not using[/TD]
[TD="align: left"]Monologue[/TD]
[TD="align: left"]N/A, N/A[/TD]
[TD] [/TD]
[TD="align: left"]Joette Paulone[/TD]
[TD]Not needed[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]907g[/TD]
[TD="align: left"]v5179[/TD]
[TD="align: left"]The Rise of Oralism [/TD]
[TD="align: left"]486-***[/TD]
[TD="align: left"]Taking Care of Yourself[/TD]
[TD="align: left"]Unit 4, Focus [/TD]
[TD="align: left"]Deaf Awareness Ad (v5179)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]907h[/TD]
[TD="align: left"]v5180[/TD]
[TD="align: left"]Discovering Deaf Identity[/TD]
[TD="align: left"]491-***[/TD]
[TD="align: left"]Taking Care of Yourself[/TD]
[TD="align: left"]Unit 4, CTD[/TD]
[TD="align: left"]Identity (v5180)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]907i[/TD]
[TD="align: left"]v5181[/TD]
[TD="align: left"]Audism[/TD]
[TD="align: left"]907g-v5179[/TD]
[TD="align: left"]In My Free Time[/TD]
[TD="align: left"]Unit 5, Toolbox[/TD]
[TD="align: left"]The Deaf Man (v5181)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]907j[/TD]
[TD="align: left"]v5182[/TD]
[TD="align: left"]Double Minority: Black Deaf[/TD]
[TD="align: left"]907h-v5180[/TD]
[TD="align: left"]I Think….[/TD]
[TD="align: left"]Unit 6, Focus[/TD]
[TD="align: left"]Pay Attention (v5182)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]907k[/TD]
[TD="align: left"]v5183[/TD]
[TD="align: left"]Deaf Places[/TD]
[TD="align: left"]907i-v5181[/TD]
[TD="align: left"]I Think….[/TD]
[TD="align: left"]Unit 6, Toolbox[/TD]
[TD="align: left"]Rocky Mountain Deaf School (v5183)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]908a[/TD]
[TD="align: left"]v5184[/TD]
[TD="align: left"]Crime & Punishment[/TD]
[TD="align: left"]907j-v5182[/TD]
[TD="align: left"]News & Current Events[/TD]
[TD="align: left"]Unit 8, Lesson 2[/TD]
[TD="align: left"]First Responder (v5184)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]908aa[/TD]
[TD="align: left"]v5185[/TD]
[TD="align: left"]Talking About Government & Politics [/TD]
[TD="align: left"]907k-v5183[/TD]
[TD="align: left"]News & Current Events[/TD]
[TD="align: left"]Unit 8, Lesson 5[/TD]
[TD="align: left"]Elections Canada (v5185)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]908b[/TD]
[TD="align: left"]v5186[/TD]
[TD="align: left"]Collectivism[/TD]
[TD="align: left"]908a-v5184[/TD]
[TD="align: left"]News & Current Events[/TD]
[TD="align: left"]Unit 8, CTD[/TD]
[TD="align: left"]Am I the only Deaf Teenager? (v5186)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]908bb[/TD]
[TD="align: left"]v5187[/TD]
[TD="align: left"]Unusual Jobs[/TD]
[TD="align: left"]908aa-v5185[/TD]
[TD="align: left"]The Work World[/TD]
[TD="align: left"]Unit 9, Lesson 3[/TD]
[TD="align: left"]Deaf Mugger (v5187)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]908c[/TD]
[TD="align: left"]v5188[/TD]
[TD="align: left"]Rhyme[/TD]
[TD="align: left"]908b-v5186[/TD]
[TD="align: left"]Exploration 1 (Rhyme & Rhythm) [/TD]
[TD="align: left"]Exploration 1, Lesson 1[/TD]
[TD="align: left"]Three Little Pigs (v5188)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]908cc[/TD]
[TD="align: left"]v5189[/TD]
[TD="align: left"]Rhyme & Rhythm Together [/TD]
[TD="align: left"]908bb-v5187[/TD]
[TD="align: left"]Exploration 1 (Rhyme & Rhythm) [/TD]
[TD="align: left"]Exploration 1, Lesson 3[/TD]
[TD="align: left"]Grouchy Monkey (v5189)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]908d[/TD]
[TD="align: left"]v5190[/TD]
[TD="align: left"]Rhyme & Rhythm Together [/TD]
[TD="align: left"]908c-v5188[/TD]
[TD="align: left"]Exploration 1 (Rhyme & Rhythm) [/TD]
[TD="align: left"]Exploration 1, Lesson 3[/TD]
[TD="align: left"]ASL Song - Thanksgiving (v5190)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]908dd[/TD]
[TD="align: left"]v5191[/TD]
[TD="align: left"]The Rise of Oralism [/TD]
[TD="align: left"]908cc-v5189[/TD]
[TD="align: left"]Taking Care of Yourself[/TD]
[TD="align: left"]Unit 4, Focus [/TD]
[TD="align: left"]Oralists' Lies (v5191)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]908e[/TD]
[TD="align: left"]v5192[/TD]
[TD="align: left"]Discovering Deaf Identity[/TD]
[TD="align: left"]908d-v5190[/TD]
[TD="align: left"]Taking Care of Yourself[/TD]
[TD="align: left"]Unit 4, CTD[/TD]
[TD="align: left"]Pointless: Big D, Small D (v5192)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]908ee[/TD]
[TD="align: left"]v5193[/TD]
[TD="align: left"]Bio: George Veditz[/TD]
[TD="align: left"]908dd-v5191[/TD]
[TD="align: left"]Taking Care of Yourself[/TD]
[TD="align: left"]Unit 4, Toolbox[/TD]
[TD="align: left"]Who is George Veditz? (v5193)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]908f[/TD]
[TD="align: left"]v5194[/TD]
[TD="align: left"]You Have to Be Deaf to Understand [/TD]
[TD="align: left"]908e-v5192[/TD]
[TD="align: left"]I Think….[/TD]
[TD="align: left"]Unit 6, CTD[/TD]
[TD="align: left"]Yellow Diamond-Shaped (v5194)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]908ff[/TD]
[TD="align: left"]v5195[/TD]
[TD="align: left"]Talking About Government & Politics [/TD]
[TD="align: left"]908ee-v5193[/TD]
[TD="align: left"]News & Current Events[/TD]
[TD="align: left"]Unit 8, Lesson 5[/TD]
[TD="align: left"]Why are we not politically active (v5195)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]908g[/TD]
[TD="align: left"]v5196[/TD]
[TD="align: left"]Workplace Problems & Solutions [/TD]
[TD="align: left"]908f-v5194[/TD]
[TD="align: left"]The Work World[/TD]
[TD="align: left"]Unit 9, Lesson 4[/TD]
[TD="align: left"]nTouch Advertisement (v5196)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]908h[/TD]
[TD="align: left"]v5197[/TD]
[TD="align: left"]The Future of the Deaf Community[/TD]
[TD="align: left"]908ff-v5195[/TD]
[TD="align: left"]Changing Times[/TD]
[TD="align: left"]Unit 10, CTD[/TD]
[TD="align: left"]The End (v5197)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

please check it, i want to sort column A Alphanumerically and all other columns adjust automatically according to sorting of A column

any body can help

its urgent
thx

 

[Peter_SSs]

its urgent

It may be to you but when looking for free help as on this forum it isn't a good idea to sound demanding.
Refer also to point 12 of the Posting Guidelines

.. i want to sort column A Alphanumerically ..

1. From your post, it isn't possible to tell for sure where column A ends.

2. You may also assist helpers by explaining, and/or giving examples, what you mean by sorting "alphanumerically".

 

[imisy3d]

It may be to you but when looking for free help as on this forum it isn't a good idea to sound demanding.
Refer also to point 12 of the Posting Guidelines



1. From your post, it isn't possible to tell for sure where column A ends.

2. You may also assist helpers by explaining, and/or giving examples, what you mean by sorting "alphanumerically".

ok
[TABLE="width: 72"]
<colgroup><col width="72" style="width:54pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 72"]1
1640
1646
168
169
486
491
493
494
907g
907h
908ee
908ff
908g
908h
[/TD]
[/TR]
</tbody>[/TABLE]
these should be like
[TABLE="width: 72"]
<colgroup><col width="72" style="width:54pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 72"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]168[/TD]
[/TR]
[TR]
[TD="class: xl63"]169[/TD]
[/TR]
[TR]
[TD="class: xl63"]486[/TD]
[/TR]
[TR]
[TD="class: xl63"]491[/TD]
[/TR]
[TR]
[TD="class: xl63"]493[/TD]
[/TR]
[TR]
[TD="class: xl63"]494[/TD]
[/TR]
[TR]
[TD="class: xl63"]907g[/TD]
[/TR]
[TR]
[TD="class: xl63"]907h[/TD]
[/TR]
[TR]
[TD="class: xl63"]908ee[/TD]
[/TR]
[TR]
[TD="class: xl63"]908ff[/TD]
[/TR]
[TR]
[TD="class: xl63"]908g[/TD]
[/TR]
[TR]
[TD="class: xl63"]908h[/TD]
[/TR]
[TR]
[TD="class: xl63"]1640[/TD]
[/TR]
[TR]
[TD="class: xl63"]1646


[/TD]
[/TR]
</tbody>[/TABLE]
total rows are A1 to A7000 while in columns A1 to L1
if i sort column A, then other columns text should be adjusted as per sort of column a
thx

 

[Peter_SSs]

Thanks, that's clearer.

I've assumed there are headings in row 1 and the data starts in row 2. Advise if that is not the case.

Test in a copy of your workbook.

Sub SortNumberAlpha()
  Dim a
  Dim nc As Long, i As Long, ubA As Long
  Dim v As Double
  Dim s As String
  
  nc = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, _
    SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, SearchFormat:=False).Column + 1
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  ubA = UBound(a, 1)
  For i = 1 To ubA
    s = a(i, 1)
    v = Val(s)
    a(i, 1) = Format(v, String(10, "0")) & Mid(s & "z", 1 + Len(CStr(v)))
  Next i
  Application.ScreenUpdating = False
  With Range("A2").Resize(ubA, nc)
    .Columns(nc).Value = a
    .Sort Key1:=.Cells(1, nc), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, _
      MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    .Columns(nc).ClearContents
  End With
  Application.ScreenUpdating = True
End Sub

 

[imisy3d]

Thanks, that's clearer.

I've assumed there are headings in row 1 and the data starts in row 2. Advise if that is not the case.

Test in a copy of your workbook.

Sub SortNumberAlpha()
  Dim a
  Dim nc As Long, i As Long, ubA As Long
  Dim v As Double
  Dim s As String
  
  nc = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, _
    SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, SearchFormat:=False).Column + 1
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  ubA = UBound(a, 1)
  For i = 1 To ubA
    s = a(i, 1)
    v = Val(s)
    a(i, 1) = Format(v, String(10, "0")) & Mid(s & "z", 1 + Len(CStr(v)))
  Next i
  Application.ScreenUpdating = False
  With Range("A2").Resize(ubA, nc)
    .Columns(nc).Value = a
    .Sort Key1:=.Cells(1, nc), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, _
      MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    .Columns(nc).ClearContents
  End With
  Application.ScreenUpdating = True
End Sub

bundle of thx ... its working perfectly
again thx

 

[imisy3d]

one problem is there
[TABLE="width: 72"]
<colgroup><col width="72" style="width:54pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 72"]023a[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 72"]023b[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 72"]23

see this, 23 should come first then 23a, and 23b

can you please solve it[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

one problem is there

Yes, my mistake. Try changing this line only.

a(i, 1) = "z" & Format(v, String(10, "0")) & Mid(s, 1 + Len(CStr(v)))

 

[imisy3d]

thx for your kind help
now its fine

 

[Peter_SSs]

thx for your kind help
now its fine

Good news. Thanks for letting us know.

 

[imisy3d]

hi how are you
sir
today i again need you help
file is here
https://www.dropbox.com/s/nf0aqng6yl7sld6/Outlook_Contacts_8-24-2014__sue.xlsx?dl=0

what i want to do
see row 1039 and 1040
name and email are same, just the difference is L1040 and O1040
i want if the email is same, then vba arrange data in one row and remove the other row

need you kind help

thx in advance