Macro for Multiple Sorts
Try this, I tried with your data and it worked.
Create a cell to hold how many elements in the column.
Example: B50's formula =CountA(B2:B49)
C50's formula =CountA(C2:C49)
D50's formula =CountA(D2:D49)
Sub SortColumns()
Dim i As Integer
Dim count As Integer
Dim ColumnCount As Integer
i = 2 'The sorting starts at row 2
ColumnCount = 3 'Number of columns to be sorted
For j = 1 To ColumnCount
count = Cells(50, j + 1).Value
Range(Cells(2, j + 1), Cells(49, j + 1)).sort Cells
(2, j + 1)
Range(Cells(2, j + 1), Cells(count + 2, j +
1)).Cut (Cells(i, j + 1))
i = i + count
Next j
End Sub
-----Original Message-----
Using Excel 2000.
We have hundreds of rows of data that look like this:
A B C D E F G
1 Descr 1 hr 2 hrs 3 hrs 4 hrs 5 hrs 6 hrs
2 da 62
3 rf 24
4 aa 6
5 fr 5
6 cd 4
7 as 55
8 xx 14
The workbook has data in every row under column A
and one entry in one column (B-G) for each row.
We want to get a macro to perfrom multiple sorts on the
workbook so that it will look like this when finished:
A B C D E F G
1 Descr 1 hr 2 hrs 3 hrs 4 hrs 5 hrs 6 hrs
2 cd 4
3 fr 5
4 aa 6
5 as 55
6 da 62
7 xx 14
8 rf 24
We want to sort on column B. Then sort on the rest of
the
worksheet on cloumn C. Then column D, E, and F.
We got the first part (the easy one) but can't figure out
how to get the macro to go to the next and succeeding
steps. After the first sort, I think that we need for the
macro to look for the first blank row under column B,
range select the rest of the worksheet and sort on C.
Then
go to the first blank row under Column C, range select
the
rest of the worksheet, and sort on Column D. Etc.
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells
(xlLastCell)).Select
Selection.Sort Key1:=Range("B2"),
Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Can someone help us here, please?
.
|