Automatic sorting in excel
 

I would like to be able to have a column sorted alphabetically contiuously.
In other words, whenever data is entered, the whole column gets arranged
alphabetically without having to "click' on the sort function.

I am not a power user of excel, but I now understand the basics of running a
macro. I have been guided to use the following macro example and adapt it to
my worksheet. I have tried a few changes, but I cannot get it to work. Lets
say the filename is "mybook" and the worksheet name is "sheet1" and the
range I want to sort automatically is B1:B20

Private Sub Worksheet_Change(ByVal Target As Range)


Columns("B:B").Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending

End Sub

However, I am not sure exactly which part I need to change in this code, for
it to work on my worksheet.


Thanks.

Automatic sorting in excel
 

See:

http://www.microsoft.com/communities...1-7676c90b18d3
--
Gary's Student


"Gostal" wrote:

I would like to be able to have a column sorted alphabetically contiuously.
In other words, whenever data is entered, the whole column gets arranged
alphabetically without having to "click' on the sort function.

I am not a power user of excel, but I now understand the basics of running a
macro. I have been guided to use the following macro example and adapt it to
my worksheet. I have tried a few changes, but I cannot get it to work. Lets
say the filename is "mybook" and the worksheet name is "sheet1" and the
range I want to sort automatically is B1:B20

Private Sub Worksheet_Change(ByVal Target As Range)


Columns("B:B").Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending

End Sub

However, I am not sure exactly which part I need to change in this code, for
it to work on my worksheet.


Thanks.

Automatic sorting in excel
 

Let's see if we can't roll all the answers up into one place. It looked to
me like, in another post, you are having trouble figuring out how to get the
code into the proper place in the Excel workbook. The code people have
pointed you to needs to go into the worksheet's code segment. Having said
that, you're probably scratching your head saying "where the #@!!? is that"?

First: here is a complete code segment that will detect a change in column B
on a sheet and sort the contents of column B, beginning with cell B1 and
continuing down to the first empty cell in that column. Changes in other
areas of the worksheet will not cause anything to happen.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim whereIam As Range
Set whereIam = ActiveCell
If Intersect(Target, Range("B:B")) Is Nothing Then
Exit Sub
End If
Range("B1:" & Range("B1").End(xlDown).Address).Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending
'and back to where you started
whereIam.Select
End Sub

Second: Copy that code and open your workbook and select the sheet that it
needs to work with. Right-Click on the worksheet's name tab and choose View
Code from the list. Paste all of the code right into the area that shows up.
Close that window and then take it for a test drive.

"Gostal" wrote:

I would like to be able to have a column sorted alphabetically contiuously.
In other words, whenever data is entered, the whole column gets arranged
alphabetically without having to "click' on the sort function.

I am not a power user of excel, but I now understand the basics of running a
macro. I have been guided to use the following macro example and adapt it to
my worksheet. I have tried a few changes, but I cannot get it to work. Lets
say the filename is "mybook" and the worksheet name is "sheet1" and the
range I want to sort automatically is B1:B20

Private Sub Worksheet_Change(ByVal Target As Range)


Columns("B:B").Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending

End Sub

However, I am not sure exactly which part I need to change in this code, for
it to work on my worksheet.


Thanks.