将Excel筛选结果获取到VBA数组中 [英] Get Excel filter results into VBA array

问题描述

我有一个VBA子程序，用来过滤在第4列有文字SV-PCS7的记录。我怎样才能把这些结果存入数组？ （）
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim ro As Integer
Set xlbook = GetObject（ C：\07509\04-LB-06 MX-sv.xlsx）
设置xlsheet = xlbook.Sheets（04-LB-06 MX）
使用xlsheet

.AutoFilterMode = False
.Range（blockn）。AutoFilter字段：= 1，Criteria1：=SV-PCS7

结束
$ b $如果你想避免复杂的循环，那么你可以使用一个简单的循环来实现这个循环。如果你想避免复杂的循环（优秀）的解决方案，您可以使用临时工作表复制可见的行首先。

pre $子测试（$）
Dim src As Range，m As Variant，sh As Worksheet

Set src = Sheet1.Range（c3）。CurrentRegion.SpecialCells（xlCellTypeVisible）
Set sh = Worksheets.Add

src.Copy sh.Range（a1）
m = sh.Range（a1）。CurrentRegion
Application.DisplayAlerts = False
sh.Delete
Application。 DisplayAlerts = True
Debug.Print UBound（m）
End Sub

I have a VBA subroutine which filters records that have the text "SV-PCS7" in column 4. How can I get these results into an array?

Sub FilterTo1Criteria()
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim ro As Integer
Set xlbook = GetObject("C:\07509\04-LB-06 MX-sv.xlsx")
Set xlsheet = xlbook.Sheets("04-LB-06 MX")
   With xlsheet

       .AutoFilterMode = False
       .Range("blockn").AutoFilter Field:=1, Criteria1:="SV-PCS7"

   End With

End Sub

解决方案

If you want to avoid the complex looping of Jeeped's (excellent) solution, you can use a temp sheet to copy the visible rows first.

Sub test()
    Dim src As Range, m As Variant, sh As Worksheet

    Set src = Sheet1.Range("c3").CurrentRegion.SpecialCells(xlCellTypeVisible)
    Set sh = Worksheets.Add

    src.Copy sh.Range("a1")
    m = sh.Range("a1").CurrentRegion
    Application.DisplayAlerts = False
    sh.Delete
    Application.DisplayAlerts = True
    Debug.Print UBound(m)
End Sub

这篇关于将Excel筛选结果获取到VBA数组中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！