A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Need macro to move to first blank cell in column



 
 
Thread Tools Display Modes
  #1  
Old December 16th 09, 07:25 PM posted to microsoft.public.excel.misc
Joe M.
external usenet poster
 
Posts: 97
Default Need macro to move to first blank cell in column

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.
Ads
  #2  
Old December 16th 09, 07:46 PM posted to microsoft.public.excel.misc
Rik_UK
external usenet poster
 
Posts: 22
Default Need macro to move to first blank cell in column

If the description of what you are doing is all you would like to achieve,
then this could be acheived by formulae in each cell.

From your description:
cell A1 is copied down column A of the same sheet
cell B2 and subsequent cells in column B are the same cell reference from
another sheet (assume named 'sheet2' and cell ref is B1)
cell C1 is copied down column C of the same sheet.

If you put =$A$1 in cell A2; ='sheet'!$B$1 in cell B2; =C1 in cell C2; and
and the select/highlight the three cells A2,B2,C2 together and then click and
drag down the formulas in the columns you will get the result as described,
without the use of macros.

The reason that the recorded macro doesent work though, is that it is
'static'. Unfortunately Excel is unable to detirmin when an incremental macro
is required, for example to step through rows. The macro would need to be
viewed in the VB editor and edited to allow the row counts to increment, or
for the last blank cell to be identified and that reference used each time
the maro is run.

Not an ideal answer i know, but i hope it helps...


--
Kind regards

Rik


"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.

  #3  
Old December 16th 09, 08:06 PM posted to microsoft.public.excel.misc
Joe M.
external usenet poster
 
Posts: 97
Default Need macro to move to first blank cell in column

Thanks, but extending a formula down will not help for what I need. I need a
macro as this worksbook is to automatically fill the values. Also, I am not
copying down from A2 to A3 to A4 etc. I am copying A1 to i.e. A43. B43 is
copied from another w/s fixed cell location. C43 is filled by a formula
extended from C42. When the macro is run again the next row; A44, B44 and C44
are to be filled. I hope this better explains what I am trying to do.
Thanks,
Joe M.

"Rik_UK" wrote:

> If the description of what you are doing is all you would like to achieve,
> then this could be acheived by formulae in each cell.
>
> From your description:
> cell A1 is copied down column A of the same sheet
> cell B2 and subsequent cells in column B are the same cell reference from
> another sheet (assume named 'sheet2' and cell ref is B1)
> cell C1 is copied down column C of the same sheet.
>
> If you put =$A$1 in cell A2; ='sheet'!$B$1 in cell B2; =C1 in cell C2; and
> and the select/highlight the three cells A2,B2,C2 together and then click and
> drag down the formulas in the columns you will get the result as described,
> without the use of macros.
>
> The reason that the recorded macro doesent work though, is that it is
> 'static'. Unfortunately Excel is unable to detirmin when an incremental macro
> is required, for example to step through rows. The macro would need to be
> viewed in the VB editor and edited to allow the row counts to increment, or
> for the last blank cell to be identified and that reference used each time
> the maro is run.
>
> Not an ideal answer i know, but i hope it helps...
>
>
> --
> Kind regards
>
> Rik
>
>
> "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.

  #4  
Old December 16th 09, 09:10 PM posted to microsoft.public.excel.misc
Jim Thomlinson
external usenet poster
 
Posts: 5,942
Default Need macro to move to first blank cell in column

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.

  #5  
Old December 16th 09, 09:10 PM posted to microsoft.public.excel.misc
Bill Kuunders
external usenet poster
 
Posts: 303
Default Need macro to move to first blank cell in column

to find the next empty row
you need to insert

Range("A65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Select

this will select the next empty cell in column A
greetings from New Zealand


"Joe M." > wrote in message
...
> Thanks, but extending a formula down will not help for what I need. I need
> a
> macro as this worksbook is to automatically fill the values. Also, I am
> not
> copying down from A2 to A3 to A4 etc. I am copying A1 to i.e. A43. B43 is
> copied from another w/s fixed cell location. C43 is filled by a formula
> extended from C42. When the macro is run again the next row; A44, B44 and
> C44
> are to be filled. I hope this better explains what I am trying to do.
> Thanks,
> Joe M.
>
> "Rik_UK" wrote:
>
>> If the description of what you are doing is all you would like to
>> achieve,
>> then this could be acheived by formulae in each cell.
>>
>> From your description:
>> cell A1 is copied down column A of the same sheet
>> cell B2 and subsequent cells in column B are the same cell reference
>> from
>> another sheet (assume named 'sheet2' and cell ref is B1)
>> cell C1 is copied down column C of the same sheet.
>>
>> If you put =$A$1 in cell A2; ='sheet'!$B$1 in cell B2; =C1 in cell C2;
>> and
>> and the select/highlight the three cells A2,B2,C2 together and then click
>> and
>> drag down the formulas in the columns you will get the result as
>> described,
>> without the use of macros.
>>
>> The reason that the recorded macro doesent work though, is that it is
>> 'static'. Unfortunately Excel is unable to detirmin when an incremental
>> macro
>> is required, for example to step through rows. The macro would need to be
>> viewed in the VB editor and edited to allow the row counts to increment,
>> or
>> for the last blank cell to be identified and that reference used each
>> time
>> the maro is run.
>>
>> Not an ideal answer i know, but i hope it helps...
>>
>>
>> --
>> Kind regards
>>
>> Rik
>>
>>
>> "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.



  #6  
Old December 16th 09, 09:32 PM posted to microsoft.public.excel.misc
Joe M.
external usenet poster
 
Posts: 97
Default Need macro to move to first blank cell in column

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.

  #7  
Old December 16th 09, 10:22 PM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 22,912
Default Need macro to move to first blank cell in column

Joe

You have lost the stop recording toolbar?

Go to Tools>Macro>Record new macro.

Click OK on the macroname and where to store dialog.

Tools>Customize...............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.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
macro to delete rows if cell blank in column puiuluipui Excel Discussion (Misc queries) 4 October 15th 09 05:22 PM
Macro to delete blank rows and move data/info samoan Excel Discussion (Misc queries) 3 September 19th 08 04:50 PM
Move Column within Sheet with VB Macro Letzdo_1t Excel Discussion (Misc queries) 4 May 30th 07 11:43 PM
Macro to go to next blank cell in column RJB Excel Discussion (Misc queries) 3 February 28th 07 04:51 PM
Macro to move to next column iblonger Excel Discussion (Misc queries) 9 January 3rd 06 05:18 PM


All times are GMT +1. The time now is 01:06 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.