Need macro to Sort Column A starting A2 to N in ascending order
-
- Lounger
- Posts: 29
- Joined: 03 Sep 2020, 05:10
Need macro to Sort Column A starting A2 to N in ascending order
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
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
-
- Administrator
- Posts: 78541
- 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
Hi Jude0887,
Please don't direct all your questions at me. Others are actively answering questions too.
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
Hans
-
- Lounger
- Posts: 29
- Joined: 03 Sep 2020, 05:10
Re: Need macro to Sort Column A starting A2 to N in ascending order
Hello with this macro it is throwing an error message stating
Run time error ‘1004’
Application defined or object Defined error
Run time error ‘1004’
Application defined or object Defined error
-
- Administrator
- Posts: 78541
- 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
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
Hans
-
- Lounger
- Posts: 29
- Joined: 03 Sep 2020, 05:10
Re: Need macro to Sort Column A starting A2 to N in ascending order
Hello
Please find the attachement these are my sample files .
Please find the attachement these are my sample files .
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78541
- 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
The code works fine on your sample workbook:
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 29
- Joined: 03 Sep 2020, 05:10
Re: Need macro to Sort Column A starting A2 to N in ascending order
There is space after XYZ and then underscore. And is it bcoz of which it is showing error for me
-
- Administrator
- Posts: 78541
- 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
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?
What happens when you click the macro?
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 29
- Joined: 03 Sep 2020, 05:10
Re: Need macro to Sort Column A starting A2 to N in ascending order
Finally i think it will work fine but this time facing an 400 error. And advice on how to fix it
-
- Administrator
- Posts: 78541
- 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
Does this happen in the workbook that I attached?
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 29
- Joined: 03 Sep 2020, 05:10
Re: Need macro to Sort Column A starting A2 to N in ascending order
No it doesn have any problem with the one u attached
-
- Administrator
- Posts: 78541
- 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
Could you attach a workbook in which the macro does cause problems?
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 29
- Joined: 03 Sep 2020, 05:10
Re: Need macro to Sort Column A starting A2 to N in ascending order
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
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
-
- Administrator
- Posts: 78541
- 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
Like this:
See the attached version.
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
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 29
- Joined: 03 Sep 2020, 05:10
Re: Need macro to Sort Column A starting A2 to N in ascending order
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
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.
-
- Administrator
- Posts: 78541
- 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
Does your worksheet contain merged cells? They are often the cause of this error message.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 12615
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Need macro to Sort Column A starting A2 to N in ascending order
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
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