ActiveCell.CurrentRegion property

[Microsoft Forum]

Hi all,

I have a problem about the Activecell.CurrentRegion property.

Suppose the active sheet has the following data (A - C and 1 - 3 are column
and row headings respectively):

A B C
1 x y
2 z
3

If the active cell is on B2 then the activecell.currentregion will return
A1:B2, which is the one I anticipated.

However if the active cell is on B3 then activecell.currentregion will
return A1:B3, which is not the one I want. I want the property to return
only B3. How can I do it, or any other workaround? Thanks for your advice.

Frederick Chow
Hong Kong.

 

[Jim Rech]

MS considers cells to be in the same current region as long as they are
connected by shared sides or corners. Since you cannot change that you'd
have to write a macro to select cells based on your definition.

--
Jim Rech
Excel MVP
| Hi all,
|
| I have a problem about the Activecell.CurrentRegion property.
|
| Suppose the active sheet has the following data (A - C and 1 - 3 are
column
| and row headings respectively):
|
| A B C
| 1 x y
| 2 z
| 3
|
| If the active cell is on B2 then the activecell.currentregion will return
| A1:B2, which is the one I anticipated.
|
| However if the active cell is on B3 then activecell.currentregion will
| return A1:B3, which is not the one I want. I want the property to return
| only B3. How can I do it, or any other workaround? Thanks for your advice.
|
| Frederick Chow
| Hong Kong.
|
|

 

[Myrna Larson]

The logic used to determine the current region is to start from the current
cell, then move out in all directions until you get to a region that is
completely enclosed by blank cells (or the edge of the sheet is reached). That
includes checking the cells that touch B3 only at their corners, i.e. A2, C2,
A4, and C4. There is data in A2, hence the result you show.

OTOH, if you were referring to B4, CurrentRegion would be B4 since there is no
data in any of the 8 contiguous cells (A3:C3,A4,C4,A5:C5).

What is your definition of CurrentRegion? Evidently it differs from
Microsoft's. Maybe it's no data in the cells immediately above, below, left,
and right, but the corners don't matter? If so, your code will have to check
each of the appropriate cells individually.

 

[Microsoft Forum]

Hi Myrria,

If the definition of activecell.currentregion then the usefulness of this
property would be really limited.

According to the VBA help, a current region is "a range surrounded by at
least one row and one column". Based on this definition, the current region
should only be A1:B2.

If one has ever used the database commands of Excel which require the user
to select anywhere within a cell, e.g. Data | Sort command, these commands
will "intelligently highlight A1:B2 even though B3 is selected. I thnk it is
the understanding of "current region" by most users.

By the way, any way to imitate the behaviour of the selection behaviour of
"Data|Sort" command? Please advise.

Frederick Chow
Hong Kong.

 

[JE McGimpsey]

One way:

ActiveCell.CurrentRegion(1).CurrentRegion.Select