View Full Version : Solved: Changing rows to columns????
twelvety
02-09-2009, 04:58 AM
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
02-09-2009, 05:45 AM
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
02-09-2009, 05:55 AM
Xld, I am afraid but some error is there in following line
.Offset(0, LastCol).PasteSpecial Paste:=xlPasteAll, Transpose:=True
Bob Phillips
02-09-2009, 06:03 AM
Would you care to elaborate on that, I cannot see any problem.
vicks
02-09-2009, 06:17 AM
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
02-09-2009, 06:36 AM
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
02-09-2009, 07:10 AM
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
02-09-2009, 07:11 AM
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
02-09-2009, 07:21 AM
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
02-09-2009, 07:28 AM
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
02-09-2009, 08:06 AM
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
02-09-2009, 08:08 AM
It just uses find to search for the very last occupied cell and returns the row and column number of that cell.
Bob Phillips
02-09-2009, 08:12 AM
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
02-10-2009, 07:16 AM
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
02-10-2009, 07:29 AM
ActiveSheet.DisplayRightToLeft = True
twelvety
02-10-2009, 08:00 AM
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
02-10-2009, 09:32 AM
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
02-10-2009, 03:04 PM
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
02-10-2009, 03:50 PM
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
02-11-2009, 08:46 AM
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
02-11-2009, 08:49 AM
For info, this is what I am trying to manipulate 69
Bob Phillips
02-11-2009, 03:31 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.