|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
RTRT,
代码如下:
- Sub AppendOrUpdateColumnMergeInfo()
- Dim selectedRange As Range
- Dim cell As Range
- Dim mergeCols As Long
- Dim originalValue As String
- Dim newValue As String
- Dim startPos As Integer
- Dim endPos As Integer
- Dim hasMergeInfo As Boolean
- Dim processedMerges As Object
- Set processedMerges = CreateObject("Scripting.Dictionary") ' 使用字典跟踪已处理的合并区域
- Set selectedRange = Selection
- 'Set selectedRange = ActiveSheet.Range("C6:DC20")
-
- For Each cell In selectedRange
- If cell.MergeCells And Not processedMerges.Exists(cell.MergeArea.Address) Then
- ' 获取合并单元格横跨的列数
- mergeCols = cell.MergeArea.Columns.Count
- processedMerges(cell.MergeArea.Address) = True ' 标记此合并区域为已处理
-
- With cell.MergeArea.Cells(1, 1)
- originalValue = .Value
- ' 检测是否已含列数信息
- startPos = InStr(originalValue, "(")
- endPos = InStr(startPos, originalValue, "w)")
- If startPos > 0 And endPos > 0 Then
- hasMergeInfo = True
- Else
- hasMergeInfo = False
- End If
-
- If hasMergeInfo Then
- ' 精确替换旧的列数信息
- newValue = Left(originalValue, startPos) & mergeCols & "w)" & Mid(originalValue, endPos + 2)
- Else
- ' 添加新的列数信息
- newValue = originalValue & " (" & mergeCols & "w)"
- End If
-
- .Value = newValue ' 更新单元格内容
- End With
- End If
- Next cell
- End Sub
复制代码
|
|