Home |
Search |
Today's Posts |
#1
|
|||
|
|||
I want to select the first blank cell in column A
Now I know i could select ctrl down arrow to select the last cell and then
arrow down again to select the next cell. If I record this it suggests I am selecting specific cells. My issue with that is that column A will have a different number of rows occupied by text and whatnot. Is there a command or something that would select the first blank cell in column A? Thank you in advance. You guys are always willing to give an answer. G |
#2
|
|||
|
|||
Range("A1").End(xlDown).Select
But review the message in the programming newsgroup for caveats about not selecting and activating things when you don't have to. On Fri, 11 Mar 2005 23:41:33 -0600, "Greegan" wrote: Now I know i could select ctrl down arrow to select the last cell and then arrow down again to select the next cell. If I record this it suggests I am selecting specific cells. My issue with that is that column A will have a different number of rows occupied by text and whatnot. Is there a command or something that would select the first blank cell in column A? Thank you in advance. You guys are always willing to give an answer. G |
#3
|
|||
|
|||
Hi Greegan,
I'm not sure you are asking what you really want, seems you would want the cell below anything else used in the column, rather than the first available empty cell. i.e. every fifth row is empty. Yours is not actually a worksheet functions questions and since this is not programming group, you might want to read the following if not familiar with installing and using macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Might consider something along the lines the following macro that I use as a toolbar button http://www.mvps.org/dmcritchie/excel...ars.htm#macros of this which takes you to the bottom of the column and then up the an entry and offset down 1 to the next cell. Sub GotoBottomOfCurrentColumn() 'Tom Ogilvy 2000-06-26 Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select End Sub you can modify to go down to next cell Sub GotoBottomOfCurrentColumn_Plus1() 'Tom Ogilvy 2000-06-26 Cells(Rows.Count, ActiveCell.Column).End(xlUp).Offset(1,0).Select End Sub Within your own macro you might to this *without* changing the active cell Cells(Rows.Count, Range("A1").Column).End(xlUp).Offset(1, 0).Value = "** New Entry**" Some of the things like changing the active cell to avoid can be seen in http://www.mvps.org/dmcritchie/excel/slowresp.htm Getting the correct answer to a question you ask is normally what you will get. It takes a bit of work to ask the question you want to ask. By the time you ask the question exactly the way you want, considering additional obstacles and how to work around them, you will find you can answer and program for most of your own questions. Good Luck. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel. "Myrna Larson" wrote... Range("A1").End(xlDown).Select But review the message in the programming newsgroup for caveats about not selecting and activating things when you don't have to. On Fri, 11 Mar 2005 23:41:33 -0600, "Greegan" wrote: Now I know i could select ctrl down arrow to select the last cell and then arrow down again to select the next cell. If I record this it suggests I am selecting specific cells. My issue with that is that column A will have a different number of rows occupied by text and whatnot. Is there a command or something that would select the first blank cell in column A? Thank you in advance. You guys are always willing to give an answer. G |
#4
|
|||
|
|||
Thank you for this answer. However if I use this it will select the last
row, not the next one that's empty. If you can add to this so it selects the first blank then that will be the answer I'm looking for. For example, if Column A rows 1 though 13 are filled in, you macro selects A13. I need it to select the next one down - in this case its A14. But the rows filled in are always random so it needs to be the first blank field in column A. Thank you again. G "Myrna Larson" wrote in message ... Range("A1").End(xlDown).Select But review the message in the programming newsgroup for caveats about not selecting and activating things when you don't have to. On Fri, 11 Mar 2005 23:41:33 -0600, "Greegan" wrote: Now I know i could select ctrl down arrow to select the last cell and then arrow down again to select the next cell. If I record this it suggests I am selecting specific cells. My issue with that is that column A will have a different number of rows occupied by text and whatnot. Is there a command or something that would select the first blank cell in column A? Thank you in advance. You guys are always willing to give an answer. G |
#5
|
|||
|
|||
Thank you for your answer, I will try these macros in excel in just a
moment. However I had failed to mention in my first description that the worksheet will always be sorted by column A prior to this macro is used. Which allows me to ask for the first blank. Thank you again and sorry if i've confused the issue. G "David McRitchie" wrote in message ... Hi Greegan, I'm not sure you are asking what you really want, seems you would want the cell below anything else used in the column, rather than the first available empty cell. i.e. every fifth row is empty. Yours is not actually a worksheet functions questions and since this is not programming group, you might want to read the following if not familiar with installing and using macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Might consider something along the lines the following macro that I use as a toolbar button http://www.mvps.org/dmcritchie/excel...ars.htm#macros of this which takes you to the bottom of the column and then up the an entry and offset down 1 to the next cell. Sub GotoBottomOfCurrentColumn() 'Tom Ogilvy 2000-06-26 Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select End Sub you can modify to go down to next cell Sub GotoBottomOfCurrentColumn_Plus1() 'Tom Ogilvy 2000-06-26 Cells(Rows.Count, ActiveCell.Column).End(xlUp).Offset(1,0).Select End Sub Within your own macro you might to this *without* changing the active cell Cells(Rows.Count, Range("A1").Column).End(xlUp).Offset(1, 0).Value = "** New Entry**" Some of the things like changing the active cell to avoid can be seen in http://www.mvps.org/dmcritchie/excel/slowresp.htm Getting the correct answer to a question you ask is normally what you will get. It takes a bit of work to ask the question you want to ask. By the time you ask the question exactly the way you want, considering additional obstacles and how to work around them, you will find you can answer and program for most of your own questions. Good Luck. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel. "Myrna Larson" wrote... Range("A1").End(xlDown).Select But review the message in the programming newsgroup for caveats about not selecting and activating things when you don't have to. On Fri, 11 Mar 2005 23:41:33 -0600, "Greegan" wrote: Now I know i could select ctrl down arrow to select the last cell and then arrow down again to select the next cell. If I record this it suggests I am selecting specific cells. My issue with that is that column A will have a different number of rows occupied by text and whatnot. Is there a command or something that would select the first blank cell in column A? Thank you in advance. You guys are always willing to give an answer. G |
#6
|
|||
|
|||
Tha is awesome !! That worked like a charm. Thank you very much!
G "David McRitchie" wrote in message ... Hi Greegan, I'm not sure you are asking what you really want, seems you would want the cell below anything else used in the column, rather than the first available empty cell. i.e. every fifth row is empty. Yours is not actually a worksheet functions questions and since this is not programming group, you might want to read the following if not familiar with installing and using macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Might consider something along the lines the following macro that I use as a toolbar button http://www.mvps.org/dmcritchie/excel...ars.htm#macros of this which takes you to the bottom of the column and then up the an entry and offset down 1 to the next cell. Sub GotoBottomOfCurrentColumn() 'Tom Ogilvy 2000-06-26 Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select End Sub you can modify to go down to next cell Sub GotoBottomOfCurrentColumn_Plus1() 'Tom Ogilvy 2000-06-26 Cells(Rows.Count, ActiveCell.Column).End(xlUp).Offset(1,0).Select End Sub Within your own macro you might to this *without* changing the active cell Cells(Rows.Count, Range("A1").Column).End(xlUp).Offset(1, 0).Value = "** New Entry**" Some of the things like changing the active cell to avoid can be seen in http://www.mvps.org/dmcritchie/excel/slowresp.htm Getting the correct answer to a question you ask is normally what you will get. It takes a bit of work to ask the question you want to ask. By the time you ask the question exactly the way you want, considering additional obstacles and how to work around them, you will find you can answer and program for most of your own questions. Good Luck. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel. "Myrna Larson" wrote... Range("A1").End(xlDown).Select But review the message in the programming newsgroup for caveats about not selecting and activating things when you don't have to. On Fri, 11 Mar 2005 23:41:33 -0600, "Greegan" wrote: Now I know i could select ctrl down arrow to select the last cell and then arrow down again to select the next cell. If I record this it suggests I am selecting specific cells. My issue with that is that column A will have a different number of rows occupied by text and whatnot. Is there a command or something that would select the first blank cell in column A? Thank you in advance. You guys are always willing to give an answer. G |
#7
|
|||
|
|||
Thanks, always good to hear feedback, especially when there is
more than one solution offered. Feedback is even better when it indicates which part you used, I guess if it was awesome it included the toolbar button, and not just code for your own macro. Please clip responses there was already one post that contained all of the words someone might have used to find this thread, except perhaps for the word awesome. I wonder if awesome is usually linked to Tom Ogilvy in some manner "Greegan" wrote... That is awesome !! That worked like a charm. Thank you very much! |
#8
|
|||
|
|||
Now I know i could select ctrl down arrow to select the last cell and then
arrow down again to select the next cell. If I record this it suggests I am selecting specific cells. Just to add if it's not already mentioned. In the latest versions of Excel, if you switch to relative reference while recording, Excel will record the End(xlDown) statement for you. This was not the case with earlier versions of Excel, so this was a nice "fix." Just for a demo, start the macro recorder, and select cell A1. Now, look next to the Stop Marco button, and select the "Relative Reference" button. Now, do you ctrl down, and then arrow down, and stop the macro. Your macro should look like this: Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select It's up to you to clean it up a little to something like: Range("A1").End(xlDown).Offset(1, 0).Select HTH -- Dana DeLouis Win XP & Office 2003 "Greegan" wrote in message ... Now I know i could select ctrl down arrow to select the last cell and then arrow down again to select the next cell. If I record this it suggests I am selecting specific cells. My issue with that is that column A will have a different number of rows occupied by text and whatnot. Is there a command or something that would select the first blank cell in column A? Thank you in advance. You guys are always willing to give an answer. G |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Go to first blank cell | Excel Worksheet Functions | |||
How to highlight row and column of the selected cell | Excel Discussion (Misc queries) | |||
COPY A CONCATENATE CELL TO BLANK CELL PUTTING IN THE NEXT BLANK C. | Excel Discussion (Misc queries) | |||
conditional formatting blank cell | Excel Discussion (Misc queries) | |||
VLookup resulting in a blank cell... | Excel Worksheet Functions |