Modifying Sort Routine

AlexanderBB

Well-known Member
Joined
Jul 1, 2009
Messages
1,804
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Code:
Sub SortACol(strSheetName As String, strEnd As String, Column as string)

    Dim strColumnRange As String
    Dim rngCurrentCell As Range
    Dim rngNextCell As Range
    strColumnRange = "A1:O" & Trim(strEnd)
    Worksheets(strSheetName).Range(strColumnRange).Sort _
        Key1:=Worksheets(strSheetName).Range(column &"1"), Order1:=xlAscending
    Set rngCurrentCell = Worksheets(strSheetName).Range(strColumnRange)
End Sub

Can I have details on what to change to make that Sort only the Column passed in ?

Do I need all the DIMs and strColumnRange, plus know the last row (strEnd) in advance ?
 
Last edited:

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.

MartyS

Board Regular
Joined
Feb 12, 2018
Messages
197
Code:
Sub SortACol(strSheetName As String, strEnd As String, Column as string)

    Dim strColumnRange As String
    Dim rngCurrentCell As Range
    Dim rngNextCell As Range
    strColumnRange = "A1:O" & Trim(strEnd)
    Worksheets(strSheetName).Range(strColumnRange).Sort _
        Key1:=Worksheets(strSheetName).Range(column &"1"), Order1:=xlAscending
    Set rngCurrentCell = Worksheets(strSheetName).Range(strColumnRange)
End Sub

Can I have details on what to change to make that Sort only the Column passed in ?

Do I need all the DIMs and strColumnRange, plus know the last row (strEnd) in advance ?


ASSUMING YOU MEAN: You only one to sort ONE column

(instead of wanting to sort a range based on one column)


- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Sorting is achieved by this line:

Code:
Worksheets(strSheetName).Range(strColumnRange).Sort Key1:=Worksheets(strSheetName).Range(column &"1"), Order1:=xlAscending

I don't know what you pass into the routine for the Column variable: Sub SortACol(strSheetName As String, strEnd As String, Column as string)


Is it a string that is a single column reference? e.g. "A" or "F" or "AX", etc

Is it a string that is a single column as a range? e.g. "A:A" or "B:B" etc


If it's a single column as a range (e.g. "A:A") then all that's needed is:

Calling routine
Code:
Sub test()


    SortACol "Sheet1", "E:E"


End Sub


Sort routine
Code:
Sub SortACol(strSheetName As String, SortColumn As String)    

    Sheets(strSheetName).Range(SortColumn).Sort Key1:=Range(SortColumn), Order1:=xlAscending, Header:=xlNo

End Sub



N.B. If your column has a header, you'll need to change Header:=xlNo to Header:=xlYes
 
Last edited:
Upvote 0

AlexanderBB

Well-known Member
Joined
Jul 1, 2009
Messages
1,804
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Many thanks Marty, I often wondered if that long-winded routine was all needed. When you first see it, it looks really complicated!
Usually I'd send in the column letter then make it a Range (without fully realising it.)
Your routine has much improved things. I added a third parameter for the Header.
Also tried sending in "A1:E3" instead of ""E:E" and that worked too. Although reversing that would mean sorting e.g. A1:E3 by E
which I see now that's what you mean by 'range based on one column'. Could you give an example for that please?
 
Upvote 0

MartyS

Board Regular
Joined
Feb 12, 2018
Messages
197
Many thanks Marty, I often wondered if that long-winded routine was all needed. When you first see it, it looks really complicated!
Usually I'd send in the column letter then make it a Range (without fully realising it.)
Your routine has much improved things. I added a third parameter for the Header.
Also tried sending in "A1:E3" instead of ""E:E" and that worked too. Although reversing that would mean sorting e.g. A1:E3 by E
which I see now that's what you mean by 'range based on one column'. Could you give an example for that please?


Hi,

Glad it helped! :)


By "Sorting one column" it means there is only one column selected: one column is selected and that is the only column sorted, regardless of any other data next to it / associated with it.




By "Sorting a range based on one column" I mean:


Column A, Column B, Column C

are all selected... and are sorted using one of the Columns as the KEY



e.g. with this data:


Name Age Location

Smith 35 New Jersey
Xu 66 New York
Jones 42 Hampshire



Selecting the entire range and then sorting on Column A as the key alters all the data (sorting a range based on a column)


Name Age Location

Jones 42 Hampshire
Smith 35 New Jersey
Xu 66 New York



whereas having a sheet with the same data in it, but only sorting one column (say column A) means the data in column A will be sorted independent of the rest of the columns, and is now mismatched.


Name Age Location

Jones 35 New Jersey
Smith 66 New York
Xu 42 Hampshire
 
Upvote 0

AlexanderBB

Well-known Member
Joined
Jul 1, 2009
Messages
1,804
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
> and are sorted using one of the Columns as the KEY

I'm not sure I understand "key" yet

Code:
    Sheets(strSheetName).Range(SortColumn).Sort Key1:=Range(SortColumn), Order1:=xlAscending, Header:=xlNo

Key1 is 1 as it's the first, nothing to so with Column 1 ?

Does this part Select the range
Sheets(strSheetName).Range(SortColumn)

and this part the columns to sort
.Sort Key1:=Range(SortColumn),

I noticed run time Error 1004 with Sub SortACol unless its the selected (active?) sheet. Although I'm not sure why, especially as the sheet name is passed in.
 
Upvote 0

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,782
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
[/I]I noticed run time Error 1004 with Sub SortACol unless its the selected (active?) sheet. Although I'm not sure why, especially as the sheet name is passed in.

Do you get the error with the code below?
Code:
Sub SortACol(strSheetName As String, SortColumn As String)    

    Sheets(strSheetName).Range(SortColumn).Sort Key1:=Sheets(strSheetName).Range(SortColumn), Order1:=xlAscending, Header:=xlNo

End Sub
 
Upvote 0

AlexanderBB

Well-known Member
Joined
Jul 1, 2009
Messages
1,804
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Yes. Full message is

Run-time error '1004':

The sort reference is not valid. Make sure that it's within the data you
want to sort, and the first Sort By box isn't the same or blank.


The parameters passed in are the sheet name and "K:K".
If Worksheets(SheetName).Select is before the call to SortACol, it's OK.
 
Upvote 0

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,782
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
OK, I must admit that I am a bit surprised at that with the code I posted and so I have no further input at this time.
 
Upvote 0

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,782
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
I have Just tested and the code I posted is working fine for me as long as the parameters are being passed correctly i.e. if I run Sub dddd below.

Code:
Sub SortACol(strSheetName As String, SortColumn As String)

    Sheets(strSheetName).Range(SortColumn).Sort Key1:=Sheets(strSheetName).Range(SortColumn), Order1:=xlAscending, Header:=xlNo

End Sub

Code:
Sub dddd()
Call SortACol("Sheet3", "D:D")
End Sub
 
Upvote 0

AlexanderBB

Well-known Member
Joined
Jul 1, 2009
Messages
1,804
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Ah, I didn't notice subSortA in Msg6 had changed. Sorry, it is working now.
 
Upvote 0

Forum statistics

Threads
1,186,732
Messages
5,959,432
Members
438,424
Latest member
TheHank

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