☰

将过滤后的数据移动到变体变量数组中

tag-icon • tag-icon microsoft-excel vba array
将过滤后的数据移动到变体变量数组中

我目前正在尝试自动执行一项手动任务。我已经过滤了我的数据，如果我将可见范围复制并粘贴到另一张表中，这就可以正常工作。我正在尝试将可见数据写入变量变量数组。我想将其保存在内存中，然后将其写入一个范围。每次我这样做时，我都会得到 #NAs。感谢您的想法和建议。

Sub Derivatives()
    Application.ScreenUpdating = False
    Application.Calculation = xlAutomatic
    Application.DisplayAlerts = False

    Dim wb As Workbook, ws As Worksheet
    Dim lRow As Long
    Dim lRow1 As Long
    Dim Fields() As Variant
    Dim wb1 As Workbook

    Set wb = ThisWorkbook
    Set ws = wb.ActiveSheet

    lRow = Range("A" & Rows.Count).End(xlUp).Row

    With ws
        Range("$G$9:$I$22479").AutoFilter Field:=3, Criteria1:="TRUE"
        lRow1 = Range("G" & Rows.Count).End(xlUp).Row
        Fields = ws.Range("G9").CurrentRegion.SpecialCells(xlCellTypeVisible)
        ActiveSheet.ShowAllData
        Range("K9").CurrentRegion = Fields
    End With

    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic
    Application.DisplayAlerts = True
End Sub

相关内容