Reverse column order with a macro

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

misterhanky@gmail.com wrote:
>
> 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

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 wrote:
> 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@gmail.com wrote:
> >
> > 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

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.

misterhanky@gmail.com wrote:
>
> 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 wrote:
> > 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@gmail.com wrote:
> > >
> > > 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

--

Dave Peterson