Macro to move/delete pairs of cells

[tdakil]

Hi -- I need help creating a macro to move and delete pairs of cells.

Background: I keep to-do lists using a system recommended by Dave Allen's Getting Things Done. The items are in pairs of columns. Each pair of columns represents one category (eg calls to make, things to do in the office). The left column of the pair contains the forecast time to do the job and the right column contains the details of the job. Eg Cell C15 contains 5, and cell D 15 contains 'Pay phone bill'.

Here's how I do it manually at present. When I've done an item, I move the two cells (the forecast time and the details) to columns A and B. I do this by copying the contents of the two cells to the first empty row in cols A and B, and then go back and Edit:delete:shift cells up.. Note that I don't do Edit:delete:entire row, because then that would delete various items that haven't been done in the other columns.

I'd like a way to do this with a single keystroke, but I'm not good enough at macros to do it. One way to do it could be to create a macro that does the following steps and then assign it to a shortcut key.

1. Copy the current cell and the cell to its right to the clipboard.
2. Go to the first empty cell from the top in column A.
3. Paste the contents of the two cells (hence into cols A and B)
4. Go back to the two highlighted cells from which the copies were made
5. Edit:delete:shift cells up.

Putting step 1 is a macro is easy.
Step 2 I tried to do by naming a range 'Next' and defining it as =offset(A1,counta(B:B),0), but that doesn't seem to work.
Step 3 is easy.
Step 4 I don't know how to do.
Step 5 would be easy, but I can't get there.

Can anyone help, please?

The steps could be:

 

[JamesW]

Hi there,

Welcome to the board.

Try:

Sub test()
    Dim copyRng As Range
    emptyRow = Range("A1").End(xlDown).Row + 1
    Set copyRng = Range(ActiveCell, ActiveCell.Offset(0, 1))
    copyRng.Copy
    Paste Range("A" & emptyRow)
    copyRng.Delete (xlShiftUp)
End Sub

 

[tdakil]

James, thanks so much.

And this is a *really* dumb question:
- do I replace test() with some other string in the subroutine name?
- and how do I assign this macro to a keystroke?

 

[tdakil]

Wait. I found out how to assign it to CTRL-SHIFT-K, and I renamed it

Sub Clear2Cells()

But now I get an error dialog when I try to invoke it:

Compile error:
Sub or Function not defined
OK/Help

Do I have to copy the two current cells into the clipboard before I invoke the macro to make it work? When I do this, the macro doesn't move the contents of the cells, but it doesn't throw up the error dialogue.

What I'd really like is to include the copying in the macro, so it takes the cell I'm in right now and copies that cell plus the one to the left of it into the clipboard, and then does the moving.

Thank you in case you can help!