record a custom sort macro

[sparcle]

Hi, all

I want to record a macro with custom sort with different level.When i select some rows then it sort according to custom sort which is store in macro.


please help me.


Thanks in advance.

 

[Luke M]

The macro should record the custom list that you use. Is it not doing that on your machine?

 

[sparcle]

i record macro but it sort only those cells which selected during record macro what to do now

 

[SirJB7]

Hi, sparcle!

Fortunately recorded macros operates exactly as they were recorded... so, either record it again, or upload the workbook with detailed instructions of what do you want to achieve.

Regards!

 

[sparcle]

http://www.2shared.com/file/F1kd1Ccn/New_Microsoft_Office_Excel_Wor.html


here is my work book.

please go to custom sort to see what i want.

 

[SirJB7]

Hi, sparcle!


First of all, the uploaded workbook is a .xlsx file so it can't have macros... so where is the recorded macro?

Despite of this, why not trying to delete the first 3 rows and then select whole worksheet or just columns D:H? That would do the job.


Regards!

 

[sparcle]

here below is macro code.

[pre]

Sub SORT()
'
' SORT Macro
'

'
ActiveWorkbook.Worksheets("Sheet1").SORT.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").SORT.SortFields.Add(Range("F5:F8"), _
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 0 _
, 0)
ActiveWorkbook.Worksheets("Sheet1").SORT.SortFields.Add Key:=Range("E5:E8"), _
SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="TSL", _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").SORT.SortFields.Add Key:=Range("H5:H8"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").SORT
.SetRange Range("C4:H8")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Save
Application.WindowState = xlMinimized
Application.WindowState = xlMinimized
Application.WindowState = xlNormal
End Sub

[/pre]

 

[sparcle]

hi, SirJB7


please select D5 cell and go to custom sort, what is there i want to do with macro when select some rows i.e. every time i select rows & run macro it will produce result what is produced by custom sort.

 

[sparcle]

help me for sorting as i can't find any solution.

 

[Luke M]

Assuming the columns you sorted are constant, here's a more generic version of your macro.

[pre]

Sub SortMacro()

Dim xRange As Range
Dim StartRow As Integer
Dim EndRow As Integer

Set xRange = Selection
StartRow = xRange.Cells(1, 1).Row
EndRow = xRange.Rows + StartRow - 1

ActiveWorkbook.Worksheets("Sheet1").SORT.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").SORT.SortFields.Add(Range(Cells(StartRow, "F"), Cells(EndRow, "F")), _
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 0 _
, 0)
ActiveWorkbook.Worksheets("Sheet1").SORT.SortFields.Add Key:=Range(Cells(StartRow, "E"), Cells(EndRow, "E")), _
SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="TSL", _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").SORT.SortFields.Add Key:=Range(Cells(StartRow, "H"), Cells(EndRow, "H")), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").SORT
.SetRange Range(Cells(StartRow, "C"), Cells(EndRow, "H"))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Save

End Sub

[/pre]

 

[SirJB7]

Hi, sparcle!


Try doing:


1) Insert these lines after the comment lines and before first "ActiveWorkbook...":

-----

[pre]

Dim rng As Range
Set rng = Application.Intersect(Selection, Range("C2:H1048576")).EntireRow
If rng Is Nothing Then Exit Sub[/pre]
-----


2) Replace this line:

----

[code].SetRange Range("C4:H8")

----

by this:

-----

[code].SetRange rng

-----


3) Replace this line:

----

.Header = xlYes[/code]

----

by this:

-----

.Header = xlNo[/code]

-----


4) No need to say that you must save your workbook as .xlm file... do I?

And remove the first 3 lines or change accordingly the above range reference.


Regards!


PS: BTW, give a look at this:

http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting

Specially the 12th, 13th and 16th paragraphs.

 

[sparcle]

Thanks Luke M & SirJB7 for your help.


SirJB7, i will definitely keep those in mind.

thanks again, if need any help ask you again.

 

[SirJB7]

Hi, sparcle!

Glad you solved it. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!

 

[SirJB7]

@Luke M

Hi!

Looking over my shoulder again...?

Regards!

 

[Luke M]

@SirJB7

You caught me!

 

[sparcle]

SirJB7, it works fine but has one problem.


ActiveWorkbook.Worksheets("Sheet1").SORT.SortFields.Add Key:=Range("E5:E8"), _

SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="TSL", _

DataOption:=xlSortNormal


when it sort using 2nd condition, it sort first TSL then alphabetical order but i want to sort only TSL & other remain same & skip to 3rd condition for sorting.

 

[SirJB7]

Hi, sparcle!


That's a different issue and the recorded macro doesn't do such a thing. Recorded macro sorts firstly by F column, then by E column and lastly by H column; there's no skipping condition, all three columns are always used and in that order.


If you want to exclude or include any criteria from those three, you should add a:

IF <Condition> THEN

structure to the beginning of each "ActiveWorkbook.Worksheets("Sheet1").SORT.SortFields.Add Key..."

statement, accordingly to your desired conditions.


BTW, I omitted telling you to remove the row references from the "F5:F8" (and E and H), leaving them as "F:F" and so on.


Regards!