My data looks like this:

a 1 2 
b 1 2
c 1 2

I want to reorganise it so it looks like this:

a 1
a 2 
b 1
b 2
c 1
c 2
  • I suspect my question is downvoted because it is a duplicate. Would anyone be so kind as to point me in the right direction so I can delete? – F Bert Aug 31 '15 at 22:03
  • 1
    More likely due to the fact that you didn't try anything yourself - this isn't a "question", it plainly is a request for someone to write a macro for you. – Hannu Sep 1 '15 at 10:08

Please note that this is typed from memory, and NOT tested (I do not have access to Excel currently); you might need to THINK and adapt!

Recording a Macro:

  • Place the selection on the cell containing a
  • Find 'Record a macro', close to it you see "Use relative references"; make the option be active.
  • Start recording the macro.
  • Select a shortcut key for the macro (e.g. SHIFT+CTRL+Q)
  • Now do the actions required to move about the cells and their content, for the FIRST line (keyboard use preferred!)
  • Make sure to END the recording with the cursor on top of b

From now on, the shortcut key repeats the same actions for the line the cursor is on.

Additionally, how to modify the macro to repeat for non-empty lines:

Alt+F8 shows a list of the available macros, where you can select to edit the macro you created above.

If you EDIT a cell WHILE recording the macro you will get a statement in the macro that reads similar to:


... this is easily changed into

if ActiveCell.value<>"" then goto Repeat

which of course belongs at the end of the macro code, just before "end sub".

Then add a line Repeat: at the top, as a marker for where to restart for repeats
(that is a "label", look it up in the help for "goto" if I goofed here).

If you have done the above correctly, the macro will repeat for all lines that has something in the cell where it "ends"

Happy transposing!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.