Excel VBA problems with combined cells

Question

I am writing a Excel VBA program to generate text. I am using IF statements to find out if some specific text is in in a cell and concatenate a string based on that.

I have a problem with combined cells:

IF Cells(1,1) Like "A" and Cells(1,2) Like "---" Then
concatenate something
end if

The above works

IF Cells(2,1) Like "B" and Cells(2,2) Like "---" Then
concatenate something
end if

This does not work, because the Cell(2,2) is empty according to MsgBox (Cells(2, 2).Value) instead of the "---".

Is it possible to get the code to work, without splitting the cells again?

I would like to go row by row concatenating text, the VBA code should recognize the cellvalue one cell right to "B" as "---".

To get value of the "Merged" cells, they need to referenced with the first cell address amongst them. So, When cells `Cells(1,2)` ; `Cells(2,2)` and `Cells(3,2)` are merged then value in them could be taken using `Cells(1,2)` and not `Cells(2,2)` or `Cells(3,2)` as they lost their identity after merging with `Cells(1,2)` — Naresh, Jul 20 '21 at 14:00
DUPLICATE Question.. Use `Range("B3").MergeArea.Cells(1,1).Value` ... Refer comments on [this answer on SO](https://stackoverflow.com/a/9408817/9808063) — Naresh, Jul 20 '21 at 14:08

Chronocidal · Answer 1 · 2021-07-20T14:22:35.030

2

Cells(1,1).Value is "A". Cells(1,2).Value is "---".
Cells(2,1).Value is empty. Cells(3,2).Value is empty.
Cells(3,1).Value is "B". Cells(3,2).Value is empty.

Just because you merge the cells above, doesn't change that the cell is in Row 3. Only the top-left cell in a Merged Range has a value. (Although, there are ways around that.)

You can check if a cell is merged using Range.MergeCells. You can get the value of a Merged Cell using Range.MergeArea

For example:
Cells(1,1).MergeArea.Cells(1,1) is "A". Cells(2,2).MergeArea.Cells(1,1) is "---". Cells(1,1).MergeCells is True.
Cells(2,1).MergeArea.Cells(1,1) is "A". Cells(2,2).MergeArea.Cells(1,1) is "---". Cells(2,1).MergeCells is True.
Cells(3,1).MergeArea.Cells(1,1) is "B". Cells(3,2).MergeArea.Cells(1,1) is "---". Cells(3,1).MergeCells is False.

If you are using a loop to run through the cells, consider using a While or Do instead of a For, since you can increment by different numbers for each iteration:

Dim rowCurrent AS Long
rowCurrent = 1
While Cells(rowCurrent,1).Value <> ""
    If Cells(rowCurrent,2).MergeArea.Cells(1,1).Value = "---" Then
        Select Case Cells(rowCurrent,1).MergeArea.Cells(1,1).Value
            Case "A"
                'concatenate something
            Case "B"
                'concatenate something else
            Case "C"
                'concatenate a third thing
            Case Else
                'throw an error?
        End Select
    End If
    'Skip the rest of the merged cell
    rowCurrent = rowCurrent + Cells(rowCurrent,1).MergeArea.Rows.Count
Wend

edited Jul 20 '21 at 14:22

answered Jul 20 '21 at 14:10

Chronocidal

6,827
1
12
26

https://stackoverflow.com/a/9408817/9808063 – Naresh Jul 20 '21 at 14:11
1

@Naresh ? "Centre Across Selection" is not available in Vertical. The accepted answer on that question does not apply to this question, so (per StackOverflow guidelines) it is not a Duplicate, even if some of the comments *might*. – Chronocidal Jul 20 '21 at 14:15
Yes.. Agreed .. The link is just for reference to the comments on that answer. by [Josh](https://stackoverflow.com/users/341536/josh-brown) – Naresh Jul 20 '21 at 14:15
1

This is a clever way to loop down both col **A** and **B** *(+1)* – Gary's Student Jul 20 '21 at 14:16
1

Just because the accepted answer isn't the best in the linked thread, this is a dupe, as that thread (@Naresh's link, or better, https://stackoverflow.com/a/20365398/9245853) definitely proposes using `MergeArea.Cells(1, 1)`... still a valid answer here. This thread can serve as a pointer to that thread (which, with 133k views, is probably a good dupe target). – BigBen Jul 20 '21 at 14:18
This loop is a better idea.. We can also determine if a cell is in merged area like this [SO Answer](https://stackoverflow.com/a/22076713/9808063) – Naresh Jul 20 '21 at 14:28

Excel VBA problems with combined cells

1 Answers1