finding UsedRange with sparse data
 

I am creating an addin that works with user created 'databases'.

Assume there is a 'database' that looks something like:

Name | Phone | Address | City | State
Dave | | | |
Thom | | | |
Mary | | | |
Joe | | | |
Beth | | | |
Sue |12345 | Home |Town |NY

It can be anywhere on a given worksheet, the only requirement is that
is have no blanks in the first row.
The 'database' is user created so the code doesn't know how many
columns or rows, or where the upper left corner is.

When the user clicks on a cell within the bounds of the 'database' and
then clicks on a menu button provided by the addin, some action is
taken on the database.

When the database is not sparse using activecell.currentregion works
fine. When the 'database' is sparse activecell.currentregion doesn't
work. e.g. user clicked on Mary's Address above.

Any suggestions on how to define the database (CurrentRegion) with
sparse data?

finding UsedRange with sparse data
 

On 8 Jan., 00:02, dbKemp wrote:
I am creating an addin that works with user created 'databases'.

Assume there is a 'database' *that looks something like:

* Name | Phone | Address | City | State
* Dave * | * * * * * | * * * * * * *| * * * *|
* Thom *| * * * * * | * * * * * * *| * * * *|
* Mary * | * * * * * | * * * * * * *| * * * *|
* Joe * * | * * * * * | * * * * * * *| * * * *|
* Beth * | * * * * * | * * * * * * *| * * * *|
* Sue * * |12345 | Home * * |Town |NY

It can be anywhere on a given worksheet, the only requirement is that
is have no blanks in the first row.
The 'database' is user created so the code doesn't know how many
columns or rows, or where the upper left corner is.

When the user clicks on a cell within the bounds of the 'database' and
then clicks on a menu button provided by the addin, some action is
taken on the database.

When the database is not sparse using activecell.currentregion works
fine. *When the 'database' is sparse activecell.currentregion doesn't
work. *e.g. user clicked on Mary's Address above.

Any suggestions on how to define the database (CurrentRegion) with
sparse data?

Try

Activecell.end(xltoLeft).currentRegion

//Per

finding UsedRange with sparse data
 

On Jan 7, 6:29 pm, Per Jessen wrote:
On 8 Jan., 00:02, dbKemp wrote:



I am creating an addin that works with user created 'databases'.

Assume there is a 'database' that looks something like:

Name | Phone | Address | City | State
Dave | | | |
Thom | | | |
Mary | | | |
Joe | | | |
Beth | | | |
Sue |12345 | Home |Town |NY

It can be anywhere on a given worksheet, the only requirement is that
is have no blanks in the first row.
The 'database' is user created so the code doesn't know how many
columns or rows, or where the upper left corner is.

When the user clicks on a cell within the bounds of the 'database' and
then clicks on a menu button provided by the addin, some action is
taken on the database.

When the database is not sparse using activecell.currentregion works
fine. When the 'database' is sparse activecell.currentregion doesn't
work. e.g. user clicked on Mary's Address above.

Any suggestions on how to define the database (CurrentRegion) with
sparse data?

Try

Activecell.end(xltoLeft).currentRegion

//Per

Thanks. Good suggestion, poor example provided (on my part). There
could be a row with no name, but have a value in the state column.
Using a Activecell.end() function could end up 'finding' a 'island'
cell that has a value, but no other cells around with a value so the
CurrentRegion still wouldn't work.

finding UsedRange with sparse data
 

Have the user select the database and then feed the selection
address back to the user in a MsgBox for confirmation.
Then you verify it by checking that the top left and top right cells
contain "Name" and State" and that there at least two(?) rows in it.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"dbKemp"

wrote in message
I am creating an addin that works with user created 'databases'.
Assume there is a 'database' that looks something like:

Name | Phone | Address | City | State
Dave | | | |
Thom | | | |
Mary | | | |
Joe | | | |
Beth | | | |
Sue |12345 | Home |Town |NY

It can be anywhere on a given worksheet, the only requirement is that
is have no blanks in the first row.
The 'database' is user created so the code doesn't know how many
columns or rows, or where the upper left corner is.
When the user clicks on a cell within the bounds of the 'database' and
then clicks on a menu button provided by the addin, some action is
taken on the database.
When the database is not sparse using activecell.currentregion works
fine. When the 'database' is sparse activecell.currentregion doesn't
work. e.g. user clicked on Mary's Address above.
Any suggestions on how to define the database (CurrentRegion) with
sparse data?

finding UsedRange with sparse data
 

On Jan 7, 6:38 pm, "Jim Cone" wrote:
Have the user select the database and then feed the selection
address back to the user in a MsgBox for confirmation.
Then you verify it by checking that the top left and top right cells
contain "Name" and State" and that there at least two(?) rows in it.
--
Jim Cone
San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"dbKemp"

wrote in message
I am creating an addin that works with user created 'databases'.
Assume there is a 'database' that looks something like:

Name | Phone | Address | City | State
Dave | | | |
Thom | | | |
Mary | | | |
Joe | | | |
Beth | | | |
Sue |12345 | Home |Town |NY

It can be anywhere on a given worksheet, the only requirement is that
is have no blanks in the first row.
The 'database' is user created so the code doesn't know how many
columns or rows, or where the upper left corner is.
When the user clicks on a cell within the bounds of the 'database' and
then clicks on a menu button provided by the addin, some action is
taken on the database.
When the database is not sparse using activecell.currentregion works
fine. When the 'database' is sparse activecell.currentregion doesn't
work. e.g. user clicked on Mary's Address above.
Any suggestions on how to define the database (CurrentRegion) with
sparse data?

Thanks Jim. I currently check to see that the top row has no blanks
and that there are at least two rows once I define a CurrentRegion fro
the database. The headers are created by the user, so the code
doesn't know what headers to check for. I am reluctant, due to the
nature of the app to have the user verify every time they want the
addin operation to happen, therefore I would prefer not to have the
user verify the correct selection of the database. Because some of
the databases can be quite large I would prefer not to require the
user to select the entire database. I think some of my users might
even be upset if they had to select one of the header cells.

finding UsedRange with sparse data
 

I would think that there would be a rule that the first column couldn't be empty
either.

If that's true:

Dim LastRow as long
dim LastCol as long
dim myDBRng as Range

with worksheets("somesheetnamehere")
lastrow = .cells(.rows.count,"A").end(xlup).row
lastcol = .cells(1, .columns.count).end(xltoleft).column
set myDBRng = .range("A1",.cells(lastrow,lastcol))
end with

'do something with the myDBRng

dbKemp wrote:

I am creating an addin that works with user created 'databases'.

Assume there is a 'database' that looks something like:

Name | Phone | Address | City | State
Dave | | | |
Thom | | | |
Mary | | | |
Joe | | | |
Beth | | | |
Sue |12345 | Home |Town |NY

It can be anywhere on a given worksheet, the only requirement is that
is have no blanks in the first row.
The 'database' is user created so the code doesn't know how many
columns or rows, or where the upper left corner is.

When the user clicks on a cell within the bounds of the 'database' and
then clicks on a menu button provided by the addin, some action is
taken on the database.

When the database is not sparse using activecell.currentregion works
fine. When the 'database' is sparse activecell.currentregion doesn't
work. e.g. user clicked on Mary's Address above.

Any suggestions on how to define the database (CurrentRegion) with
sparse data?

--

Dave Peterson