PDA

View Full Version : flips left-to-right sequence of selected columns



alienscript
07-07-2008, 11:24 AM
Hi,

I know this must be some ready answers on the web but I had been searching the whole evening without success for the right code. Hope that someone could please help.

Let's say, I select a contiguous entire range of columns (example, from H to S, 12 columns representing 12 months of data) and I want to flip all the selected columns from left to right. If it is from Jan, Feb... to Dec, I want it to re-arrange to the sequence Dec,Nov,Oct... to Jan.

Thanks a lot.

Simon Lloyd
07-07-2008, 11:33 AM
I don't know about 2003 or earlier but in 2007 you can highlight data choose DATA, SORT, OPTIONS choose SORT LEFT TO RIGHT, then in the ORDER dropdown choose custom and create a new custom list then sort!

alienscript
07-07-2008, 11:56 AM
Thanks Simon. I will read the cross-post message very carefully and make sure I do it right always.

marshybid
07-08-2008, 03:15 AM
Hi Alienscript,

In order for this to work correctly the values for the month in each cell need to be input as - 01/2008, 02/2008, etc then format cells as custom - mmm

By recording a macro the code below shows how to sort the columns in the opposite order;


Sub opporder ()
Columns("A:L").Select '//change range to suit your requirements
Selection.Sort Key1:=range("A1"), Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal
range("A1").Select

End Sub


Marshybid :hi:

Bob Phillips
07-08-2008, 04:32 AM
ActiveSheet.DisplayRightToLeft = True

marshybid
07-08-2008, 04:38 AM
ActiveSheet.DisplayRightToLeft = True

Hi xld,

That's a clever way of doing it :bow:

I guess this would just flip every column in the active sheet? This way you would not need to worry about the cell values or formatting?? is that right? You always make it look way too easy for beginners like me :rotlaugh:

I opted for the record a macro method to teach myself how it could be done :whistle:

Marshybid

Bob Phillips
07-08-2008, 06:19 AM
It was just a bit of fun really, I am sure it affecst the spreadsheet in ways the OP doesn't want, but interesting anyway.

mikerickson
07-08-2008, 07:04 AM
This works on any Selection that has any (non-duplicates) text in the top row
Sub Macro1()
Dim customListNum As Long
With Selection
Application.AddCustomList ListArray:=Application.Index(.Cells, 1, 0)
customListNum = Application.GetCustomListNum(Application.Index(.Cells, 1, 0).Value)

.Sort Key1:=.Range("a1"), Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=customListNum + 1, MatchCase:=False, Orientation:=xlLeftToRight
Application.DeleteCustomList ListNum:=customListNum
End With
End Sub

mdmackillop
07-08-2008, 09:20 AM
You can always add a numbered helper row and sort using that.