比如我写了一个 Excel VBA 来获取当前合并单元格的首行和尾行行号,我希望把行号存到 Quicker 的变量里,方便后续进行其他操作,据说用函数function可以,但不会用诶,可以教一下嘛
▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰
Sub 获取合并单元格首尾行号_兼容版()
Dim rng As Range
Dim firstCell As Range
Dim mergeRng As Range
Dim firstRow As Long, lastRow As Long
' 1. 基础校验
If TypeName(Selection) <> "Range" Then
MsgBox "请选中单元格!", vbExclamation
Exit Sub
End If
Set rng = Selection
' 2. 取选中区域的【第一个单元格】,再获取它的合并区域(核心修复!)
Set firstCell = rng.Cells(1)
' 3. 判断是否为合并单元格
If firstCell.MergeCells Then
Set mergeRng = firstCell.MergeArea ' 获取完整合并区域
firstRow = mergeRng.Row ' 合并单元格首行
lastRow = mergeRng.Row + mergeRng.Rows.Count - 1 ' 合并单元格尾行
' 仅弹窗1次!
MsgBox "合并单元格跨越:" & mergeRng.Address & vbCrLf & _
"首行行号:" & firstRow & vbCrLf & _
"尾行行号:" & lastRow, vbInformation
Else
MsgBox "当前选中单元格不是合并单元格!", vbExclamation
End If
' 释放对象
Set rng = Nothing
Set firstCell = Nothing
Set mergeRng = Nothing
End Sub
▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰▰
' 函数1:获取合并单元格的【首行行号】
' 使用方法:单元格输入 =GetMergeFirstRow(选中单元格)
' ==============================================
Function GetMergeFirstRow(rng As Range) As Long
On Error Resume Next
' 取第一个单元格,避免多单元格报错
Set rng = rng.Cells(1)
' 判断是否为合并单元格
If rng.MergeCells Then
GetMergeFirstRow = rng.MergeArea.Row
Else
GetMergeFirstRow = rng.Row ' 非合并单元格返回自身行号
End If
On Error GoTo 0
End Function
' ==============================================
' 函数2:获取合并单元格的【尾行行号】
' 使用方法:单元格输入 =GetMergeLastRow(选中单元格)
' ==============================================
Function GetMergeLastRow(rng As Range) As Long
On Error Resume Next
Set rng = rng.Cells(1)
If rng.MergeCells Then
' 计算合并区域最后一行
GetMergeLastRow = rng.MergeArea.Row + rng.MergeArea.Rows.Count - 1
Else
GetMergeLastRow = rng.Row ' 非合并单元格返回自身行号
End If
On Error GoTo 0
End Function