Sum up column values in a merged cell

Question

I have a very specific case of automatically summing up values in a merged cell that I can't manage to loop correctly:

There are nine columns in my table, the first five Range("A:E") include information about the Client and the Date. In the other four columns there are a bunch of variables, the first two (column F and G) are put in manually, the final two are supposed to be calculated automatically (H is calculated using an Excel formula, I will be calculated using VBA).

Whenever a new variable is put into column F without a change in the Client and Date Section Range("A:E") of that very row, I want the cells of Range(A:E) to merge with the ones above. In that case I also would like the final column I to merge for the specific number of rows. The code for that merging process basically works fine, it is the following:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim tmp As Range
Dim i As Integer

For Each tmp In Range("F4:F1000")
If tmp.Value <> "" Then
If tmp.Offset(0, -1).Value = "" Then
Range(tmp.Offset(0, -1), tmp.Offset(-1, -1)).MergeCells = True
Range(tmp.Offset(0, -2), tmp.Offset(-1, -2)).MergeCells = True
Range(tmp.Offset(0, -3), tmp.Offset(-1, -3)).MergeCells = True
Range(tmp.Offset(0, -4), tmp.Offset(-1, -4)).MergeCells = True
Range(tmp.Offset(0, -5), tmp.Offset(-1, -5)).MergeCells = True
Range(tmp.Offset(0, 3), tmp.Offset(-1, 3)).MergeCells = True
Else
tmp.Offset(0, -1).MergeCells = False
tmp.Offset(0, -2).MergeCells = False
tmp.Offset(0, -3).MergeCells = False
tmp.Offset(0, -4).MergeCells = False
tmp.Offset(0, -5).MergeCells = False
tmp.Offset(0, 3).MergeCells = False
End If
End If
Next tmp

End Sub

The tricky part is the following: I want the merged area of the final column I automatically to sum the values that are listed in the same rows in column H like seen in the picture below (the picture only shows the columns D to H)

My idea was to put the following code before the Else :

i = (tmp.Offset(0, -1).MergeArea.Rows.Count - 1) * (-1)
tmp.Offset(i, 3) = Application.Sum(Range(tmp.Offset(i, 2), tmp.Offset(0, 2)))

Unfortunately, this very code snippet results in an infinite, chaotic merging and de-merging process along the single rows when more than three rows are being merged (although this process does NOT appear when I run the whole sub without the additional code snippet).

Does anyone have an idea to solve that issue?

Also, I would be thankful for any suggestions on how to extend the code for the case that I delete a cell in column F. I know VBA gets terribly complicated through the use of cell merging, I will eventually have to stick to repeated values in each single row. Still, if anyone likes the challenge I'd welcome the help on the subject!

Look here for how to test if it is merged. Then use the merged address to dictate the range.value, else just use the cell. — Scott Craner

Scott Craner Scott Craner · Accepted Answer · 2015-10-10T15:57:11

Just use

range(range("A3").MergeArea.Cells(1,1).address).value

It will work whether the cell is merged or not. Change "A3" to the cell you are needing.

In short is finding the upper left cell in the merged area which is where the values are being stored. If there is only one cell then it returns that cell value.

Sum up column values in a merged cell

1 Answers