Solved: Changing rows to columns????

[twelvety]

I have three worksheets named 'R', 'G' and 'B' which each have 187 rows and 250 columns. Each cell has a number in it and I need to switch the rows into columns in each sheet. For example the first cells;

....A. B. C. D. E
1 57 64 17 4 152
2 85 98 5 76 12
3 3 45 12 76 32
4 38 74 56 82 19

Would become;

....A. B. C. D
1 57 85 3 38
2 64 98 45 74
3 17 5 12 56
4 4 76 76 82
5 152 12 32 19

I have very limited vba knowledge and was hoping to find some code to do this for me. I'm sure this is very simple but unfortunately I don't have the skills to be able to do this myself.

Anyhelp is much appreciated.
Chris

[Bob Phillips]

Dim LastCol As Long

With Range("A1")

.CurrentRegion.Copy
LastCol = .End(xlToRight).Column
.Offset(0, LastCol).PasteSpecial Paste:=xlPasteAll, Transpose:=True
.Resize(, LastCol).EntireColumn.Delete
End With

[vicks]

Xld, I am afraid but some error is there in following line

.Offset(0, LastCol).PasteSpecial Paste:=xlPasteAll, Transpose:=True

[Bob Phillips]

Would you care to elaborate on that, I cannot see any problem.

[vicks]

I have attached the sheet, if you run it, you will get the error. I am sorry, i also might be wrong. Sorry in advance.

[twelvety]

Thanks for your help xld. When I try to run this code I get an error saying "Compile error: invalid outside procedure" and Visual Basic highlights the line "With Range ("A1")". Are you able to help with this at all.

Also I am using excel 2007, I don't know if this makes a difference.

Regards
Chris

[Bob Phillips]

I have attached the sheet, if you run it, you will get the error. I am sorry, i also might be wrong. Sorry in advance.

Got it, a special case I didn't cater for



Sub transpose()
Dim LastCol As Long

With Range("A1")

.CurrentRegion.Copy
If .Offset(0, 1).Value = "" Then

LastCol = 1
Else
LastCol = .End(xlToRight).Column
End If
.Offset(0, LastCol).PasteSpecial Paste:=xlPasteAll, transpose:=True
.Resize(, LastCol).EntireColumn.Delete
End With

End Sub

[Bob Phillips]

Thanks for your help xld. When I try to run this code I get an error saying "Compile error: invalid outside procedure" and Visual Basic highlights the line "With Range ("A1")". Are you able to help with this at all.

Also I am using excel 2007, I don't know if this makes a difference.

Regards
Chris

You have to put it in a macro. See vicks attachment, that does it.

[vicks]

Thanks xld

Thats was a good one. However still one problem persist if there is a gap (a blank row or column) the same gets deleted. See the attached file if you run the macro the digits 56 and 48 gets deleted. Can there be potential plug for this?

Regards

Vicks

[Bob Phillips]

Sub transpose()
Dim LastRow As Long
Dim Lastcol As Long

With ActiveSheet

On Error Resume Next
LastRow = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Lastcol = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
.Range("A1").Resize(LastRow, Lastcol).Copy
.Range("A1").Offset(0, Lastcol).PasteSpecial Paste:=xlPasteAll, transpose:=True
.Range("A1").Resize(, Lastcol).EntireColumn.Delete
End With

End Sub

[vicks]

xld

this was amazing

can you please explain how the following line works and what is their use, for a beginner like me?

LastRow = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Lastcol = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column

Great Regards

Vicks

[Bob Phillips]

It just uses find to search for the very last occupied cell and returns the row and column number of that cell.

[Bob Phillips]

You could also use this code



Dim LastRow As Long
Dim LastCol As Long

With ActiveSheet.UsedRange

LastRow = .Row + .Rows.Count - 1
LastCol = .Column + .Columns.Count - 1
End With

[twelvety]

Got it, a special case I didn't cater for



Sub transpose()
Dim LastCol As Long

With Range("A1")

.CurrentRegion.Copy
If .Offset(0, 1).Value = "" Then

LastCol = 1
Else
LastCol = .End(xlToRight).Column
End If
.Offset(0, LastCol).PasteSpecial Paste:=xlPasteAll, transpose:=True
.Resize(, LastCol).EntireColumn.Delete
End With

End Sub


Thanks xld, this is brilliant and exactly what I wanted. I have one further problem I hope you can help with. How do I effectively spin this entire array by 90 degrees?

i.e. the first rows of cells (1)

45 86 63 69 64 91

would become the first column (A)

91 64 69 63 86 45

for my 250 x 187 array?

Thanks in advance
Chris

[Bob Phillips]

ActiveSheet.DisplayRightToLeft = True

[twelvety]

ActiveSheet.DisplayRightToLeft = True


As I am new to VBA I am unsure where to replace this in the original code. Sorry! Could you please tell me where in the original code to place this?

[Bob Phillips]

Don't place it anywere, I was just messing with you. That code turns the spreadsheet around, so that it reads right to left!

This is what you really want


Sub Reflect()
Dim LastCol As Long
Dim i As Long

With ActiveSheet

LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
.Rows(2).Insert
For i = 1 To LastCol

.Cells(2, i).Value = .Cells(1, LastCol - i + 1).Value
Next i
.Rows(1).Delete
End With
End Sub

[twelvety]

Hi, I've tried this code but it doesn't do what I'm trying to achieve. I have 187 rows of numbers and 250 columns which I am trying to rotate 90 degrees to the left meaning there would then be 250 rows and 187 columns. For example if it were for 5 rows and 5 columns it would go from:

....A......B......C......D......E
1..54....87....123....43....34
2..78...158....43.....83....19
3..34....89....195....67....12
4..294..36.....32.....48....57
5..28....39.....69....152...48

would become

....A......B......C......D......E
1..34....19.....12....57....48
2..43....83.....67....48....152
3..123...43....195...32....69
4..87...158.....89....36....39
5..54....78......34...294...28

The full stops are just to try and keep the numbers in each column.

[Bob Phillips]

Sub transpose()
Dim LastRow As Long
Dim Lastcol As Long
Dim i As Long

With ActiveSheet

On Error Resume Next
LastRow = .Cells.Find( _
What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Lastcol = .Cells.Find( _
What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
.Range("A1").Resize(LastRow, Lastcol).Copy
.Range("A1").Offset(0, Lastcol).PasteSpecial Paste:=xlPasteAll, transpose:=True
.Range("A1").Resize(, Lastcol).EntireColumn.Delete

For i = LastRow - 1 To 1 Step -1

.Cells(i, "A").Resize(, Lastcol).Copy .Cells(LastRow * 2 - i, "A")
Next i

.Rows(1).Resize(LastRow - 1).Delete
End With
End Sub

[twelvety]

Hi

Using this code I unfortunately lose the last 63 lines of data although it does do what I am trying to achieve. Can this code be adapted so that column IP becomes row 1 and column A becomes row 250?

Thanks in advance
Chris

[twelvety]

For info, this is what I am trying to manipulate 69

[Bob Phillips]

Sub transpose()
Dim LastRow As Long
Dim LastCol As Long
Dim i As Long

With ActiveSheet

On Error Resume Next
LastRow = .Cells.Find( _
What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
LastCol = .Cells.Find( _
What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
.Range("A1").Resize(LastRow, LastCol).Copy
.Range("A1").Offset(0, LastCol).PasteSpecial Paste:=xlPasteAll, transpose:=True
.Range("A1").Resize(, LastCol).EntireColumn.Delete

For i = LastCol - 1 To 1 Step -1

.Cells(i, "A").Resize(, LastRow).Copy .Cells(LastCol * 2 - i, "A")
Next i

.Rows(1).Resize(LastCol - 1).Delete
End With
End Sub