I have 135,000 cells that need to be transposed which is too large to use the paste special transpose or the transpose array. The range does fit within excels row and column limits but excel just can't handle that many cells at once. Is there vba that would allow me to take the entire selection and transpose itin pieces? Or is there another way to approach this?
Edit: I should add that I can transpose a row or column at a time but I would need to do that 8000 times.
Last edited by JPfowl; 08-14-2013 at 11:50 AM.
If you are transposing from rows to columns, the limit is 16384
Attach a sample, including a desired result. To attach, go to advance then attachment.
I can't attach a sample as the document I am working on has sensitive information.
We do not need your actual data. We need a sample of the layout and format of your data. Remove any confidential information. It needs to mimic your actual data. With out a sample, I can not see how the layout of the sheet. I can not test it either.
The choice is yours.
This doc contains a sample of smaller size. My actual data has 2171 rows and 628 columns. I was thinking I could transpose by taking a row or column at a time and transposing that. Conceptually you would take a count of the rows, transpose the first row to column, increase your transpose varible and repeat until the transpose varible reaches the count of rows. I just don't have enough experience to create the code for this. test.xlsx
The code works on the on the sample, and need to test it on actual data. The result is in new sheet.
Rather than turning the sensitive data into junk letters, I just ran the macro. It works. Here's the macro for anyone else that wants to use it (credit goes to AB33):
Formula:
Please Login or Register to view this content.
JPfowl,
You are welcome!
Please mark this thread as solved!
Today I tried the code again and it failed because "subscript was out of range". If I understand the code correctly then the sheet name is not correct. Is there a way to change the code to a more generic selection (using the actively selected cells rather than a fixed sheet)? Also does it matter if I am using r1c1 instead of the "normal" cell reference nomenclature?
test.zip
I have tried the code on the attached and it works. Here is an amended code. This code works with any sheet name, but the cursor has to be on the active sheet, i.e. where the source sheet when you run the code.
Please Login or Register to view this content.
Do you know how large of a range that this can handle?
Over 1m rows and 16384 columns.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks