Reverse column order with a macro

  • Thread starter Thread starter misterhanky
  • Start date Start date
M

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.
 
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...
 
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.
 
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.
 
Back
Top