Modifying Sort Routine

[AlexanderBB]

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 ?

 

[MartyS]

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:

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

Sub test()


    SortACol "Sheet1", "E:E"


End Sub

Sort routine

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

 

[AlexanderBB]

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?

 

[MartyS]

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

 

[AlexanderBB]

> and are sorted using one of the Columns as the KEY

I'm not sure I understand "key" yet

    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.

 

[MARK858]

[/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?

Sub SortACol(strSheetName As String, SortColumn As String)    

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

End Sub

 

[AlexanderBB]

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.

 

[MARK858]

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.

 

[MARK858]

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.

Sub SortACol(strSheetName As String, SortColumn As String)

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

End Sub

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

 

[AlexanderBB]

Ah, I didn't notice subSortA in Msg6 had changed. Sorry, it is working now.