Excel中LastCell函数在溢出区域无法获取最后单元格问题求助

问题根源 #

你原来的LastCell函数依赖Cells.Find方法查找内容，但这个方法仅识别直接输入/手动编辑的单元格内容。动态数组（比如SEQUENCE(8)）溢出生成的单元格（A2:A8）并非独立存储内容，它们属于A1单元格公式的溢出范围，因此Find无法检测到这些单元格，导致返回A1而非A8。

之前报表能正常运行，是因为当时未使用动态数组溢出功能，所有内容都是传统的手动输入或非溢出公式生成，Find可以正确识别最后单元格。

修改后的函数代码 #

Public Function LastCell(wrkSht As Worksheet) As Range
    Dim lLastCol As Long, lLastRow As Long
    Dim spillCell As Range
    On Error Resume Next
        ' 获取传统非溢出单元格的最后行列
        With wrkSht
            lLastCol = .Cells.Find("*", , , xlWhole, xlByColumns, xlPrevious).Column
            lLastRow = .Cells.Find("*", , , xlWhole, xlByRows, xlPrevious).Row
        End With
        ' 处理空白工作表默认值
        If lLastCol = 0 Then lLastCol = 1
        If lLastRow = 0 Then lLastRow = 1
        
        ' 遍历所有公式单元格，识别溢出区域并更新最大行列
        For Each spillCell In wrkSht.Cells.SpecialCells(xlCellTypeFormulas)
            If spillCell.HasSpill Then
                With spillCell.SpillingToRange
                    ' 更新最大行号
                    If .Row + .Rows.Count - 1 > lLastRow Then
                        lLastRow = .Row + .Rows.Count - 1
                    End If
                    ' 更新最大列号
                    If .Column + .Columns.Count - 1 > lLastCol Then
                        lLastCol = .Column + .Columns.Count - 1
                    End If
                End With
            End If
        Next spillCell
        
        ' 定位最终的最后单元格
        Set LastCell = wrkSht.Cells(lLastRow, lLastCol)
    On Error GoTo 0
End Function

代码说明 #

• 保留了原函数对传统单元格的识别逻辑，确保兼容旧数据场景
• 新增遍历所有公式单元格，通过HasSpill判断是否为动态数组溢出公式
• 利用SpillingToRange获取完整溢出区域，计算该区域的最后行/列，与传统结果取最大值
• 仅支持Excel 365/2021及以上支持动态数组的版本

内容的提问来源于stack exchange，提问作者Darren Bartrup-Cook