Excel Hints & Tips – Mixing Absolute + Relative Mode

How do you record a macro that will copy data from a set area on your spread sheet and then paste it into the first available empty row, in an ever increasing list?

In the example below our data is imported into an area of our spread sheet in column B – the Sales Order Form (in yellow). We then wish to copy this data from the yellow area and transpose it as a new record in the first available empty row below any existing records – with the blue field headings in row 2.

pic 1

Step 1:- Start your macro recorder with the setting set to Absolute mode

Step 2:- Select the cells to be transposed – cells B3:B11

Step 3:- Copy into system memory (Ctr+C)

Step 3:- Select the first cell in the existing table where the record is to be transposed – cell E2

Step 4:- Move the cursor down to the last record – press Ctr+   (down cursor)

Step 5:- Switch the recording to Relative mode

pic 2

Step 6:- Click into the empty cell immediately below (in this example this happens to be E6)

Step 7:- Choose, Home à Paste à Transpose (as below)

Pic 3

Step 8:- Press Escape

Step 9:- Stop Recoding

So by starting at the top of the table and using the Ctr+down-key (in Absolute mode) followed by a switch to Relative mode and then clicking one cell lower, the replay is always able to locate the first empty row wherever it happens to be!

Written by Happy Trainer Chris James.

If you have any Excel questions or hints and tips you would like shared get in touch, use this link!

If you would like any further information on the Excel course we train, go to our dedicated Excel page on our website. If you are unsure of your Excel level, take our handy Excel survey.

Remember – If you’ve been on a Happy Computers course you get 2 years free helpline access.  Call the helpline on 020 7375 7373.

Happy Computers

1 response to “Excel Hints & Tips – Mixing Absolute + Relative Mode”

  1. Love this – didn’t know you could switch modes in the middle of recording! Nice one Chris 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *