sorting

[Guest]

I know there are many posts on this already but they all seem to be question specific. I have a sheet with contact info I have first names in column A, last names in column B and different info of each person across to column H all the info to be sorted starts at row 5. I want to sort the info by last names column B and have it sort with the rows for each person including first name in column A. and be able to add names in the future and it always sort. Thanks for all the help. I love you guys ! Matt

 

[David McRitchie]

Hi Matthew,
The safest way to sort is to
1) Select all cells with Ctrl+A so that anything
you sort on will bring the columns on the same row along with it.
2) Data, Sort, Column B - ascending probably [x] has headers

The worst thing you can do is to
1) select a column B and use the ascending sort button, as only
column B will be moved around and is the problem you are trying
to avoid.
2) selecting a single cell is almost as bad you never know what you
are going to get as Excel determines what your current region is.

Another way that is safe providing you do exactly as indicated.
1) Select a cell in the column you want to sort on
2) Select all cells Ctrl+A the original cell you selected should still
be active with all cells selected -- if not true don't continue
3) click on the ascending sort icon
They main problem you may have with this method is whether Excel
will properly choose whether you have a header row or not.

Not guaranteed but for Excel to identify whether you have a header
row, make your header row bold so it has different type of content
than the data.

Don't know what you mean by
"and be able to add names in the future and it always sort "
If you mean automatically sort -- I wouldn't.

You should know from your search of Google groups that choosing
a one word title for your subject is not a good idea. Please try for
a unique title by adding a few more descriptive words. And avoid
words that are in the newsgroup name or words like help. Try to make
the subject closer to being unique with a few more words.

I know there are many posts on this already but they all seem to be question specific. I have a sheet with contact info I have

first names in column A, last names in column B and different info of each person across to column H all the info to be sorted
starts at row 5. I want to sort the info by last names column B and have it sort with the rows for each person including first name
in column A. and be able to add names in the future and it always sort. Thanks for all the help. I love you guys ! Matt

 

[Guest]

David thanks for help but I dont think I said what I wanted very clearly. I have some information above row 5 that I do not want included in the sort and I want all the rows from 6 to ? to be sorted by the contents in column B. I also want any new names I add to be sorted automatically by column b contents as above so I do not have to reselct all the info I want to sort every single time I add a new name. The sorting part isn't what I actually am having problems with its the getting it to sort automatically every time a new entry is added. Thanks for any more help. I'll try to make my subjects more specific in the future.

 

[David McRitchie]

Hi Matthew,
So you pre select rows 6 on down and then then sort on column B ascending.

I really would avoid sorting automatically, I think it will confuse you as to where
you are updating. The type of macro you use would be an event macro.
Perhaps the least confusion would be caused by sorting upon activation or
upon deactivation of the worksheet. Event Macros see
http://www.mvps.org/dmcritchie/excel/event.htm .

I think this would cause confusion, install in worksheet by right click on
worksheet tab and paste the code in place of the Option Explicit statement.:
As soon as you are finished with column B entry, it is sorted and you will
be entering column C into the wrong cell. You can try this to see why
it is a bad idea. Try to update a moving target.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
if target.column <> 2 then exit sub
activecell.
Rows("6:65536").Sort Key1:=Range("B6"), _
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub

This would be better, get rid of the above and paste in this code

Option Explicit
Private Sub Worksheet_Activate()
Rows("6:65536").Sort Key1:=Range("B6"), _
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub

David thanks for help but I dont think I said what I wanted very clearly. I have some information above row 5 that I do not want

included in the sort and I want all the rows from 6 to ? to be sorted by the contents in column B. I also want any new names I add
to be sorted automatically by column b contents as above so I do not have to reselct all the info I want to sort every single time I
add a new name. The sorting part isn't what I actually am having problems with its the getting it to sort automatically every time a
new entry is added. Thanks for any more help. I'll try to make my subjects more specific in the future.

 

[Tom Ogilvy]

At what point do you want it to sort. Do you want to sort as soon as you
enter the lastname in column B (that would seem distracting to me - then you
would have to find it to complete entering other data.

Anyway, you can use the worksheet_change event

Assume you have headers in Row 5

Right click on the sheet tab and select view code. Put in code like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, rng1 As Range, rng2 As Range
If Target.Count > 1 Then Exit Sub
If Target.Column < 9 And Target.Column > 1 Then
Set rng = Range(Cells(5, 2), Cells(Rows.Count, 2).End(xlUp))
Set rng1 = rng.Offset(0, -1).Resize(, 8)
Set rng2 = rng(rng.Count).Offset(0, -1).Resize(1, 8)
If Not Intersect(Target, rng1) Is Nothing Then
If Application.CountA(rng2) = 8 Then
rng1.Sort Key1:=Range("B6"), _
Order1:=xlAscending, _
Header:=xlYes
End If
End If
End If
End Sub

--
Regards,
Tom Ogilvy

David thanks for help but I dont think I said what I wanted very clearly.

I have some information above row 5 that I do not want included in the sort
and I want all the rows from 6 to ? to be sorted by the contents in column
B. I also want any new names I add to be sorted automatically by column b
contents as above so I do not have to reselct all the info I want to sort
every single time I add a new name. The sorting part isn't what I actually
am having problems with its the getting it to sort automatically every time
a new entry is added. Thanks for any more help. I'll try to make my subjects
more specific in the future.