You guys apparently missed a stated
x y z
===========
instead of zyx, so I offered 2 varieties
Sub CustomSortSAS() 'converts to values
Rows(3).Clear
x = Application.Match(Application.Min(Rows(2)), Rows(2))
y = Application.Match(Application.Max(Rows(2)), Rows(2))
Cells(3, y - x + 2).Resize(, x - 1).Value = _
Cells(2, 1).Resize(, x - 1).Value
Cells(3, 1).Resize(, y - x + 1).Value = _
Cells(2, x).Resize(, y - x + 1).Value
Cells(3, 1).Resize(, y - x + 1).Sort Key1:=Cells(3, 1), Order1:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight
End Sub
Sub CustomSortSAS1() 'copies
x = Application.Match(Application.Min(Rows(2)), Rows(2))
y = Application.Match(Application.Max(Rows(2)), Rows(2))
Cells(2, 1).Resize(, x - 1).Copy Cells(3, y - x + 2)
Cells(2, x).Resize(, y - x + 1).Copy Cells(3, 1)
Cells(3, 1).Resize(, y - x + 1).Sort Key1:=Cells(3, 1), Order1:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight
End Sub
From: Sam Mathai Chacko
Sent: Sunday, September 25, 2011 11:53 AM
To: MS EXCEL AND VBA MACROS
Subject: Re: $$Excel-Macros$$ columns data need to be in reverse order
Also, on second thought, the column count should be more sure than the value
count. So
=INDEX($A$2:$M$2,COLUMNS(A2:$M$2)) would be better than
=INDEX($A$2:$M$2,COUNTA(A2:$M$2))
Regards,
Sam Mathai Chacko (GL)
On Sun, Sep 25, 2011 at 10:20 PM, Sam Mathai Chacko <samde...@gmail.com> wrote:
Here's the working example.
Regards,
S
On Sun, Sep 25, 2011 at 10:18 PM, GoldenLance <samde...@gmail.com> wrote:
This short and simple macro should reverse the range for you
Sub RangeReverser()
Dim rngCell As Range
Dim rngCells As Range
Dim lngCounter As Long
Const lngOffsetRow As Long = 6 'How many rows below you want the
reversed range
Set rngCells = Range("A2:M2")
For Each rngCell In rngCells
rngCell.Copy rngCell.Offset(lngOffsetRow,
(rngCells.Cells.Count - ((lngCounter * 2) + 1)))
lngCounter = lngCounter + 1
Next rngCell
End Sub
OR, you could use a formula to reverse it too
Use =INDEX($A$2:$M$2,COUNTA(A2:$M$2)) and drag to the right
Regards
Sam Mathai Chacko (GL)
On Sep 23, 8:54 pm, "dguillett1" <dguille...@gmail.com> wrote:
> Try in a macro enabled workbook (.xls or .xlsm)
>
> Sub CustomSortSAS() 'converts to values
> Rows(3).Clear
> x = Application.Match(Application.Min(Rows(2)), Rows(2))
> y = Application.Match(Application.Max(Rows(2)), Rows(2))
>
> Cells(3, y - x + 2).Resize(, x - 1).Value = _
> Cells(2, 1).Resize(, x - 1).Value
>
> Cells(3, 1).Resize(, y - x + 1).Value = _
> Cells(2, x).Resize(, y - x + 1).Value
>
> Cells(3, 1).Resize(, y - x + 1).Sort Key1:=Cells(3, 1),
Order1:=xlDescending, _
> Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlLeftToRight
> End Sub
> ‘or
> Sub CustomSortSAS1() 'copies
> x = Application.Match(Application.Min(Rows(2)), Rows(2))
> y = Application.Match(Application.Max(Rows(2)), Rows(2))
> Cells(2, 1).Resize(, x - 1).Copy Cells(3, y - x + 2)
> Cells(2, x).Resize(, y - x + 1).Copy Cells(3, 1)
> Cells(3, 1).Resize(, y - x + 1).Sort Key1:=Cells(3, 1),
Order1:=xlDescending, _
> Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlLeftToRight
> End Sub
>
> From: shivashankar katageri
> Sent: Friday, September 23, 2011 1:21 AM
> To: excel-macros@googlegroups.com
> Subject: $$Excel-Macros$$ columns data need to be in reverse order
>
> Dear all,
>
> Any body help in this matter,please have luk of the attachement,
>
> I have lot of columns data,Now I want all the data with formula in
reverse manner
>
> --
>
> Thanks & Regards
>
> Shivashankar.C.Katageri
>
> 9945800300
>
> --
>
----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links
:http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below
linkhttp://www.facebook.com/discussexcel
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
Sam Mathai Chacko
--
Sam Mathai Chacko
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
