Sorting the Area above the Active Cell or Selection.

[omairhe]

Hello Excel Gurus,

I was hoping for a macro that would sort data for me based on the active cell. Currently If the active cell is M16 and I want nothing to do with the active cell but the cell right above it M15 and upwards til a blank
cell is found, in this case it is M6 (non-blank) and M5 (blank cell). I would like to sort alphabatically "Entire Rows" based on Values in column M15 to M6. This is relative as the range could be M20 to M10 at times or
something else but sorting by column M is constant and so is the active cell that will reside in column M and also to do a sort of Entire Rows.

The data in column M are text based if that makes any difference.

Will appreciate a lot.

 

[omairhe]

It turns out I could achieve this by pressing the UP arrow key once and then by keeping the Shift key pressed and selecting the Active Row. Pressing Ctrl and Up arrow key without releasing the Shift Key will select the data I need to sort. Sorting data now will alphabatically sort by the initially selected cell.
But Vba is still a requirment . If someone please could give a hand on this.. Hehe !!

 

[Joe4]

Is this what you are looking for?

    Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 0).End(xlUp)).Select

 

[omairhe]

Is this what you are looking for?

    Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 0).End(xlUp)).Select

Almost. I wanted to select the rows just like the cells were selected from your code. Then hit the sort alphabetically. Also needs to be done via code.

Thanks.

 

[omairhe]

If you are sorting whole rows, that means you have multiple columns.
So which columns are you wanting to sort by?

That will be Column M.

 

[Joe4]

OK, this will sort by whatever the active cell column is:

Sub MySort()

    Dim sRow As Long, eRow As Long
    Dim c As Long
    Dim rng As Range
    
'   Get column for sorting
    c = ActiveCell.Column
    
'   Find start and end row for range
    eRow = ActiveCell.Row - 1
    sRow = ActiveCell.Offset(-1, 0).End(xlUp).Row
    
'   Set rng for sorting
    Set rng = Rows(sRow & ":" & eRow)
    
'   Sort rng by active cell column
    rng.Sort key1:=Cells(sRow, c), order1:=xlAscending, Header:=xlGuess
    
End Sub

If you always want to sort by column M, you can modify it like this:

Sub MySort()

    Dim sRow As Long, eRow As Long
    Dim rng As Range
    
'   Find start and end row for range
    eRow = ActiveCell.Row - 1
    sRow = ActiveCell.Offset(-1, 0).End(xlUp).Row
    
'   Set rng for sorting
    Set rng = Rows(sRow & ":" & eRow)
    
'   Sort rng by column M
    rng.Sort key1:=Range("M" & sRow), order1:=xlAscending, Header:=xlGuess
    
End Sub

 

[omairhe]

OK, this will sort by whatever the active cell column is:

Sub MySort()

    Dim sRow As Long, eRow As Long
    Dim c As Long
    Dim rng As Range
   
'   Get column for sorting
    c = ActiveCell.Column
   
'   Find start and end row for range
    eRow = ActiveCell.Row - 1
    sRow = ActiveCell.Offset(-1, 0).End(xlUp).Row
   
'   Set rng for sorting
    Set rng = Rows(sRow & ":" & eRow)
   
'   Sort rng by active cell column
    rng.Sort key1:=Cells(sRow, c), order1:=xlAscending, Header:=xlGuess
   
End Sub

If you always want to sort by column M, you can modify it like this:

Sub MySort()

    Dim sRow As Long, eRow As Long
    Dim rng As Range
   
'   Find start and end row for range
    eRow = ActiveCell.Row - 1
    sRow = ActiveCell.Offset(-1, 0).End(xlUp).Row
   
'   Set rng for sorting
    Set rng = Rows(sRow & ":" & eRow)
   
'   Sort rng by column M
    rng.Sort key1:=Range("M" & sRow), order1:=xlAscending, Header:=xlGuess
   
End Sub

Thanks so much Joe. What I would do without you.
Cheers.

 

[Joe4]

You are welcome.

Glad I was able to help!