0

Apologies if this question has already been asked somewhere but I couldn't find a definitive answer regarding if it's possible without VBA.

I need a function to be able to know whether a cell is part of a merged groups of cells or not. I have tried =cell() and none of the properties return anything that might give a clue that it is merged (the cells are all identical apart from the left-most cell in the merged group of cells obviously contains a value).

I have found a very good VBA function that quickly tests this How to get find if a cell in excel is merged? If the cell mrged how to read the value?, and it works great! But unfortunately I need this document to work in Google Sheets, and therefore cannot use VBA or macros.

Does anyone know if it is possible to test if a cell is merged in a non-VBA way. Or if it is possible to use this function in Google Sheets somehow?

Function IsMerged(rCell As Range) As Boolean
' Returns true if referenced cell is Merged        
          IsMerged = rCell.MergeCells        
End Function

(Original code submitted by Vinny Roe - thanks!)

Community
  • 1
  • 1
cosmo
  • 7
  • 1
  • 8
  • See [How do I know if spreadsheet cells are merged using google apps script?](http://stackoverflow.com/q/11025543/1677912). There is no existing spreadsheet function for that in Google Sheets, and there's no API provided that will identify merged cells. You're left with some hacks that will do the job under the right circumstances. – Mogsdad Jan 23 '16 at 03:45

1 Answers1

0

Quick answer - no, you probably can't do this without macros. And even if you could, you should avoid merging cells on formula sheets, because as you've already gathered, it can be a big inconvenience.

Long answer - you may be able to do this, assuming ALL of your cells have data in them [except the non-top-left portion of merged cells]. This would more or less just consist of you checking whether a cell was blank - and if it was, then what is the cell either directly above it or directly to the left of it which contains data.

Without knowing what you want this information for, it's hard to advise further, although really the point is that if you're looking at working with merged cells, don't.

Grade 'Eh' Bacon
  • 3,773
  • 4
  • 24
  • 46
  • Hi, thanks for the response! Yes that was the only option I had thought of also. However, I'm using it to create a schedule, where each merged block represents an activity in the schedule. It's designed so the merged blocks can be moved around frequently. Therefore once a merged cell has moved, the cells that were in its previous place, will unfortunately be blank again. So it wouldn't work to have the other cells populated with values, as once you move the schedule activity, there will be 'empty' cells that are actually empty when they need values. I agree RE merged cells though - hate them! – cosmo Jan 14 '16 at 14:50
  • @cosmo I recommend that you simply eliminate gridlines from your sheet [View Ribbon > Show Section > Gridlines checkbox], and create larger borders around your dates. Or, simply make the cells themselves larger and use a single cell to represent the day. Or even to use another calendar method. – Grade 'Eh' Bacon Jan 14 '16 at 15:24