VBA: Sorting data with dynamic heading location.

[Magoosball]

I have a list of headers in row 13. I am trying to find the header titled "Revenue" and sort in descending order.

lCol = WorksheetFunction.Match("Revenue", Rows("13:13"), 0)
Rows("13").Sort key1:=lCol, Header:=xlYes

What am I doing wrong?

Thank you in advance

 

[JLGWhiz]

Maybe use the Find method of VBA

Dim fn As Range
Set fn = Rows(13).Find("Revenue", , xlValues)
    If Not fn Is Nothing Then
        fn.EntireColumn.Sort fn, xlAscending
    End If

It was assumed that you wanted to sort the column, not the row.

 

[jim may]

Substitute the below Macro for what you WERE using...

Sub Foo()
Range("A13").Select
lCol = WorksheetFunction.Match("Revenue", Rows("13:13"), 0)
With ActiveCell.CurrentRegion
    .Sort Key1:=Cells(13, lCol), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
     MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End With
End Sub

 

[Magoosball]

Substitute the below Macro for what you WERE using...

Sub Foo()
Range("A13").Select
lCol = WorksheetFunction.Match("Revenue", Rows("13:13"), 0)
With ActiveCell.CurrentRegion
    .Sort Key1:=Cells(13, lCol), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
     MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End With
End Sub

This is close!
1) The cells should be sorted starting on row 14. This macro is sorting them and the lowest value is starting on row 9 and the cell that contained "Revenue" on row 13 is now erased with data.
2) The Revenue column is sorting but some of the other columns to the left and right aren't sorting with it.

 

[Magoosball]

Maybe it would be easier to select the entire column and apply a filter?

 

[jim may]

Obviously you must have non blank data in all rows above Row 13 (meaning all rows between 9 and 13)... Had I known that I would not have used the CurrentRegion property -- which caused the reported problem.
Best work-a-round would be prior to the running of the revised micro below -- SELECT (Or Highlight) the Range from A13 across and down to your last record involved in the Sort.
Then run the Macro -- Save your File BEFORE Doing this in case there ends up being still another unforeseen problem.
Jim

Sub Foo()
lCol = WorksheetFunction.Match("Revenue", Rows("13:13"), 0)
With Selection
    .Sort Key1:=Cells(13, lCol), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
     MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End With
End Sub

 

[Magoosball]

Obviously you must have non blank data in all rows above Row 13 (meaning all rows between 9 and 13)... Had I known that I would not have used the CurrentRegion property -- which caused the reported problem.
Best work-a-round would be prior to the running of the revised micro below -- SELECT (Or Highlight) the Range from A13 across and down to your last record involved in the Sort.
Then run the Macro -- Save your File BEFORE Doing this in case there ends up being still another unforeseen problem.
Jim

Sub Foo()
lCol = WorksheetFunction.Match("Revenue", Rows("13:13"), 0)
With Selection
    .Sort Key1:=Cells(13, lCol), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
     MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End With
End Sub

Worked like a charm. Thank you so much! Sorry I should have clarified that in my original post.