Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Greegan
 
Posts: n/a
Default 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   Report Post  
Myrna Larson
 
Posts: n/a
Default

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   Report Post  
David McRitchie
 
Posts: n/a
Default

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   Report Post  
Greegan
 
Posts: n/a
Default

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   Report Post  
Greegan
 
Posts: n/a
Default

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   Report Post  
Greegan
 
Posts: n/a
Default

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   Report Post  
David McRitchie
 
Posts: n/a
Default

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   Report Post  
Dana DeLouis
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Go to first blank cell Curt D. Excel Worksheet Functions 4 February 28th 05 11:27 AM
How to highlight row and column of the selected cell Row_Column_Highlight Excel Discussion (Misc queries) 2 February 27th 05 11:48 PM
COPY A CONCATENATE CELL TO BLANK CELL PUTTING IN THE NEXT BLANK C. QUEST41067 Excel Discussion (Misc queries) 1 January 15th 05 10:29 PM
conditional formatting blank cell TREK5200 Excel Discussion (Misc queries) 1 December 6th 04 03:23 AM
VLookup resulting in a blank cell... KempensBoerke Excel Worksheet Functions 1 October 28th 04 09:57 PM


All times are GMT +1. The time now is 04:35 PM.

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

About Us

"It's about Microsoft Excel"