I want to automate a sorting process for a spreadsheet that I have. On
this sheet, I have various groups of names that I want to sort
alphabetically within each group. For example, cells A1:A10 should be
sorte a-z, cells A11:A20 sorted a-z, etc.

I will be updating this list from time to time with more names, and
I'll need to resort it each time. There are 10 groups of names that I
want sorted, and it's very tedious to select 10 different sets of cells
and then tell it how to sort 10 different times.

I've created a macro to do this, which works great. My problem is that
if I allow 15 cells for each set of names (so, 15 names) and 6 months
from now I need to add two rows because I have 17 names in a particular
category, the macro I've created won't sort properly, because it's only
expecting a range of 15 cells.

Is there a way to make a sort function that will take into account a
change in the number of cells in a group?

(I hope you can understand this from my description)

Without seeing your code, it's difficult to say, but ...........

Watch for line wrap. Use the intI value for the number in the sort.

Dim intI As Integer

intI = Application.InputBox(Prompt:="Enter the number of rows to
sort.", Type:=1, Default:=10)

If intI = 0 Then Exit Sub

HTH
Paul
-----------------------------

On Fri, 29 Aug 2003 16:20:48 -0400, gschimek
wrote:

I want to automate a sorting process for a spreadsheet that I have. On
this sheet, I have various groups of names that I want to sort
alphabetically within each group. For example, cells A1:A10 should be
sorte a-z, cells A11:A20 sorted a-z, etc.

I will be updating this list from time to time with more names, and
I'll need to resort it each time. There are 10 groups of names that I
want sorted, and it's very tedious to select 10 different sets of cells
and then tell it how to sort 10 different times.

I've created a macro to do this, which works great. My problem is that
if I allow 15 cells for each set of names (so, 15 names) and 6 months
from now I need to add two rows because I have 17 names in a particular
category, the macro I've created won't sort properly, because it's only
expecting a range of 15 cells.

Is there a way to make a sort function that will take into account a
change in the number of cells in a group?

(I hope you can understand this from my description)

I assume that all 10 groups are assigned the same number of rows, right? i.e. if you add 2 items
to the 2nd group, you will also add 2 to every one of the groups. If that's correct, you could
put the number of rows in a group in a worksheet cell, or define it as a constant or variable at
the top of your module. Modify the code to read this value and define the group size
accordingly. i.e.

Dim i As Long
Dim NumRows As Long
Dim Rng As Range

NumRows = Worksheets("Sheet1").Range("K1").Value
Set Rng = Range("A1").Resize(NumRows)
For i = 1 to 10
Rng.Sort .....
Set Rng = Rng.Offset(NumRows, 0)
Next i


On Fri, 29 Aug 2003 16:20:48 -0400, gschimek wrote:

I want to automate a sorting process for a spreadsheet that I have. On
this sheet, I have various groups of names that I want to sort
alphabetically within each group. For example, cells A1:A10 should be
sorte a-z, cells A11:A20 sorted a-z, etc.

I will be updating this list from time to time with more names, and
I'll need to resort it each time. There are 10 groups of names that I
want sorted, and it's very tedious to select 10 different sets of cells
and then tell it how to sort 10 different times.

I've created a macro to do this, which works great. My problem is that
if I allow 15 cells for each set of names (so, 15 names) and 6 months
from now I need to add two rows because I have 17 names in a particular
category, the macro I've created won't sort properly, because it's only
expecting a range of 15 cells.

Is there a way to make a sort function that will take into account a
change in the number of cells in a group?

(I hope you can understand this from my description)