Thread
:
Is there a better way (with less typing)?
View Single Post
#
1
Posted to microsoft.public.excel.programming
Don Guillett[_4_]
external usenet poster
Posts: 2,337
Is there a better way (with less typing)?
If this (sort each column individually) is really what you want, try
Sub sorteachcol()
colcount = ActiveSheet.UsedRange.Columns.Count
For i = 1 To colcount Step 1
x = Left(Cells(1, i).Address(, 0), InStr(Cells(1, i).Address(, 0), "$") - 1)
MsgBox x
Columns(x & ":" & x).Sort Key1:=Cells(1, x), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Next
End Sub
--
Don Guillett
SalesAid Software
"rpw" wrote in message
...
Hi everyone,
I've got this macro that sorts 600 cells of a column, then moves to the
next
column and sorts it, and so on. The macro works fine, it's just that I
have
250 columns to sort individually. Is there a way of doing this without
having to type in 250 different cell locations? (e.g. "B1", "C1", etc.).
I was able to select a range, then offset it by one to select the next
range, but I can't figure out how to do a similar thing with the sort Key1
range.
Here's the code:
Dim IntCtr As Long
Dim myColumn As Variant
myColumn = Array("B1", "C1", "D1", "E1", "F1", "G1", "h1", "i1", _
"j1", "k1", "l1", "m1", "n1", "o1", "p1", "q1", "r1", "s1", _
"t1", "u1", "v1", "w1", "x1", "y1", "z1", "aa1")
Worksheets("Category Lists").Activate
Worksheets("Category Lists").Range("A1:A601").Select
For IntCtr = LBound(myColumn) To UBound(myColumn)
Selection.Offset(0, 1).Range("A1:A601").Select
Selection.Sort Key1:=Range(myColumn(IntCtr)),
Order1:=xlDescending,
Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Next IntCtr
Worksheets("Create Task List").Select
Range("G13").Activate
End Sub
Thanks for any help you can provide.
--
rpw
Reply With Quote
Don Guillett[_4_]
View Public Profile
Find all posts by Don Guillett[_4_]