使用AutoFilter过滤固定数量的数据 [英] Filter a fixed amount of data using AutoFilter

问题描述

我只想过滤固定数量的数据。我正在实现这个WebPage上发布的代码，它的工作原理很好，但它会过滤包含Item1和Approved的所有数据。例如，我想做的是仅使用给定条件过滤5行数据，而不是过滤所有。

  Private Sub CommandButton1_Click（）
 Dim OriginalData作为工作表，FilteredData作为工作表
 
设置OriginalData = ThisWorkbook.Worksheets（Sheet1）
设置FilteredData = ThisWorkbook.Worksheets（Sheet2 ）
 
与OriginalData 
如果.AutoFilterMode然后.AutoFilterMode = False 
带.Cells（2，1）.CurrentRegion 
 .AutoFilter字段：= 1，条件1： =Item1
 
 .AutoFilter字段：= 2，Criteria1：=已批准
使用.Resize（.Rows.Count  -  1，Columns.Count）.Offset（1，0 ）
如果CBool​​（Application.Subtotal（103，.Cells））然后
 .Copy目标：= _ 
 FilteredData.Cells（Rows.Count，A）。End（xlUp） .Offset（1，0）
 End If 
 End With 
 End 
如果.AutoFilterMode然后.AutoFilterMode = False 
结束
 End Sub 
  

解决方案

如果要过滤前5行，则 Range.Resize属性可以应用于。CurrentRegion 之前的 Dim OriginalData作为工作表，FilteredData作为工作表

设置OriginalData = ThisWorkbook.Worksheets（Sheet1）
设置FilteredData = ThisWorkbook.Worksheets（Sheet2）

与OriginalData
如果.AutoFilterMode然后.AutoFilterMode = False
带.Cells（1，1）.CurrentRegion'al l从A1
'发出的单元格大小调整为6行（5个数据+ 1个标头）
带.Resize（6，.Columns.Count）
.AutoFilter字段：= 1，Criteria1 ：=Item1
.AutoFilter字段：= 2，Criteria1：=已批准
带.Resize（.Rows.Count - 1，.Columns.Count）.Offset（1，0）
如果CBool​​（Application.Subtotal（103，.Cells））然后
.Copy目标：= _
FilteredData.Cells（Rows.Count，A）。End（xlUp）.Offset （1，0）
结束如果
结束
结束
结束
如果.AutoFilterMode然后.AutoFilterMode = False
结束
End Sub

请注意，如果您使用 F8 的数据实际上将被过滤，但只会从前5个（可见或不可见）行中复制过滤数据。

如果要复制前5个已过滤的行，则需要处理不连续的可见 Range.Areas属性和一些数学。

  Private Sub CommandButton2_Click（） 
 Dim a As Long，aa As Long 
 Dim OriginalData As Worksheet，FilteredData As Worksheet 
 
 Set OriginalData = ThisWorkbook.Worksheets（Sheet1）
 Set FilteredData = ThisWorkbook.Worksheets（Sheet2）
 aa = 5 
 
与OriginalData 
如果.AutoFilterMode然后.AutoFilterMode = False 
带.Cells（1，1）。 CurrentRegion'从A1 
 .AutoFilter字段中排除的所有单元格：= 1，Criteria1：=Item1
 .AutoFilter字段：= 2，Criteria1：=已批准
 With .Resize .Rows.Count  -  1，.Columns.Count）.Offset（1，0）
如果CBool​​（Application.Subtotal（103，.Cells））然后
 with .SpecialCells（xlCellTypeVisible）
 For a = 1 To .Areas.Count 
 .Areas（a）.Resize（Application.Min（aa，.Areas（a）.Rows.Count），.Columns .Count目标：= _ 
 FilteredData.Cells（Rows.Count，A）。End（xlUp）.Offset（1，0）
 aa = aa  -  Application.Min（aa ，.Areas（a）.Rows.Count）
如果aa< 1然后退出
下一个
结束
结束如果
结束
结束
如果.AutoFilterMode然后.AutoFilterMode = False 
结束
 End Sub 
  

这两个都可以在我有限的测试中生存。如果遇到我没有考虑的问题，请发回。

I would like to filter only a fixed amount of data. I am implementing a code posted on this WebPage and it works perfectly, but it filters all the data that contain "Item1" and "Approved". For example, what I would like to do is to filter only 5 rows of data with the given conditions instead of filtering all.

Private Sub CommandButton1_Click()
    Dim OriginalData As Worksheet, FilteredData As Worksheet

    Set OriginalData = ThisWorkbook.Worksheets("Sheet1")
    Set FilteredData = ThisWorkbook.Worksheets("Sheet2")

    With OriginalData
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Cells(2, 1).CurrentRegion
             .AutoFilter field:=1, Criteria1:="Item1"

             .AutoFilter field:=2, Criteria1:="Approved"
             With .Resize(.Rows.Count - 1, Columns.Count).Offset(1, 0)
                If CBool(Application.Subtotal(103, .Cells)) Then
                    .Copy Destination:= _
                        FilteredData.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
                End If
              End With
         End With
         If .AutoFilterMode Then .AutoFilterMode = False
     End With
End Sub

解决方案

If you want to filter the first 5 rows then the Range.Resize property can be applied to the .CurrentRegion before the .AutoFilter method.

Private Sub CommandButton1_Click()
    Dim OriginalData As Worksheet, FilteredData As Worksheet

    Set OriginalData = ThisWorkbook.Worksheets("Sheet1")
    Set FilteredData = ThisWorkbook.Worksheets("Sheet2")

    With OriginalData
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Cells(1, 1).CurrentRegion   'all cells radiating out from A1
            'resize to 6 rows total (5 data + 1 header)
            With .Resize(6, .Columns.Count)
                .AutoFilter field:=1, Criteria1:="Item1"
                .AutoFilter field:=2, Criteria1:="Approved"
                With .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0)
                    If CBool(Application.Subtotal(103, .Cells)) Then
                        .Copy Destination:= _
                            FilteredData.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
                    End If
                End With
            End With
        End With
        If .AutoFilterMode Then .AutoFilterMode = False
    End With
End Sub

Note that if you step through the code with F8 all of the data will actually be filtered but only filtered data from the first 5 (visible or not visible) rows will be copied.

If you want to copy the first 5 filtered rows then you need to deal with the non-contiguous visible Range.Areas property and some maths.

Private Sub CommandButton2_Click()
    Dim a As Long, aa As Long
    Dim OriginalData As Worksheet, FilteredData As Worksheet

    Set OriginalData = ThisWorkbook.Worksheets("Sheet1")
    Set FilteredData = ThisWorkbook.Worksheets("Sheet2")
    aa = 5

    With OriginalData
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Cells(1, 1).CurrentRegion   'all cells radiating out from A1
            .AutoFilter Field:=1, Criteria1:="Item1"
            .AutoFilter Field:=2, Criteria1:="Approved"
            With .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0)
                If CBool(Application.Subtotal(103, .Cells)) Then
                    With .SpecialCells(xlCellTypeVisible)
                        For a = 1 To .Areas.Count
                            .Areas(a).Resize(Application.Min(aa, .Areas(a).Rows.Count), .Columns.Count).Copy Destination:= _
                                FilteredData.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
                            aa = aa - Application.Min(aa, .Areas(a).Rows.Count)
                            If aa < 1 Then Exit For
                        Next a
                    End With
                End If
            End With
        End With
        If .AutoFilterMode Then .AutoFilterMode = False
    End With
End Sub

Both of those survive my limited testing. Post back if you run into problems I have not accounted for.

这篇关于使用AutoFilter过滤固定数量的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！