excel - 以编程方式删除所有数组公式

Question

我有一个工作表在“E”列中包含一个数组，该数组被分组为单独的子类别，如下图所示。如何从 VBA 中的整个“E”列中删除数组，而不是使用 Find & Select > Go to special > Current Array > Delete 手动删除它？

非常感谢任何帮助！

标签： excelvba

Answer 1

删除 E 列中所有数组公式的简单解决方案是遍历列并检查每个单元格中的Range.HasArray 属性。

Option Explicit

Public Sub RemoveAllArrayFormulasInColumnE()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    Dim LastRow As Long
    LastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row
    
    Dim iRow As Long
    For iRow = 1 To LastRow
        If ws.Cells(iRow, "E").HasArray Then
            ws.Cells(iRow, "E").CurrentArray.ClearContents
        End If
    Next iRow
End Sub

为了让它更快，您可以在循环中跳转到您删除的当前数组的末尾。这样，您要检查的单元格就更少了。

Option Explicit

Public Sub RemoveAllArrayFormulasInColumnE_Quicker()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    Dim LastRow As Long
    LastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row
    
    Dim iRow As Long
    For iRow = 1 To LastRow
        If ws.Cells(iRow, "E").HasArray Then
            Dim JumpRow As Long
            JumpRow = iRow - 1 + ws.Cells(iRow, "E").CurrentArray.Rows.Count
            ws.Cells(iRow, "E").CurrentArray.ClearContents
            iRow = JumpRow
        End If
    Next iRow
End Sub