Screenshot 2024-10-02 170207.png
Dear All,
If you have a VBA or other method which can detect the merged cells in the column j as per the image attached and insert the sum formula to those merged cells. The user will run the code for individual sheet. The code needs to detect the active sheet.
I tried to code but there are some syntax error.
Here is the code:
![]()
Sub MergedCells() Dim cell As Range Dim mergedRange As Range Dim formulaText As String Dim lastRowInJ As Long Dim firstRow As Long, lastRow As Long ' Find the last row in column J, starting from row 3 lastRowInJ = ActiveSheet.Cells(ActiveSheet.Rows.Count, "J").End(xlUp).Row ' Loop through each cell in column J from row 3 downwards For Each cell In ActiveSheet.Range("J3:J" & lastRowInJ) ' Check if the cell is part of a merged range and is the first cell of that range If cell.MergeCells And cell.Address = cell.MergeArea.Cells(1, 1).Address Then ' Get the merged range Set mergedRange = cell.MergeArea ' Get the first and last row of the merged range firstRow = mergedRange.Row lastRow = firstRow + mergedRange.Rows.Count - 1 ' Create the SUM formula for the corresponding range in column I formulaText = "=SUM(I" & firstRow & ":I" & lastRow & ")" ' Place the formula in the first cell of the merged range mergedRange.Cells(1, 1).Formula = formulaText End If Next cell End Sub
Bookmarks