Columns in ABC / 123 Order

Topic

New Reply

Hello, this topic may have been covered before, but I can’t seem to find it.

Please see attached example image, and then I’ll tell you what I’m trying to accomplish.

I want to set up my spreed sheet so that if I click on the column title (“Color” or “Number” from my example) the data below would be rearranged in Alpha Numeric order.
Thanks for your help.

Reply | Quote

Replies

As a starter, how about something like this…

This uses seperate IF statements in the Workbook_SheetBeforeDoubleClick event that then calls different sub routines for the sort processes.

I’m sure that other more experienced loungers will offer more elegant approaches.

I have this set up for auto sorting 15 columns of data, (15 times over). I’d like to think this could be combined into one more efficient command, maybe… ???

Reply | Quote

As a starter, how about something like this…

This uses seperate IF statements in the Workbook_SheetBeforeDoubleClick event that then calls different sub routines for the sort processes.

I’m sure that other more experienced loungers will offer more elegant approaches.

I have this set up for auto sorting 15 columns of data, (15 times over). I’d like to think this could be combined into one more efficient command, maybe… ???

I’m sorry I can’t seem to get your spreed sheet to work, but thanks for your help anyway.

Reply | Quote

I’m sorry I can’t seem to get your spreed sheet to work, but thanks for your help anyway.

You will need to have macro’s enabled. It works by double clicking either of the header cells.

Reply | Quote

I’m sorry I can’t seem to get your spreed sheet to work, but thanks for your help anyway.

I tried it and its works.

Did you enable the macro when you first open the file and double click on the cell Color or Number

Reply | Quote

I tried it and its works.

Did you enable the macro when you first open the file and double click on the cell Color or Number

Sorry about that, I tried it again and It works great! Thank you very much.

Reply | Quote

I have a follow up on this topic.

Now I would like to sort the columns, but I want the rows to stay locked together.
I know this can be done I just don’t know how.

Hope you understand me, and thanks again.

Reply | Quote

I have a follow up on this topic.

Now I would like to sort the columns, but I want the rows to stay locked together.
I know this can be done I just don’t know how.

Hope you understand me, and thanks again.

To better explain myself please refer to my original example.

I want to be able to sort the rows by clicking on the specific field in the column heading.

So according to my previous example the row fields should always be intact (Red 24, Green 32, Blue 123, and so on)

I want to be able to click on “Color” and have “Black 24” be at the top of the list. (Not “Black 24”)
In other words I want to sort the rows by column headings. All data in a given row should always be in the same row, only the row will move up or down.

Reply | Quote

Sorry Andy, I’m not sure that I understand your question. Regardless of which sort is processed, the entire range is sorted accordingly.

Reply | Quote

Maybe I am missing something here, but as far as i can see, the workbook that I posted fulfills those requirements.

<<I want to be able to click on "Color" and have "Black 24" be at the top of the list. (Not "Black 24")

Black 24 is at the top when sorted by column A.

Reply | Quote

Maybe I am missing something here, but as far as i can see, the workbook that I posted fulfills those requirements.

<<I want to be able to click on "Color" and have "Black 24" be at the top of the list. (Not "Black 24")

Black 24 is at the top when sorted by column A.

My mistake, sorry I am dealing with my horrible allergies.

Let me try explaining again:

I want each row to be able to move up or down, but I dont want the data in the rows getting scrambled.

For example:
Please look at example "one". Now example "two" shows what happens when I click on "Numbers". Example "three" is what I want to happen when I click on "Numbers".

Thanks again for all your help.

Reply | Quote

OK. Using the workbook that I posted earlier, I input your data (example One), exactly as you provided. When I double click the word Number in cell B1, I get a rearanged list of A-H in column A and 1-8 in column B, as per your example three.

I can’t see how you are getting the result in example two. You may need to post a stripped down version of your own workbook that provides this result.

The code in the workbook that I posted sorts the entire rows of 2 to the last row with data in column A. If D and 4 are on the same row prior to sorting, they will remain on the same row after the sort.

Reply | Quote

An after thought:

Are you by any chance using absolute formula’s in either columns A or B?

Reply | Quote

An after thought:

Are you by any chance using absolute formula’s in either columns A or B?

Ok, I got it to work. I am trying to add more columns to the equation using your example, but I am having trouble.
See attached file. When I double click on column “C” nothing happens.
Please check my macro and tell me what I did wrong.

Thanks again.

Reply | Quote

Hi Andy,

Code:

Sub SortC()

'This is called by double clicking "C1" - see Private Sub Workbook_SheetBeforeDoubleClick

.........

End Sub

Your code is correct in the ‘Module_sort’, however you need to also create the caller in the ‘This Workbook’ object.

Post back if you are still unsure.

As I said previously, I’m sure that there is a much more elegant approach to this. I’m hoping others will chip in with better suggestions.

Reply | Quote

Try this version. Right click on the sheet where you want to run the macro and choose View Code. Paste the macro there.

Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column > 3 And Target.Row > 1 Then

Exit Sub
Else
Cancel = True
		Selection.CurrentRegion.sort Key1:=Selection, Order1:=xlAscending, Header:= _
		xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
		DataOption1:=xlSortNormal
End If

End Sub

If you want to use it for the entire workbook, put the code into the Workbook module and change the first line to:

Code:

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

If you have more columns the you want to be able to sort on, change the 3 in the Target.Column > 3 to the number of columns you want to use.

Reply | Quote

The first line of the code should be
If Target.Column > 3 Or Target.Row > 1 Then

not
If Target.Column > 3 And Target.Row > 1 Then

Sorry for any confusion.

Reply | Quote

Thanks, this is brill.

Would it be possible to adapt this to a toggle? Where double clicking the header cell sorts ascending`and or descending?

Reply | Quote

Paste the following to a sheet module. It could be used in the workbook module, but since it uses global variables you could run into unexpected results. The default sort is always ascending. For example, if column A is sorted ascending B’s and C’s next sort order will be ascending regardless of theirlast sort order. The toggle will flip between ascending and descending only if the same column is resorted. I could try to adjust if you would like.

The macro must be placed immediately below Option Explicit (you do use Option Explicit right? ), or at the very top of the module.

[codebox]Dim s1 As Boolean, s2 As Boolean, s3 As Boolean

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column > 3 Or Target.Row > 1 Then

Exit Sub
Else
Cancel = True
Dim sType As String
If Target.Column = 1 Then
s2 = False
s3 = False
s1 = Not (s1)
If s1 = True Then
sType = xlAscending
Else
sType = xlDescending
End If
End If
If Target.Column = 2 Then
s1 = False
s3 = False
s2 = Not (s2)
If s2 = True Then
sType = xlAscending
Else
sType = xlDescending
End If
End If
If Target.Column = 3 Then
s1 = False
s2 = False
s3 = Not (s3)
If s3 = True Then
sType = xlAscending
Else
sType = xlDescending
End If
End If

Selection.CurrentRegion.Sort Key1:=Selection, Order1:=sType, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End If

End Sub[/codebox]

Reply | Quote

You could simplify your earlier code to use just two variables, one to store the column number that you last sorted on and one to store the direction.

[codebox]
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Static sType As Integer, iCol As Integer

If Target.Column > 3 Or Target.Row > 1 Then
Exit Sub
Else
Cancel = True
If (Target.Column = iCol) And (sType = xlAscending) Then
sType = xlDescending
Else
sType = xlAscending
iCol = Target.Column
End If

Selection.CurrentRegion.Sort Key1:=Selection, Order1:=sType, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End If

End Sub
[/codebox]

Reply | Quote

Thankyou very much!

This works brilliantly!!

I love the simplicity of the code that you kindly provided in post #16, purely for the fact that this can be used on many many columns and accross different worksheets with very minimal code.

I guess I need to weigh up the need for the ascending / descending facility, due to the amount of code that would be required for many columns (based on your latest code).

Thinking…

What about the possibility of using the simplicity of your original code, because of the ease of use accross many columns, but to capture the sType by way of pop-up selector (kind of userform?) with a default of ascending, but the ability to switch to descending.

I’m learning all the time, and appreciate your input and support!

Something along the lines of…….. ??

Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Target.Column > 78 Or Target.Row > 1 Then
	Exit Sub
End If

	Cancel = True
	Dim sType As String
	
	'This does not work for me, but could it work with a pop-up Asc/Desc selector userform?
	sType = InputBox(Prompt:="Please select Sort Type", Title:="Auto Sort")
	
	Selection.CurrentRegion.Sort Key1:=Selection, Order1:=sType, Header:= _
	xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

End Sub

Reply | Quote

Still thinking……

Failing that, what about >

Using the code from post #16 to double click the header cell to sort ascending, and then do something else ( ??? ) to the header cell to sort descending.

BTW, this is not majorly important, I’m just looking to save a few clicks and reduce repetitiveness!

Reply | Quote

How about this:

Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column > 3 Or Target.Row > 1 Then

Exit Sub
Else
Cancel = True
Dim sType As String, sInput As String
retry:
sInput = Application.InputBox("Choose sort order" & Chr(13) & _
			"A for ascending or D for descending", "Sort Order", "A")

If UCase(sInput)  "A" And UCase(sInput)  "D" Then
MsgBox "Please enter either A or D"
GoTo retry
End If

If UCase(sInput) = "D" Then
sType = xlDescending
Else
sType = xlAscending
End If
		Selection.CurrentRegion.Sort Key1:=Selection, Order1:=sType, Header:= _
		xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
		DataOption1:=xlSortNormal
End If

End Sub

Reply | Quote

Gentlemen, Thankyou very much!! Both fully functional solutions

A few small questions re Stuart’s code, so that I have complete understanding…..

Am I correct in thinking:

The static variable is saved in memory once the execution is complete, which is why if you repeatedly execute on one column, the last sType is remembered, so the sort will always change, where as switching to a new column, the sType is unknown, so will always start with ascending?

Why is sType declared as an Integer?

Cancel = True < What is the purpose of this command?

Thanks again to you both.

Reply | Quote

The static variable is saved in memory once the execution is complete, which is why if you repeatedly execute on one column, the last sType is remembered, so the sort will always change, where as switching to a new column, the sType is unknown, so will always start with ascending? Yes

Why is sType declared as an Integer? Not quite sure. I did notice that I had unintentionally left off the quotes around xlDescending and xlAscending while I had declared sType as a string, the macro worked correctly. If I added the quotes, the macro bombs out.

Cancel = True < What is the purpose of this command? The normal action of a double click on a cell is to edit the cell The Cancel = True blocks this action and allows the columns to be sorted.

Reply | Quote

Why is sType declared as an Integer?

xlAscending and xlDescending are constants available within Excel. You can look for their values in the Object browser, or on the Microsoft web site.

xlAscending has a value of 1, and xlDescending has a value of 2. You could use 1 and 2 instead of the symbols if you want.

Reply | Quote

The static variable is saved in memory once the execution is complete, which is why if you repeatedly execute on one column, the last sType is remembered, so the sort will always change, where as switching to a new column, the sType is unknown, so will always start with ascending?

sType is simply set to one of two values, xlAscending or xlDescending. It has no information about columns.

My code remembers the number of the last column clicked (in static variable iCol). If you click on the same column as last time then it checks sType, if you click on a different column then it always does an ascending sort.

Reply | Quote

Thankyou for your comments, I now have a better understanding. This will come in very handy!!

Reply | Quote

Would you be able to use Auto Filter instead of the macro? The Auto Filter has options to Sort Ascending and Sort Descending.
Just a thought.

Reply | Quote