VBA Multiple Column Sort

[scvba]

Hi all,

Hope you can help, I am trying to sort data in multiple columns for a specfic range within a sheet, this code almost looks like it wants to work but I keep getting mismatch error. I want to sort only within the range "B4:ET404" and there is no header...can someone please help me with the right coding?? I would appreciate it very much!


Sub MultiSort()
Dim Cols As Range, i As Integer
Set Cols = Selection
For i = Range("B:ET") To Cols.Columns.Count
Cols.Columns(i).Sort Key1:=Cols.Columns(i).Cells(4, 404), _
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Next i
End Sub

 

[DavidB]

Hi there - welcome to the board.

You immediate problem revolves around "Range("B:ET")" not returning a numeric value for the for statement.

Sorry I have not got my head around the rest of your logic but this should get you moving in the right direction.

Regards DavidB

 

[DavidB]

Hi there


The routine sorts each column one at a time and independently of all the others.

Sub MultiSort1()
Dim Cols As Range, i As Integer
Set Cols = Range("B2:ET404")
For i = 0 To Cols.Columns.Count
Cols.Columns(i).Sort Key1:=Cols.Columns(i), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Next i
End Sub


Is this is what you want?

Regards David

 

[Legacy 68668]

change

Cols.Columns(i).Cells(4, 404)

to

Cols.Columns(i).Cells(1)

If you have heading, change to 2

 

[scvba]

david,

that was perfect, thank you so much....
cheers,
--==sandra

Hi there


The routine sorts each column one at a time and independently of all the others.

Sub MultiSort1()
Dim Cols As Range, i As Integer
Set Cols = Range("B2:ET404")
For i = 0 To Cols.Columns.Count
Cols.Columns(i).Sort Key1:=Cols.Columns(i), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Next i
End Sub


Is this is what you want?

Regards David

 

[MeghaJ]

David i wanna use this code. but for me.. rows numbers are not known.. they are dynamic.. then how to define range.? plz help

 

[DavidB]

Hi there MeghaJ

There are a number of ways of doing this:
http://www.mrexcel.com/forum/showthread.php?t=67618
The current region is an area bounded by empty rows/coulmns. This is equivalent to edit/goto/special and selecting current region.

Another way is to use UDF (User Defined Function) code to determine the last row and then use a string concatenation to set the row limit.

Public Function FinalRow()
FinalRow = Range("a65536").End(xlUp).Row
End Function

This code is written for Excel 2003 where the maximum row number is 65536. If you are using Excel 2007 the number is bigger.

Another more risky way is to think of the maximum number of rows that you will ever encounter. If you exceed this limit then only some of the database will be sort.

Regards DavidB

 

[Kim Eichman]

I am using Excel 2007. How would the code below change if I want to sort a range of cells based on the values in a single column in that range.

Hi there


The routine sorts each column one at a time and independently of all the others.

Sub MultiSort1()
Dim Cols As Range, i As Integer
Set Cols = Range("B2:ET404")
For i = 0 To Cols.Columns.Count
Cols.Columns(i).Sort Key1:=Cols.Columns(i), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Next i
End Sub


Is this is what you want?

Regards David

 

[VoG]

I am using Excel 2007. How would the code below change if I want to sort a range of cells based on the values in a single column in that range.

Try like this

Sub MultiSort1()
Range("B2:ET404").Sort Key1:=Range("C2"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

 

[Kim Eichman]

OK, I currently have the following VBA code:
Sub MultiSort1()
Range("Promethee_II!B2:D15").Sort Key1:=Range("Promethee_II!D2:D15"), Order1:=xlDescending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

I am trying to sort a range in the tab called Promethee_II from a tab called User Interface. When I hit a Macro activated button on the User Interface tab, nothing seems to happen.

When I hit the run button in the VBA editor it seems to run (ie. no errors) but nothing gets sorted. Excuse my ignorance but any help you can give will be appreciated.

Thank you.

Try like this

Sub MultiSort1()
Range("B2:ET404").Sort Key1:=Range("C2"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub