View Single Post
  #7   Report Post  
Posted to microsoft.public.excel.misc
Gord Dibben Gord Dibben is offline
external usenet poster
 
Posts: 22,906
Default Need macro to move to first blank cell in column

Joe

You have lost the stop recording toolbar?

Go to ToolsMacroRecord new macro.

Click OK on the macroname and where to store dialog.

ToolsCustomize...............hit Stop Recording which will show the SR
Toolbar with a stop button and relative reference button,

Click on the stop recording button........DO NOT click on the "x" or you
will lose the toolbar again.

Delete the module with your dummy macro.

Next time you record a macro the SR toolbar should come up.


Gord Dibben MS Excel MVP

On Wed, 16 Dec 2009 13:32:02 -0800, Joe M.
wrote:

Jim,

I looked for the Relative option when I go to TOOLS MACRO STOP
RECORDING, I don't see this option. I don't see a macro toolbar. I'm using
Excel 2003.
Many thanks,
Joe M.


"Jim Thomlinson" wrote:

What you are doing is a mix of Relative and Staic references. You can record
a mocro that will do this but it is tricky. Oddly enough it is much easier to
write from scratch.

If you want to record the macro then Before you do the down arro operation
followed by the subsequent down arrow, first select the relative opton on the
Stop recording tool bar. This makes your movements relative to the active
cell and not absolute. To complete this macro you will have to switch it on
and off fairly often and plan on getting it wrong more than once...

Written from scratch it will look something like this...

Sub CopyStuff()
dim rngToPaste as range

set rngtopaste = cells(rows.count, "A").end(xlup).offset(1,0)
'rngtopaste is now at the first blank cell in column A
sheets("Sheet1").Range("C1").copy rngtopaste 'paste in A
Sheets("Sheet2").range("D100").copy rngtopaste.offset(0,1) 'Paste in B
rngtopaste.offset(-1, 2).copy rngtopste.offset(0,2) 'Paste in C

end sub
--
HTH...

Jim Thomlinson


"Joe M." wrote:

Hi. I am trying to use a macro to move from A1 to the first blank cell in col
A. There are no gaps in the data. When the first blank cell is selected, data
will be copied and pasted from another worksheet. Then the adjacent cell in
col B must be selected and again data will be copied and pasted there from
another worksheet. Finally, the adjacent cell in col C must be selected but
the formula from the cell above must be extended down one cell. I have
recorded a macro which does all this but the problem is that when I repeat
the macro the same row is always selected instead of the next blank one. Here
are the steps I did while recording the macro:
1 .Select & copy A1
2.Press <cntl + <end (this brings me to last filled cell in col C)
3. Press down arrow key 1 time and left arrow key 2 times to move to 1st
blank cell in col A.
4. Paste in col A blank cell
5. Copy cell from other worksheet (always the same cell #)
6. Select original worksheet. Press right arrow key once to move to col B.
7. Paste in col B blank cell
8. Press right arrow once and up arrow once to move to last filled cell in
col C.
9. Extend the formula down one cell in col C.
Now the previous 3 blank cells in col A, B & C are filled. But when I
repeat the macro the same cells are filled, not one down each time. Can
someone help?

Thanks,
Joe M.