如何可靠获取跨列居中（Centered Across Selection）对应的单元格值？

要可靠获取跨列居中格式单元格的显示值，需要先定位该单元格所属的连续跨列居中单元格块，再在块内查找第一个非空单元格的值，而非单纯向左遍历。以下是完善的VBA实现：

Function GetCenteredAcrossValue(rng As Range) As Variant
    ' 仅处理单个单元格
    If rng.Cells.Count > 1 Then
        GetCenteredAcrossValue = CVErr(xlErrValue)
        Exit Function
    End If
    
    Dim ws As Worksheet
    Dim currentRow As Long
    Dim lastCol As Long
    Dim col As Long
    Dim blockStart As Long
    Dim blockEnd As Long
    Dim inBlock As Boolean
    
    Set ws = rng.Worksheet
    currentRow = rng.Row
    lastCol = ws.Cells(currentRow, ws.Columns.Count).End(xlToLeft).Column
    
    inBlock = False
    blockStart = 0
    blockEnd = 0
    
    ' 遍历当前行，分割跨列居中单元格块
    For col = 1 To lastCol + 1
        Dim isCenteredAcross As Boolean
        isCenteredAcross = (ws.Cells(currentRow, col).HorizontalAlignment = xlHAlignCenterAcrossSelection)
        
        If Not inBlock And isCenteredAcross Then
            inBlock = True
            blockStart = col
        ElseIf inBlock And Not isCenteredAcross Then
            blockEnd = col - 1
            inBlock = False
            
            ' 检查当前单元格是否在当前块内
            If rng.Column >= blockStart And rng.Column <= blockEnd Then
                Dim firstNonEmpty As Long
                On Error Resume Next
                firstNonEmpty = ws.Range(ws.Cells(currentRow, blockStart), ws.Cells(currentRow, blockEnd)) _
                    .Find(What:="*", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext).Column
                On Error GoTo 0
                
                GetCenteredAcrossValue = IIf(firstNonEmpty > 0, ws.Cells(currentRow, firstNonEmpty).Value, "")
                Exit Function
            End If
        End If
    Next col
    
    ' 处理行尾的跨列居中块
    If inBlock Then
        blockEnd = lastCol
        If rng.Column >= blockStart And rng.Column <= blockEnd Then
            Dim firstNonEmptyLast As Long
            On Error Resume Next
            firstNonEmptyLast = ws.Range(ws.Cells(currentRow, blockStart), ws.Cells(currentRow, blockEnd)) _
                .Find(What:="*", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext).Column
            On Error GoTo 0
            
            GetCenteredAcrossValue = IIf(firstNonEmptyLast > 0, ws.Cells(currentRow, firstNonEmptyLast).Value, "")
            Exit Function
        End If
    End If
    
    ' 非跨列居中单元格，返回自身值
    GetCenteredAcrossValue = rng.Value
End Function

代码说明 #

• 先校验输入为单个单元格，避免批量处理出错。
• 遍历当前行，将连续的跨列居中单元格划分为独立块，解决「同一行存在多个独立跨列居中区域」的问题。
• 在目标单元格所属的块内，从左到右查找第一个非空单元格并返回其值；若块内无有效值，返回空字符串。
• 若目标单元格未设置跨列居中，直接返回单元格自身的值。

对比原方法的优势 #

原方法单纯向左遍历查找非空单元格，会错误跨区域取值（比如将A1的值返回给D1）。本方法通过划分独立的跨列居中块，严格限定取值范围，完全匹配Excel自身的显示逻辑。

内容的提问来源于stack exchange，提问作者MonroeGA