Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Automatically sort alphabetically ignoring header
Hey everyone!
I am sure someone can help me on here. I found an amazing post on this website which really helped me with getting something to work with automatically alphabeticising a column, but something went wrong with the previous file - it corrupted during a migration - and now it won't work. I'm trying to do the same thing again, but cannot find the post and I swear I have gone through hundreds of posts on this website and the code that I have tried each and every time isn't the same one I found before. Here is what I would like to do: * Alphabeticize column A on Excel, starting from A5 onwards * When it sorts, it must keep the information on that row from columns A - G (so when say, row 10 moves up to row 8, it doesn't just move column A, it also moves columns B-G with it so the information is relevant). * It automatically does this once something is entered into column A, and either enter is hit or another box is selected I understand that once something is entered in column A and the data is sorted, it usually gets lost in the remainder of the spreadsheet but this was something we worked with in the past. Also, please note that Column A (and up to G, with the exception of two columns) are all text based, not numeric. Not sure if this makes a difference. Any help is much appreciated. Currently running on Office 2010. And this was the code I kept finding, but no matter how I amend it, it ALWAYS takes the first 4 rows instead of starting alphabetically from the fifth. Maybe someone could assist and let me know where I'm going wrong, so I can learn it instead of just being told? Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Columns("A:A")) Is Nothing Then Else Application.EnableEvents = False Columns("A:Z").Select Selection.Sort Key1:=Range("A5"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=5, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Application.EnableEvents = True End If End Sub |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically sort alphabetically ignoring header
On Monday, October 15, 2012 10:48:47 AM UTC-5, EmzOLV wrote:
Hey everyone! I am sure someone can help me on here. I found an amazing post on this website which really helped me with getting something to work with automatically alphabeticising a column, but something went wrong with the previous file - it corrupted during a migration - and now it won't work. I'm trying to do the same thing again, but cannot find the post and I swear I have gone through hundreds of posts on this website and the code that I have tried each and every time isn't the same one I found before. Here is what I would like to do: * Alphabeticize column A on Excel, starting from A5 onwards * When it sorts, it must keep the information on that row from columns A - G (so when say, row 10 moves up to row 8, it doesn't just move column A, it also moves columns B-G with it so the information is relevant). * It automatically does this once something is entered into column A, and either enter is hit or another box is selected I understand that once something is entered in column A and the data is sorted, it usually gets lost in the remainder of the spreadsheet but this was something we worked with in the past. Also, please note that Column A (and up to G, with the exception of two columns) are all text based, not numeric. Not sure if this makes a difference. Any help is much appreciated. Currently running on Office 2010. And this was the code I kept finding, but no matter how I amend it, it ALWAYS takes the first 4 rows instead of starting alphabetically from the fifth. Maybe someone could assist and let me know where I'm going wrong, so I can learn it instead of just being told? Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Columns("A:A")) Is Nothing Then Else Application.EnableEvents = False Columns("A:Z").Select Selection.Sort Key1:=Range("A5"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=5, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Application.EnableEvents = True End If End Sub -- EmzOLV If you simply record a macro doing this, wihtout header, you will see how to modify your code. |
#3
|
|||
|
|||
Quote:
Em |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically Sort Alphabetically | Excel Discussion (Misc queries) | |||
How do I sort A1 THROUGH F26 alphabetically? | Excel Discussion (Misc queries) | |||
how to sort non-alphabetically | New Users to Excel | |||
sort by alphabetically | New Users to Excel | |||
How can I sort many items alphabetically, ignoring page breaks?? | Excel Worksheet Functions |