Sort by row based on column value

[strato]

Hello
I'm using Excel 2010 and I would like to sort by row based on values in a column.
So far I have



Sub sortRow()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim ViewMode As Long
Dim i As Long

With ActiveSheet
.Select
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
.DisplayPageBreaks = False

Firstrow = .UsedRange.Cells(1).Row + 1
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

'We loop from Lastrow to Firstrow (bottom to top)
For Lrow = Firstrow To Lastrow

With sht.Sort
.SortFields.Clear
.SortFields.Add Key:=rng, SortOn:=xlSortOnValues, _
Order:=xlDescending, DataOption:=xlSortNormal
.SetRange rng
.Header = xlNo
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
Next Lrow
End With
End Sub


I believe the problem is at the .SetRange rng not sure what to put if I want column B for example
I tried .setrange :=(B:B) but it errors
I should mention I wish to order the row in descending order based on the column word

[D_Marcel]

Do you want to sort all the rows of your worksheet based on a column or some columns?

For example:



COUNTRY_NAME
TOTAL_SALES


Germany
1.000.000,00


United States
998.000,00


Brazil
765.987,00


Sweden
2.000.000,00


Italy
5.000.000,00












Then sort the countries with the highest sales, according to column B?

[strato]

Do you want to sort all the rows of your worksheet based on a column or some columns?

For example:



COUNTRY_NAME
TOTAL_SALES


Germany
1.000.000,00


United States
998.000,00


Brazil
765.987,00


Sweden
2.000.000,00


Italy
5.000.000,00












Then sort the countries with the highest sales, according to column B?

[mdmackillop]

@ Douglas
Note that the sort is .Orientation = xlLeftToRight in his code

[strato]

Hello
Yes, that is close I want to group rows by value that is either Overdue, AmostDue or Due from a column.
I thought descending by alphabetically would work since I lucked out that descending is the order I want them to be in.

I changed the .Orientation = xlTopToBottom

Thanks for the input!

[strato]

I thought I would try a different approach


Sub Sort3Layer()


With ActiveSheet
.Select
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
.DisplayPageBreaks = False

Firstrow = .UsedRange.Cells(1).Row + 1
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

'We loop from Lastrow to Firstrow (bottom to top)
For Lrow = Firstrow To Lastrow
With .Cells(Lrow, "B")
If Not IsError(.Value) Then
With ActiveSheet
.Sort.SortFields.Add Key:=Range( _
"B:B"), SortOn:=xlSortOnValues, Order:=xlDescending, _
CustomOrder:="OVERDUE", _
DataOption:=xlSortNormal
.Sort.Apply ' error here
End With
End If
End With
Next Lrow
End With
End Sub

It tells me the "Sort reference is not valid make sure it's within the data you want to sort and the first
sort by box isn't the same or blank"

I want to eventually sort by 3 values Overdue, Due and Almost_Due

Any help is appreciated

[mana]

I don't understand what you want to do.


Option Explicit

Sub test()

With ActiveSheet.Sort
With .SortFields
.Clear
.Add Key:=Range("B1"), Order:=xlDescending
.Add Key:=Range("C1"), Order:=xlDescending
End With
.SetRange Range("A1").CurrentRegion
.Header = xlYes
.Apply
End With

End Sub

[mdmackillop]

Hi Strato
Don't you think you should just post some sample data with a clear explanation as to expected result? Without that, I've no inclination to attempt a solution
Regards
MD

[strato]

Hello
My apologies for the vague explanation as to what I'm trying to do.
I'm wanting to sort rows by descending order by a value in column B.
The value is either Overdue, Due or Almost_Due.
I guess it's pretty obvious I have no idea how to do this. I was trying to pull code off
the net that I thought was what I needed.

Do I need a loop to go though all the rows or is it enough to just point to the column
to sort on?

[strato]

Thank you mana your code worked!
I appreciate the patience you guys had with me!


Option Explicit

Sub test()

With ActiveSheet.Sort
With .SortFields
.Clear
.Add Key:=Range("B1"), Order:=xlDescending
End With
.SetRange Range("B1").CurrentRegion
.Header = xlYes
.Apply
End With

End Sub