cut information on rows and traspose paste to columns

**Mengtzee** · 01-18-2012, 02:11 PM

I need to very quickly re arrange the information on the attached worksheet (I have more, thousands more entries).
For eg rows transpose paste A1, A2, A3,A4,A5,A6,A7 TO B1,C1, D1, E1, F1, G1, H1 and then A8, A9, A10, A11, A12, A13, A14 TO B2, C2, D2, E2, F2, G2, H2 and so on and so forth.
i tried using the array formula but i the original information has to stay, if i delete the original, i lose the transpose information.
Currently, i am copying and paste special and then deleting the original and then deleting the excess rows. too time consuming.

Help!!

**vlady** · 01-19-2012, 01:25 AM

sorry i have problems on connections recently.
try this one. macro.

select 7 rows then transpose by 7

excel forum whstvlady(3).xlsxs

tell me if it solve your problem.ok.

thanks..

**Alf** · 01-19-2012, 09:57 AM

Hi Mengtzee

Your data is not 100 % consistent so it’s a bit difficult to write a macro. Still I had a go at it. As there is some manual adjustment you have to do I recommend that you take a look at sheet "info" in the file “exce forum whst_mod.xlsm” before you run the macro.

To test run macro “transpose_row”

Alf

**Mengtzee** · 01-19-2012, 02:31 PM

Thanks a bunch!!!

**Alf** · 01-19-2012, 04:41 PM

Glad you liked it.

Just so you don't misunderstand me. If you add number and letters to rows that is missing a number when you do a "Find" and Replace part you don't write anything in the "Replace" part. My explanation in the file was a bit muddeled I think.

To get rid of blank row you could add this macro

Sub delete_empty_rows()

Dim lastrow As Integer
Dim i As Integer

lastrow = Range("A" & Rows.Count).End(xlUp).Row

For i = lastrow To 1 Step -1

    If Cells(i, 1) = "" Then
            Rows(i).EntireRow.Delete
    End If
    
Next i

End Sub

Perhaps you could run this macro first and then the "transpose_row" macro. As this will reduce the number of "Check for ...." messages.

After fixing rows with missing number and removing the number and letters you added delete column A and run "delete_empty_rows"

If you liked my solution you can click on the star in my post and give a rating to my answer.

Alf

Ps I liked you problem it was quite a bit of fun working with it!

**Mengtzee** · 01-20-2012, 10:18 AM

i recorded my macro but cant seem to repeat the action. I did it yesterday and it worked fine but today....

my steps: click on record macro, perform the task, click stoep recording.

yesterdays results: awesome
todays results in new worksheet: record a new macro in a new wksht but when i try to repeat the action using the shortcut key, the cursow returns to the original spot and nothing happens.

i am so confused!!

**Alf** · 01-20-2012, 11:27 AM

Hi Mengtzee

I'm a bit confused myself. To whom are addressing your question? vlady or me? Your question indicates that you went for vlady's solution and if so vlady will give you the best answer.

Alf

**Mengtzee** · 01-20-2012, 02:41 PM

ok, i went with vlady first but i think your way is going to cut down the process even faster. let me have a go at it. it all seem sdaunting but i think i can take it one step at a time and try to figure everything out. Thanks again for your help Alf. I will update you guys on how I am progressing.