Sorting the Area above the Active Cell or Selection.

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,012
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,012
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,512
Office Version
  1. 365
Platform
  1. Windows
Is this what you are looking for?
VBA Code:
    Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 0).End(xlUp)).Select
 

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,012
Office Version
  1. 2019
Platform
  1. Windows
Is this what you are looking for?
VBA Code:
    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

Well-known Member
Joined
Mar 26, 2009
Messages
2,012
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,512
Office Version
  1. 365
Platform
  1. Windows
OK, this will sort by whatever the active cell column is:
VBA Code:
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:
VBA Code:
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
 
Solution

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,012
Office Version
  1. 2019
Platform
  1. Windows
OK, this will sort by whatever the active cell column is:
VBA Code:
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:
VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,512
Office Version
  1. 365
Platform
  1. Windows
You are welcome.

Glad I was able to help!
:)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,107
Messages
5,622,780
Members
415,927
Latest member
vedasinternational

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top