Flipping Data

[N1KO]

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

 

[Newbie]

copy > paste special > check 'Transpose'

 

[Newbie]

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

 

[p45cal]

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 E
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 i
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 whic
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 whol
range you want flipping in each of the 3 occasions it's used, and (b) i
in the same columns as the range to be flipped. (It can be used slightl
differently so that your result is anywhere in the sheet, for exampl
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 wit
values:Sub blah() 'horizontal flip
If Selection.Areas.Count = 1 And Selection.Rows.Count = 1 Then '
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 Su

 

[N1KO]

Hi,

Yes technically it is being flipped horizontally sorry.

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

Niko

 

[Rick Rothstein]

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.