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

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.

Oh my god, I totally forgot about that. I've got something to work with now. Thanks for stating the obvious :) very much appreciated!!!
Em