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.
|