Excel VBA - If cells is part of merged cells pass the value?

Question

I'm having some trouble to prepare macro which would help me to pass the value to another cell if the specified cell is a part of merged cells.

As you can see, cells A1-A15 are merged, in B1 I've written =A1 in B2 I did =A2, so what I want to achieve is that whenever I assign somewhere cell which is part of merged cells(A1-A15) the 'test' value is passed so there is no difference if I write =A1 or =A15 or =A10

I would appreciate any help of advice.

Some options here https://stackoverflow.com/questions/6464265/how-to-keep-value-of-merged-cells-in-each-cell. Do the merged cells have to remain merged? Hideous things merged cells. — QHarr, Mar 16 '18 at 09:03

score 5 · Answer 1 · answered Mar 16 '18 at 09:03

5

You can detect if a Cell is part of a Merged Cell using If Range("A1").MergeCells = True.

Get the number of rows you have in your MergedArea using Range("A" & i).MergeArea.Rows.Count.

More explanation inside the code below.

Code

Option Explicit

Sub CheckifMergedCell()

Dim MergeRows As Long, i As Long

i = 1
While i < 100 ' 100 is just for example , change it later according to your needs

    If Range("A" & i).MergeCells = True Then
        MergeRows = Range("A" & i).MergeArea.Rows.Count ' number of merged cells
    Else ' not merged >> single row
        MergeRows = 1
    End If

    Range("B" & i).Resize(MergeRows, 1).Value = Range("A" & i).Value

    i = i + MergeRows
Wend

End Sub

answered Mar 16 '18 at 09:03

Shai Rado

33,032
6
29
51

1

Nice solution. I haven't looked at MergeArea before. – QHarr Mar 16 '18 at 09:06
2

@QHarr I have, unfortunately some managers use it in Excel since it looks nice to them, they are unaware of the numerous head-aches it gives us :) – Shai Rado Mar 16 '18 at 09:08
@ShaiRado that's nice, but I want to have such option that if A1-A15 are merged so if in any other cell I'll write =A2 or =A15 or =A5 it will pass the value from merged cells ? Is that possible ? – Sebastian Wdowiarz Mar 16 '18 at 09:16
@SebastianWdowiarz just run the Code above, why you want to use formulas ? you will get the same result – Shai Rado Mar 16 '18 at 09:17

score 2 · Answer 2 · answered Mar 16 '18 at 09:28

2

In B1,

=INDEX(A:A, MATCH("zzz", A$1:A1))

Fill or copy down.

answered Mar 16 '18 at 09:28

I'm trying to wrap my head around this formula, could you please explain how does this exactly work? – user628797 Jun 14 '18 at 12:28
It simply looks for the last cell in column A containing any text up to the current row in column B. – Jun 14 '18 at 12:33

score 0 · Answer 3 · answered Mar 16 '18 at 09:46

what I want to achieve is that whenever I assign somewhere cell which is part of merged cells(A1-A15) the 'test' value is passed so there is no difference if I write =A1 or =A15 or =A10

What you want to accomplish can't be done easily. You could do it with an VBA code that checks every single time you type something, but it's not worth it. The other answer you got here are worth it.

What you want to do is not possible because Excel works in a weird way. Let's say you have cells A1:A15 merged. The value is ALWAYS in first cell of merged area (in this case in A1). So when you reference a cell inside the merged area, it will have a 0 value (a blank cell) always, unless it is the first one.

So my advice, would be:

Use 1 of the other answers, because both are really helpful

If you insist in using normal formulas, then instead of typing =A1, try with absolute references, try =$A$1. If you click and drag, that formula will work for you to complete adjacent cells to merged area.

I insist, use 1 of the other answers.

Excel VBA - If cells is part of merged cells pass the value?

3 Answers3