View Single Post
  #7   Report Post  
ratchick
 
Posts: n/a
Default How can I transpose rows to columns in a large worksheet?

Dave! You are my hero!

You've cut down my work by half, and maybe by 99% if I can figure out how to
transpose my rows into columns before running this macro. Right now what it
is doing is taking my columns and putting them into one...which is very good.
However I have to get my rows to turn into columns so that the data is in
the proper order first. I'll check out your macro to see where I might be
able to scooch in something to do that.

Thanks so much!
Suzanne

"Dave Peterson" wrote:

If I had 30 worksheets to do this against, I think I'd use a macro.

You can group the sheets you want to fix (click on the first tab and ctrl-click
on the subsequent tabs).

Then run this macro:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim LastCol As Long
Dim FirstCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iCol As Long
Dim DestCell As Range

For Each wks In ActiveWindow.SelectedSheets
With wks
FirstRow = 1 'no headers!
FirstCol = 2 'don't touch column A.
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

For iCol = FirstCol To LastCol
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
LastRow = .Cells(.Rows.Count, iCol).End(xlUp).Row
.Range(.Cells(FirstRow, iCol), .Cells(LastRow, iCol)).Cut _
Destination:=DestCell
Next iCol
End With
Next wks

'ungroup all the sheets!
worksheets(1).select

End Sub

I use row 1 to find the last column with data.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

ratchick wrote:

I have a worksheet with 1268 rows and 31 columns. Ideally I need all the
rows to be converted to one column, although I believe this is impossible
without some additional manual steps.

Every time I attempt to copy/transpose my rows to columns, I receive the
message that the paste area does not match the size of the copy area. I have
tried transposing a few rows at a time. I have tried transposing ONE row at
a time. Each time I try, I either receive an error message, or get the
#VALUE! error message in the cell. I've tried TRANSPOSE and MINVERSE.

I would prefer NOT to do this manually as I have about 30 worksheets of a
similar size, and I'd really like to have my thesis done in five
months...sooo...if anyone has any ideas for me, they would be much
appreciated.

As well, if Excel cannot handle an action of this size, does anyone know any
other programs that might??

Thanks,
Suzanne


--

Dave Peterson