What is function to select only not empty cells in a column (like SHIFT+END+ArrowDown or UP)

[mar_male]

I want to select only cells with something inside not empty. For
example:
I want to find a function in VBA witch work like SHIFT+END+"ArrowDown
or Up"
I have
_____A_____B____
1 | string | |
2 | string | |
3 | string | |
4 | string | |
...........................
40| string | |
41| | |
Cell A41 is empty
I want to select only cells from A1 to A40 , but I dont want to use
something like this (from
Cells A1 to Cells A40 Select) I want to find a function which will do
it automaticaly without
enetering number of cells like from A1 to A40 Select.
Do you know is there a special function in Excel.
If you write some text in column A like mentioned above and go with
cursor on cel A1 than
turn holdSHIFT+ END + DownArrow than it select cells witch text inside
from A1 to A40, how to do something like this in VBA??
Thank you for response

 

[Douglas J Steele]

You'd be better off asking this in a newsgroup related to Excel. This one's
for questions about Access, the database product that's part of Office
Professional.

However, Excel does give you the ability to record macros. Select Tools |
Macros | Record New Macro, do what you're trying to do using the keyboard
and/or mouse, stop the recorder and look at the VBA it generated.

 

[Guest]

I like this question. There is nothing better than Access controlling
everything in the Office Suite. Access rules. So in answer to your question
the code would be -

xlapp.Selection.CurrentRegion.Select

xlapp being a reference to Excel from Access.

This will select all adjacent non null cells.

Good luck and keep on Accessing!

 

[David C. Holley]

This is an Aceess group.

 

[Guest]

I think you guys are missing the point. There is defiantly a grey area when
it comes to VBA code. I control Excel thru Access all the time. I can't
remember the last time I coded in Excel. And I personally think Access
coders are the best VBA coders bar none.

 

[Douglas J Steele]

No, I don't think we missed the point.

I automate Excel from Access all the time. And if I want to find the VBA
required in Excel so that I can run it from Access, I almost always record a
macro in Excel and use that as the basis for creating my Access code.

 

[Guest]

My apoligies. I was actually more refering to the comment by David Holley.
It was a bit short. And I was coming off an early morning wake up a call on
our sql server, so I was a little punchey.

 

[Larry Linson]

. . . I can't remember the last time I coded in Excel. And I
personally think Access coders are the best VBA coders bar none.

I know some outstandingly brilliant Access VBA coders, however, who never
use it to control Excel through automation, are thus not familiar with the
Excel object model, and who aren't very good resources for Excel
information. If you post Excel VBA questions in the Access newsgroup, you
really ought to so indicate in the Subject of the post so people who can't
or won't answer Excel VBA will not have to open the post to discover that it
is Excel VBA.

Larry Linson
Microsoft Access MVP