Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using LINEST with a sparse data row? | Excel Programming | |||
How do I graph a pivot table of sparse values? | Charts and Charting in Excel | |||
Can you Sparse a field in Excel??? and Database Query?? | Excel Discussion (Misc queries) | |||
Efficent way of setting sparse array of formulas? | Excel Programming | |||
finding last row number of UsedRange | Excel Programming |