Sort in Excel with Multiple rows and column

[redchilli]

I have been using this code to sort with multiple rows and columns is any other short code possible.

Please help.


Sub Sort0()
Dim i As Long
Dim c As Long
Dim u As Long
Dim Frow As String
Dim Lrow As String
Dim sColumn As Variant
Dim fColumn As Variant
Dim StrMyValue As Variant
Dim NewStr As Variant
Dim str2 As String
StrMyValue = Selection.Address
c = 0
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
fColumn = Split(Range(StrMyValue).Offset(0, c).Address, ":")
If UBound(fColumn) > 0 Then

For u = 1 To Len(fColumn(0))
If Mid(fColumn(0), u, 1) <> "$" And IsNumeric(Mid(fColumn(0), u, 1)) Then
Frow = Frow & Mid(fColumn(0), u, 1)
End If
Next
For u = 1 To Len(fColumn(1))
If Mid(fColumn(1), u, 1) <> "$" And IsNumeric(Mid(fColumn(1), u, 1)) Then
Lrow = Lrow & Mid(fColumn(1), u, 1)
End If
Next


If Frow <> Lrow Then


For i = 1 To Range(StrMyValue).Columns.Count

sColumn = Range(fColumn(1)).Offset(0, c).Address
c = c - 1
For u = 1 To Len(sColumn)
If Mid(sColumn, u, 1) <> "$" And Not IsNumeric(Mid(sColumn, u, 1)) Then
str2 = str2 + Mid(sColumn, u, 1)
End If
Next


NewStr = str2 & Frow & ":" & str2 & Lrow
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(NewStr) _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

str2 = ""
Str1 = ""

Next
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range(StrMyValue)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End If

End If

End Sub

[snb]

Did you ever use the macrorecorder ?

[redchilli]

Yes,

But the selection of range would change often for me.

[snb]

A lot of your code has nothing to do with sorting.

how to sort a variable range on the 3rd column::

sub M_snb()
sheets(1).cells(1).currentregion.sort sheets(1).cells(1,3)
end sub

or


sub M_snb()
with sheets(1).cells(30,3).currentregion
.sort .cells(1,3)
end with
end sub

[redchilli]

I agree with you, i am new to it, Thanks for your Post i got some idea on it.

with your code it sort the particular column and when i code it with loop for two columns , the results is, only the second sort order remains. i hope it is clear for you.

I have attached the required result with file.

[snb]

Yes it is. You can find the anwer in the VBEditor's helpfiles, lemma 'sort method'.

[redchilli]

I have modified the code to make it little smaller, Now is this shorter code or is possible more shorter way to do it.

Help to improve.

Sub SortMeth2()
'
' Sorting Macro
' sorting with Multiple Row and Column excluding first two column
'
'
Dim x, y, a, b As Variant
Dim RefCell As Variant
Dim StrMyValue As Variant
x = Selection.Rows.Count
y = Selection.Columns.Count
a = ActiveCell.Row
b = ActiveCell.Column
StrMyValue = Selection.Address
ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
For i = y To b Step -1
RefCell = Range(Cells(a, ((b + i) - 1)), Cells(((x + a) - 1), ((b + i) - 1))).Address
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range(RefCell) _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
Next
With ActiveWorkbook.ActiveSheet.Sort
.SetRange Range(StrMyValue)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

[snb]

See:

http://vbaexpress.com/forum/showpost.php?p=280873&postcount=4

[redchilli]

Sorry Sub, i could not get your logic. as it sorts one particular column, But i want to sort first column "I" then followed by "H" , "G" , "F" , "E" , "D".

[redchilli]

Sorry Snb, i could not get your logic. as it sorts one particular column, But i want to sort first column "I" then followed by "H" , "G" , "F" , "E" , "D"

[snb]

Please read the lemma 'sort method' in the VBEditor's helpfiles.

You can add more sorting keys to the sort method.