Macro help

J

Jeff Garrett

Hi,
I am working on a macro in Excel 2003 Pro, and everytime I go to sort a
column of data. There are about 10 rows of data in about 200+ columns. I
can only get the macro to sort the column that I edited it with. How can I
create the macro, so each time I click on the first row of each column, click
the macro button I will assign to the toolbar, then that row will be sorted,
and do this for each column?

Any help at all would be greatly appreciated,
Jeff Garrett ([email protected])
 
J

JulieD

Hi Jeff

please post your current code and then we can suggest changes to it.

Cheers
JulieD
 
D

Don Guillett

Sub Sort()'where sortrange is a named range to sort a1:aa200
[SortRangeAdd].Sort Key1:=Cells(1, activecell.column), Order1:=xlAscending,
Orientation:=xlTopToBottom
End Sub
 
G

Guest

posting your code would help.
Macros do not respond to mouse clicks or other forms of
input divices. the code replaces all these. mouse clicks
can trigger code to run but they can't be used as code
parameters.
you might try in the code to sort by Activecell but i
have never tried that and am not too sure it would work.
 
J

Jeff Garrett

Sub Macro1()
Dim varColumn As String
Dim varNo As String
For varColumn = "A" To "IV"
varNo = varColumn & 1
Range([varNo]).Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range(ActiveCell, ActiveCell.End(xlUp)).Select,
Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Next varColumn
End Sub


Basically, I want to go from Cell B1 to Cell IV1, and call this macro to
sort the column that is currently selected descendingly. It will be all
integers, some non-entries representing a 0. The whole reason to do a macro
is because there is like 200 entries in the worksheet.

Any and all suggestions are welcome.
 
D

Don Guillett

try this to sort EACH column individually. Is that what you want?

Sub SortEachColumn()
lc = Cells(1, "iv").End(xlToLeft).Column
For i = 1 To lc
lr = Cells(Rows.Count, i).End(xlUp).Row
Range(Cells(2, i), Cells(lr, i)).Sort key1:=Cells(2, i), Order1:=xlAscending
Next i
End Sub
 
J

Jeff Garrett

Yes. Sort each column individually. Then, iterate over each column. Do I
have to dim lc, lr as variables? What kind though? String? Char?
 
J

Jeff Garrett

Order1:=xlAscending
This means ascending order (lowest to highest) right? I want descending
order. So I put this:
Order1:=xlDescending

Correct?
 
J

Jeff Garrett

That's worked Don. The only problem is it's doing too much. It should only
sort rows 2-12 inclusive, and this is what we last came up with:
lr = Cells(Rows.Count, i).End(xlUp).Row
This does too much. How do you do only sort the rows 2-12?
 
D

Don Guillett

It is sorting row 2 - 12 if 12 is the last row with data. It would be 234 if
that was the last row with data. I thought you wanted it to be adaptable for
the amount of date in each column. If not, change to 12.....
 
Top