Sorting with a macro?

[gschimek]

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)

 

[pfsardella]

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

 

[Myrna Larson]

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