Merged cells in Excel are known to cause inaccuracy issues with lookups, matching etc and can be a problem area for users analysing data or calculating against cells where the value is in the middle of a merged area of cells.
Datanology have recently completed a project related to Amazon Postage and storage calculations where a matrix was provided to allow a manual paper based lookup of the likely cost to store and post out any given item by weight, size and storage time. This matrix contained large areas of merged cells in order to be easy to read. However, if merged cells have their actual text value stored in the top left cell of the merged area so if you are looking for an intersection using X,Y references or cell references, if an area is merged and you are not looking at the top left cell, the value returned is going to be 0.
This code example can be adapted to your needs, but with this example we are looking at cell D411 which we are assuming is in a merged area and is NOT the foremost top left cell. This code will find the top most left cell in the merged area that your cell belongs to and return the correct value represented by that merged cells area.
We’d be happy to assist on any Excel or data project you are currently working on or looking to achieve. Leave a message below or contact us through the form. We’ll get straight back to you.
c = ActiveSheet.Range("D411").MergeArea.Cells(1, 1).Value