Need macro to Sort Column A starting A2 to N in ascending order

Jude0887
Lounger
Posts: 29
Joined: 03 Sep 2020, 05:10

Need macro to Sort Column A starting A2 to N in ascending order

Post by Jude0887 »

Hello Hans,

Need your help on this
Need macro to Sort Column A starting from A2 to A(N) in ascending order.

Say for example i usually have my Column A with datas such as
A
ABC XYZ_200910_FC3.pdf
ABC XYZ_200910_FC6.pdf
ABC XYZ_200910_FC9.pdf
ABC XYZ_200910_FC1.pdf
ABC XYZ_200910_FC8.pdf
ABC XYZ_200910_FC2.pdf
ABC XYZ_200910_FC4.pdf
ABC XYZ_200910_FC10.pdf
ABC XYZ_200910_FC7.pdf
ABC XYZ_200910_FC5.pdf

Here i have given an sample of 10 files . Is it possible to have the above in order without changing of file name but to have the outcome in ascending order like below

ABC XYZ_200910_FC1.pdf
ABC XYZ_200910_FC2.pdf
ABC XYZ_200910_FC3.pdf
ABC XYZ_200910_FC4.pdf
.
.
.
.
ABC XYZ_200910_FC9.pdf
ABC XYZ_200910_FC10.pdf

Thanks in advance

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Need macro to Sort Column A starting A2 to N in ascending order

Post by HansV »

Hi Jude0887,

Please don't direct all your questions at me. Others are actively answering questions too.

Code: Select all

Sub SortData()
    Dim m As Long
    Application.ScreenUpdating = False
    m = Range("A" & Rows.Count).End(xlUp).Row
    Range("B1").EntireColumn.Insert
    Range("B2:B" & m).Formula = "=--MID(A2,FIND(""_FC"",A2)+3,FIND("".pdf"",A2)-FIND(""_FC"",A2)-3)"
    Range("A2:B" & m).Sort Key1:=Range("B2"), Header:=xlNo
    Range("B1").EntireColumn.Delete
    Application.ScreenUpdating = True
End Sub
Best wishes,
Hans

Jude0887
Lounger
Posts: 29
Joined: 03 Sep 2020, 05:10

Re: Need macro to Sort Column A starting A2 to N in ascending order

Post by Jude0887 »

Hello with this macro it is throwing an error message stating
Run time error ‘1004’
Application defined or object Defined error

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Need macro to Sort Column A starting A2 to N in ascending order

Post by HansV »

The code works for me without error. Could you attach a small sample workbook with the filenames and with your version of the macro to a reply? Thanks in advance.
Best wishes,
Hans

Jude0887
Lounger
Posts: 29
Joined: 03 Sep 2020, 05:10

Re: Need macro to Sort Column A starting A2 to N in ascending order

Post by Jude0887 »

Hello

Please find the attachement these are my sample files .
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Need macro to Sort Column A starting A2 to N in ascending order

Post by HansV »

The code works fine on your sample workbook:

S3525.png
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

Jude0887
Lounger
Posts: 29
Joined: 03 Sep 2020, 05:10

Re: Need macro to Sort Column A starting A2 to N in ascending order

Post by Jude0887 »

There is space after XYZ and then underscore. And is it bcoz of which it is showing error for me

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Need macro to Sort Column A starting A2 to N in ascending order

Post by HansV »

Here is your workbook (now a macro-enabled workbook) with the macro, and a command button to run the macro.
What happens when you click the macro?

Sorting in ascending.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

Jude0887
Lounger
Posts: 29
Joined: 03 Sep 2020, 05:10

Re: Need macro to Sort Column A starting A2 to N in ascending order

Post by Jude0887 »

Finally i think it will work fine but this time facing an 400 error. And advice on how to fix it

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Need macro to Sort Column A starting A2 to N in ascending order

Post by HansV »

Does this happen in the workbook that I attached?
Best wishes,
Hans

Jude0887
Lounger
Posts: 29
Joined: 03 Sep 2020, 05:10

Re: Need macro to Sort Column A starting A2 to N in ascending order

Post by Jude0887 »

No it doesn have any problem with the one u attached

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Need macro to Sort Column A starting A2 to N in ascending order

Post by HansV »

Could you attach a workbook in which the macro does cause problems?
Best wishes,
Hans

Jude0887
Lounger
Posts: 29
Joined: 03 Sep 2020, 05:10

Re: Need macro to Sort Column A starting A2 to N in ascending order

Post by Jude0887 »

Hello Hans it is working fine i had some problem with my excel. One query on this.
If there is any file number which is missing in the order say

ABC XYZ_200910_FC1.pdf
ABC XYZ_200910_FC2.pdf
ABC XYZ_200910_FC3.pdf
ABC XYZ_200910_FC4.pdf
ABC XYZ_200910_FC6.pdf
ABC XYZ_200910_FC7.pdf
ABC XYZ_200910_FC8.pdf
ABC XYZ_200910_FC9.pdf
ABC XYZ_200910_FC10.pdf

In this case file File 5 is missing
ABC XYZ_200910_FC5.pdf

Is it possible to find the missing one when sorting happens from 1 to N
Like throwing a message that the order is missing or the file 5 is missing anything like that
Or filling the missing file name and highlighting it in red so that we are aware that the order is missing
TIA

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Need macro to Sort Column A starting A2 to N in ascending order

Post by HansV »

Like this:

Code: Select all

Sub SortData()
    Dim s As String
    Dim r As Long
    Dim t As Long
    Dim m As Long
    Application.ScreenUpdating = False
    s = Range("A2").Value
    s = Left(s, InStr(s, "_FC") + 2)
    m = Range("A" & Rows.Count).End(xlUp).Row
    Range("B1:B1").EntireColumn.Insert
    With Range("B2:B" & m)
        .Formula = "=--MID(A2,FIND(""_FC"",A2)+3,FIND("".pdf"",A2)-FIND(""_FC"",A2)-3)"
        .Value = .Value
    End With
    Range("A2:B" & m).Sort Key1:=Range("B2"), Header:=xlNo
    For r = m To 3 Step -1
        For t = 0 To Range("B" & r).Value - Range("B" & r - 1).Value - 2
            Range("A" & r + t).EntireRow.Insert
            Range("A" & r + t).Value = s & Range("B" & r - 1).Value + t + 1 & ".pdf"
            Range("A" & r + t).Interior.Color = vbYellow
        Next t
    Next r
    Range("B1").EntireColumn.Delete
    Application.ScreenUpdating = True
End Sub
See the attached version.

Sorting in ascending.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

Jude0887
Lounger
Posts: 29
Joined: 03 Sep 2020, 05:10

Re: Need macro to Sort Column A starting A2 to N in ascending order

Post by Jude0887 »

Hello all

Regarding this sorting . I had some 138 files in random order. And it had file 130 missing . When i tried sorting and i am getting the attached error message.

Actually when i am sorting it should arrange the files in column A from 1 to N ( ascending order) and highlight the missing file number in red.

Request you to please review the attached and advice me

Many thanks in advance
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Need macro to Sort Column A starting A2 to N in ascending order

Post by HansV »

Does your worksheet contain merged cells? They are often the cause of this error message.
Best wishes,
Hans

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Need macro to Sort Column A starting A2 to N in ascending order

Post by StuartR »

There could be something in a cell that you think is empty.

Select the first completely empty row that is below your data.
Control-shift-downarrow to select from there to the bottom of the worksheet
Right click - Delete to delete all the empty rows in the workbook

NOTE: This will delete any data in the selected rows. Make sure that you don't have any data that you want selected
StuartR