Sort by row based on column value

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

Code:

---

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

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?

Quote:

---

Originally Posted by 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?

---

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

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!

I thought I would try a different approach

Code:

---

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

I don't understand what you want to do.

Code:

---

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

---

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

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?

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

Code:

---

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

---