Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
How do I remove the annoying automatic date formatting in Excel? | Excel Discussion (Misc queries) | |||
Excel Date Format - users should be able to override it automatic. | Excel Discussion (Misc queries) | |||
Multiple Excel versions. | Excel Discussion (Misc queries) | |||
Excel: Allow to turn on / off automatic header selection in sorts | Excel Discussion (Misc queries) |