在VBA中捕获"Data range is too complex“错误

Question

在我正在处理的excel宏中，我将筛选过的数据复制到新的工作表中，以删除隐藏的行。这允许我在用户过滤的数据上运行更复杂的公式。对于经过复杂过滤的大数据集，复制操作不再只接受过滤的数据，而是复制所有数据。

手动复制过程时，当我试图复制筛选的数据时，Excel给出“数据范围太复杂”的错误。这很容易通过排序和过滤来通过，但是我不知道如何在VBA中捕获这个错误。我想知道复制操作是否正常工作(并允许宏继续)，或者选择是否太复杂(并停止宏并提醒用户先尝试排序)。

你知道我该怎么做吗？任何帮助都将不胜感激。

相关代码如下：

    Select Case Range("GDT_Filtered").Value
    Case "Filtered Data"
        Worksheets.Add after:=Worksheets(Worksheets.Count)
        Set wRaw = ActiveSheet
        Sheets("Raw Data").Select
        lastRow = Range("A1").End(xlDown).Row
        Range("A1:S" & lastRow).Copy Destination:=wRaw.Range("A1")

    Case "All Data"
        Set wRaw = Sheets("Raw Data")
        On Error Resume Next
        wRaw.ShowAllData
        On Error GoTo 0
    End Select

谢谢!

相同的

Answer 1

好吧，我已经想出了一个可管理的变通方法。

假设原始数据存储在sheet wOne中，并将筛选后的数据复制到sheet wTwo中，下面的代码将通过检查筛选和每个工作表中的行数来查找错误：

' Excel can't copy too many discontinuous sections. If it happens it won't throw
' an error but will copy ALL data instead of just filtered data. This checks that
' such an error has not occurred.
If wOne.AutoFilter.FilterMode = True Then
    If wOne.range("A1").End(xlDown).Row = wTwo.Range("A1").End(xlDown).Row Then
        MsgBox "Filtering has produced too many discontinuous selections. " & _ 
                "Try removing the filters, sorting data by the variables " & _ 
                "you wish to filter with, reapplying the filters, and " & _
                "trying again. Good luck!"
        GoTo 1
    End If
End If

Answer 2

使用OnError

sub 

   On Error Goto 1

   do the copy, if it fails, code will jump to 1.

   On Error Goto 0 'zero means you are not doing any treatment for further errors

   continue the code

   exit sub 'you don't want the code to do error treatment below if there's no error

   1 Do what you need if error occurs

end sub