Reverse column order with a macro

[misterhanky]

For my job, I frequently need to reverse the order of columns in Excel.
I'll get a feed of financial data that goes 2006 2005 2004 2003 (etc),
and I need to make it go 2003 2004 2005 2006. The good news is that I
know how to do this by sorting. The bad news is that I spend 5% of my
work week doing it. I know it's just a few keystrokes, but it's a
hassle, and they add up over time. I should also note that the data
format is sometimes unusual, so I almost always have to create a helper
row. However, the data always comes to me in exactly the reverse of the
order that I want it. In other words, I do not need to "sort," as much
as I need to "reverse."

I'd like to be able to simply highlight the rows and use a shortcut.
Does there exist a routine to do this for me? If not, does there exist
a similar routine that could easily be modified?


Thank you.

 

[Dave Peterson]

Next time you have to do it manually, try recording a macro when you do it.

Then you should be able to just rerun that macro when you want to do it again
and again and again and...

 

[misterhanky]

Thanks for the suggestion. I don't think that will work because the
number of columns affected changes every time, and I can't have a bunch
of blank spacebetween the descriptions (Column A) and the data.

 

[Dave Peterson]

If you know your data enough, maybe you can use it to determine the range to
sort.

I used the data in column A to get the last row and the data in Row 1 to get the
last column:

Option Explicit
Sub testme()

Dim LastRow As Long
Dim LastCol As Long

With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

With .Range("b1", .Cells(LastRow, LastCol))
.Sort Key1:=.Range("B1"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlLeftToRight
End With
End With
End Sub

Test it before you trust it.