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