Working with merge and center cells in Excel using VBA

Question

If I take a range over merge and centered cells in excel, are each of them addressed as single cells or the group of cells constituting them? For example, if the cells A1 to A10 are merged and I do a

Worksheets(1).Range("A5")

Would this return the range of a single cell among those constituting the merged cell or a range consisting the merged cell itself? And what value would it contain if I get its value?

In other words, how would you represent range over merged cells in excel VBA?

Also, how can I get the range of the non merged cells adjacent to the length of this merged cell?

Answer 1

In other words, how would you represent range over merged cells in excel VBA?

By addressing it with the top left cell. Ex: "A1" in this case.

When in doubt, check it yourself first. If still in doubt, search google or whatever search engine you use. Still if something is unclear, ask :)

Would this return the range of a single cell among those constituting the merged cell or a range consisting the merged cell itself? And what value would it contain if I get its value?

It would return a single cell A5 which doesn't have anything in it because when you merge cells the data from the top left cell is kept and rest discarded. The reason why I say discarded is that if you now unmerge the cells, you will not get your values back.

Best way to check:

Let's say A1 to A10 had 1,2,3..10. Merge them. The cell will now have only 1

Try this in Immediate window

?Range("A5").Value

You will get nothing. Similarly if you want to write to it, you cannot use Range("A5").Value = "Blah". You have to address it with the top left cell. For example

Range("A1").Value = "Blah"