is a range.address available to describe the selection in "Center Across Selection"

CONTEXT
As far as I can tell, a major reason merged cells are avoided is that some VBA and excel functions
use headings, expecting the headings to be ONLY one cell wide. Apparently Headings more than one
cell wide can introduce unpredictable results.

Problem:
Unlike merged cells, for which vba has methods and properties that operate on a merged selection,
"Center Across Selection" does not appear to have such assistance. Is that observation correct?

I would like to identify the range which covers the selected region when i use "Center Across Selection".
I then need to identify the string that is within that selected region.

it is not apparent to me that excel stores the selection used in Center Across Selection and makes
the selection available to me to identify its address or other properties (like a text string within)

any thoughts?

Using merged cells can also depend on the VBA author skills !

When a cell formatting uses 'center across columns' so the Selection of this cell is nothing but only this cell
so why should you need the address with empty cells ?!

OH.....

So if I understand you, Marc, "Center across Selection" actually makes the selection APPEAR as a single cell, just as VBA does with a selection using "merge"?
Why not just use "merge"?


I am wondering if Center Across Selection works differently.

Maybe Excel constructs a VIEW that shows only one header stretched across the selection ,but in fact, the header is in each cell, yet invisible . That is how the sort routine using a header can work. a header is in only one cell width. each cell in the selection has an identical invisible header so any code dependent on a single cell header works. such a model of how it works seems logical.

HOWEVER....it does not explain how i can identify the cells in the selection? there ARE multiple cells in that selection, if my model is correct. Somehow i should be able to extract their address as a range.address.

Am I misunderstanding something?

That is why your sort routines work,
is my guess.

Marc,
Please be patient with me. I am truly trying to grasp your help to me. Let me take each of your sentences and see if i can convey to you what I think you said in my own words.

MARC:
"As a sort may work according to merged cells if at least the VBA procedure author takes that in account !"

bil:
Merge cells does not deprive one of doing anything. The coder must just take into account the idiosyncrasies associated with Merging cells.

MARC:
"The Selection difference: with the Merged cells all the merged area is selected but when using Center Across Columns only a cell is selected.
In the last case, if the cell is empty that means the data is stored in a cell to the left."

bil:
In the case of Merged cells, all the cells in the selection are represented as if they are one cell. That one cell is the entire selected region. None of the individual cells that make up the selection can be individually addressed. [my note: interestingly the address of that one cell is not a range but the top left most cell, which seems counterintuitive).

In the case of Center Across Columns, one can selected any single cell within the entire set of cells across which text is presented. I am not sure I understand which cell "contains"
the text. I am not sure how excel knows how to center the text across the set of cells unless it has some internal property that identifies the selection

REMAINING QUESTIONS
I do not know how to identify the effective range of the region across which Center Across Columns has been applied. think of it this way. I am not interested
in the text per se. My primary interest is in identifying the horizontal number of cells of the selection and their starting address. A Range would be awesome.
Secondarily, i then want to identify the text within that selection but have no idea how to address the location containing the text.

So
FIRST: Have i understood your answer
SECONDLY: Do you have a better grasp of what question i am trying to assemble (in my own mind and for you).


AND, purely out of curiosity....nothing else....I would love to know how excel, INTERNALLY, represents merged cells and Center Across Columns. for Merged cells, I can imagine a property of each
cell indicating if it is part of a set of cells and given a way to address that set of cells.

For Center Across Columns, i can imagine exactly the same cell property but in this case, the cell property is only considered in presentation mode and not used with other operators.

Originally Posted by whburling

In the case of Merged cells, all the cells in the selection are represented as if they are one cell. That one cell is the entire selected region. None of the individual cells that make up the selection can be individually addressed.

I agree only if this belongs to the Excel user interface as under VBA any cell even merged can be 'adressed' …

Originally Posted by whburling

[…] which Center Across Columns has been applied.
My primary interest is in identifying the horizontal number of cells of the selection and their starting address.

As it depends on what exactly means here 'selection' …

The beginner training to find out the range of cells centered across columns :
if the horizontal alignment of a cell is 'Center Across Columns' then if this cell is not empty
so this could be the first cell of the CAC range, so just check the cells to the right
until last with the same CAC horizontal alignment.
But if the cell is empty, check to the left until a non empty cell or the first cell with the same CAC formatting
and check to the right of the selected cell to find the last cell …

But this is the easy part when this is just a single CAC but as a CAC can be nested into another CAC
to an outer CAC and an inner CAC …

All the necessary exists for merged cells with different statements, properties …

But for CAC I do not know if something 'public' exists or if it's just an inner private reference.
And as I never met such case where it could be usefull to know it
as it's just for the layout and obviously just observing the data structure so it's easy to get rid of …

And even among thousands forums threads it's the first time someone ask for !
Maybe a question of interest to ask directly on some Microsoft website …

Thank you Marc and Rory for taking your valuable time to help me think better.




let me attempt to justify my approach. Due to my ignorance, there may be many alternative methods of achieving what I am trying to do...
but at the moment, I am believing I have a fairly decent approach.

Goal:
To group information on many worksheets in a workbook
that involves minimal effort to group the data on a worksheet
that minimizes the possibilities of error in constructing the groups on a worksheet
understanding that the number of groups always is constant, but the number of members(rows and/or columns) of a group can change from one worksheet to another.
to detect the worksheet grouping with vba
* extracting from the worksheet the range of (number of rows and columns) data within a group without having to look at the actual data within the grouping.
* accomplishing the above using minimal clearly understood lines of code.
* to identify the grouping with minimal error given that group membership can vary from worksheet to worksheet
* to build a check into the process that confirms a given group is the expected group.

here is how i might be able to achieve the above goal using merged cells (for this example, i am avoiding Center Across Columns by grouping rows, and letting the column grouping be vague)

* the person inputting data into a worksheet
* constructs a unique column (ie: "A") that VBA code will recognize
* constructs a unique row (ie: "1") that VBA code will recognize

* for each group of rows that will be members of a group, create a merged cell in the above Column A
* in the above merged cell, type a string of characters that VBA will recognize.
* each worksheet will always have the same number of groups
* each worksheet may have a different number of members(rows) in each group.

* the code reading the worksheet
* knows where to find column "A"
* sequences down column A identifying the address of each merged cell (one instruction to get address of merged cell). The address enables the code to know the
number of rows in the group and the starting addres
* Confirms the Merged cell is one of the X number of expected finite groups by matching the text string found in a merged cell with a list of acceptable groupings.

* knows where to find Row "1"
* extracts the single merged cell address that stretches across all the data columns that varies from worksheet to worksheet.
* assemble the row and colum inforamtion that represents the range of the data within a single group

8888888888888 THAT IS IT *******************************
A suggestion made by you folks, in previous correspondence was to just put a blank line at the beginning of each row grouping. I suspect that is the least error prone approach for the person inputting data. but the one code line to obtain the address of the merged cells is very enticing to me.....Maybe the blank line/least error approach might actually be the most prudent despite the increased number of code lines.

Do you guys now understand what I am trying to achieve? Do you still prefer to demark a grouping with a blank row and blank column?

Maybe 'cause I have not such good sight on your project but after reading your description
I believe it should be achieved without mergings cells in particular if a blank row is left between two of what you call groups …
As a reminder there is a grouping feature within Excel.

For a clear sight then maybe later with a raw worksheet as it is before
and accordingly its result worksheet as it must be after using the VBA procedure …

But first the question is what is exactlly the purpose of using Excel according to what it was designed for ?
As obviously all columns purpose must be known, Excel is not a guessing challenge product but just a spreadsheet application
where guessing as no place so if the worksheet design is well done with warming some neurons
so no need to over complicate its use neither the users work life.

And far above all the worst idea is to use Excel with big data as a database software, just a waste of time …
Using Excel to just store data without needing its specific calculation and other features has no sense.
In particular in a pro context as Excel is inconstant, unsafe, not secure, 100 times slower than a database software, …
the reason why it needs a serious backup strategy more than any other pro application.

In particular cases the entry can be reduced to a specific worksheet form, easy just protecting it.
Some prefers to use an UserForm which can be a mess, a source of issues …

And if each column is well defined according to its content - easy with a clear enough header - there is an Excel / VBA feature
which manages the entry like a 'vertical' UserForm needing just a single codeline ! Excel basics making the life easier …

Good Morning Marc!
It is raining here. Birds must be hunkered down instead of singing for a mate...No sounds but rain hitting
the sunroom glass panels, and a few sleepy cats snoring on soft pillows.

I thank you for your reply.

Here is the essence of what I heard from your last letter:
* Excel is NOT a very good use for a database.
* guessing is not part of the thinking and use of excel.

Fundamentally I really understand where you are coming from. I, too, spend a great deal of time thinking about
what I am trying to accomplish and how. Data organization is very important to me as it is to you.

I suspect I am using excel and VBA in a very different manner than most people. I have persisted in continuing
this conversation because I have doubts that my approach is served well by excel and VBA. You have certainly
offered me many aspects to consider in my quest to find an effective solution.

I am basically using excel as merely visual sustained memory in the process of inputting data. I am not
using it for its ability to readily calculate.

I am using VBA because of its object oriented characteristic which is easily coupled to the extraction
of data from a worksheet.

I am visually identifying my input data so that multiple data can be quickly concurrently assessed and in a spatially
dense manner. Error Patterns can thus be visually detected in a manner that might be superior to other input mechanisms
required for database data entry. Complex errors, errors which are associated with relationships of multiple data,
can be more easily detected visually than by code which usually verifies only that a SINGLE data entry has certain
required characteristics or values.

In my case, data is transcribed from a worksheet into code objects. Calculations are made using the objects
and data output onto worksheets in a manner that electricians (in my case) can visually understand. there is no interest
in saving the data for the future as a bank might want to do in journaling transactions. The goal is to produce a design
that an electrician will use to translate into physical objects. Designs ar then forgotten and not of use. Electricians
then move on to the next job.

I really like your idea of using a database to store NEC code(old correspondence). That makes damn good sense to me.
The national code is used as the basis for every design. It changes infrequently over the course of several years
(current code in most municipalities is based on 2020 code) despite this year being 2023. A database is perfect for
that use.

I think objects are a better approach to storing data in my application which is a one shot effort for each design.

Thank you for your thoughts and the valuable time you spend on conveying them to me.

Guillaume (bil)