Hi,

Is there any way of flipping data vertically.

For example
I have data in cells A1, B1, C1, D1, E1, F1.

I want the data to be reversed. So A1 goes to F1, F1 to A1 B1 to E1 E1 to B1
etc. I need this to be done on any number of cells.

Either in macro code or cell formulae.

Thanks in advance

Niko

copy paste special check 'Transpose'

"N1KO" wrote:

Hi,

Is there any way of flipping data vertically.

For example
I have data in cells A1, B1, C1, D1, E1, F1.

I want the data to be reversed. So A1 goes to F1, F1 to A1 B1 to E1 E1 to B1
etc. I need this to be done on any number of cells.

Either in macro code or cell formulae.

Thanks in advance

Niko

...sorry misread...
adjacent column, insert 1 to 6 then sort by adjacent column Z to A

"N1KO" wrote:

Hi,

Is there any way of flipping data vertically.

For example
I have data in cells A1, B1, C1, D1, E1, F1.

I want the data to be reversed. So A1 goes to F1, F1 to A1 B1 to E1 E1 to B1
etc. I need this to be done on any number of cells.

Either in macro code or cell formulae.

Thanks in advance

Niko

N1KO;490988 Wrote:
Hi,

Is there any way of flipping data vertically.

For example
I have data in cells A1, B1, C1, D1, E1, F1.

I want the data to be reversed. So A1 goes to F1, F1 to A1 B1 to E1 E1
to B1
etc. I need this to be done on any number of cells.

Either in macro code or cell formulae.

Thanks in advance

Niko

Isn't the range you quote (A1:F1) a horizontal range, so shouldn't it
be flipping horizontally?
Anyway:
1. Worksheet formula solution: In A2 place this formula:
=INDEX($A$1:$F$1,COLUMNS($A$1:$F$1)+COLUMN($A$1:$F $1)-COLUMN())
and copy across to F2.
It's more complex than it needs to be for your example range which
starts in column 1, but this formula will work for a range of any width,
starting in any column, as long as the formula (a) refers the whole
range you want flipping in each of the 3 occasions it's used, and (b) is
in the same columns as the range to be flipped. (It can be used slightly
differently so that your result is anywhere in the sheet, for example
this flips your A1:F1 range to E41:J41 when placed in that range:
=INDEX($A$1:$F$1,COLUMNS($E$41:$J$41)+COLUMN($E$41 :$J$41)-COLUMN())

2. Macro solution: This flips the single row of selected cells in situ.
Be aware that it will replace any formulae with
values:Sub blah() 'horizontal flip
If Selection.Areas.Count = 1 And Selection.Rows.Count = 1 Then 'a
check
xxx = Selection
yyy = xxx
For i = LBound(xxx, 2) To UBound(xxx, 2)
yyy(1, i) = xxx(1, UBound(xxx, 2) - i + 1)
Next i
Selection = yyy
End If
End Sub


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=135342

Hi,

Yes technically it is being flipped horizontally sorry.

Thanks for the help I'll check them out as soon as i can.

Niko

"p45cal" wrote:


N1KO;490988 Wrote:
Hi,

Is there any way of flipping data vertically.

For example
I have data in cells A1, B1, C1, D1, E1, F1.

I want the data to be reversed. So A1 goes to F1, F1 to A1 B1 to E1 E1
to B1
etc. I need this to be done on any number of cells.

Either in macro code or cell formulae.

Thanks in advance

Niko

Isn't the range you quote (A1:F1) a horizontal range, so shouldn't it
be flipping horizontally?
Anyway:
1. Worksheet formula solution: In A2 place this formula:
=INDEX($A$1:$F$1,COLUMNS($A$1:$F$1)+COLUMN($A$1:$F $1)-COLUMN())
and copy across to F2.
It's more complex than it needs to be for your example range which
starts in column 1, but this formula will work for a range of any width,
starting in any column, as long as the formula (a) refers the whole
range you want flipping in each of the 3 occasions it's used, and (b) is
in the same columns as the range to be flipped. (It can be used slightly
differently so that your result is anywhere in the sheet, for example
this flips your A1:F1 range to E41:J41 when placed in that range:
=INDEX($A$1:$F$1,COLUMNS($E$41:$J$41)+COLUMN($E$41 :$J$41)-COLUMN())

2. Macro solution: This flips the single row of selected cells in situ.
Be aware that it will replace any formulae with
values:Sub blah() 'horizontal flip
If Selection.Areas.Count = 1 And Selection.Rows.Count = 1 Then 'a
check
xxx = Selection
yyy = xxx
For i = LBound(xxx, 2) To UBound(xxx, 2)
yyy(1, i) = xxx(1, UBound(xxx, 2) - i + 1)
Next i
Selection = yyy
End If
End Sub


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=135342

Here is another one you can try...

Sub FlipRow()
Dim X As Long, StartCol As Long, LastCol As Long, _
LastRow As Long, DataRow As Long
StartCol = 1
DataRow = 3
With ActiveSheet
LastRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row
LastCol = Rows(DataRow).Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
For X = StartCol To LastCol
.Cells(DataRow, X).Copy .Cells(LastRow + 1, _
StartCol + LastCol - X)
Next
.Range(.Cells(LastRow + 1, StartCol), .Cells(LastRow + 1, LastCol)).Copy
..Cells(DataRow, StartCol)
.Range(.Cells(LastRow + 1, StartCol), .Cells(LastRow + 1,
LastCol)).Clear
End With
End Sub

Change the StartCol and DataRow as appropriate. If you want to do this for
more than one row, then create an outer For..Next loop after the LastRow
assignment statement and before the End With statement using DataRow as the
loop counter variable.

--
Rick (MVP - Excel)


"N1KO" wrote in message
...
Hi,

Is there any way of flipping data vertically.

For example
I have data in cells A1, B1, C1, D1, E1, F1.

I want the data to be reversed. So A1 goes to F1, F1 to A1 B1 to E1 E1 to
B1
etc. I need this to be done on any number of cells.

Either in macro code or cell formulae.

Thanks in advance

Niko