VBA 选择过滤范围内的特定行数

VBA to select specific number of rows on filtered range

提问by user2298601

I have a macro that filters a range, and I have a range of values which I want to represent the number of rows being selected after the filter is applied.

我有一个过滤一个范围的宏，我有一个值范围，我想表示应用过滤器后选择的行数。

I have most of the code sorted, im just getting stuck on selecting the visible rows only. EG. Sheet 1 contains variable numbers (1, 2, 3 ,4 etc) which I have labelled as NOC1.

我对大部分代码进行了排序，我只是卡在只选择可见行上。例如。第 1 页包含我标记为 NOC1 的可变数字（1、2、3、4 等）。

Now once the filter is applied it selects the correct number of rows, but also selects hidden cells. I just want it to select the visible cells only.

现在一旦应用过滤器，它就会选择正确的行数，但也会选择隐藏的单元格。我只想让它只选择可见的单元格。

Here is the code:

这是代码：

Set TopVisibleCell = Rstatus.Offset(1).Rows.SpecialCells(xlCellTypeVisible).Rows(1)
TopVisibleCell.Select
Selection.Resize(Selection.Rows.Count + NOC1 - 1, _
Selection.Columns.Count).Copy

Any help would be greatly appreciated.

任何帮助将不胜感激。

Thanks!

谢谢！

Edit:

编辑：

Please excuse my poor description, it seems I didnt express myself clearly. Please find link to Sample.xlsm which will hopefully shed some light on my problem.

请原谅我描述的不好，似乎我没有表达清楚。请找到指向 Sample.xlsm 的链接，它有望对我的问题有所了解。

Link : Sample Workbook

链接：示例工作簿

Thanks for your help

谢谢你的帮助

采纳答案by JosieP

you can loop with a counter:

你可以用一个计数器循环：

Sub FilterCDA()
   Dim sh1                         As Worksheet
   Dim N                           As Long
   Dim TopVisibleCell              As Range
   Dim sh2                         As Worksheet
   Dim HeaderRow                   As Long
   Dim LastFilterRow               As Long
   Dim st                          As String
   Dim rng1                        As Range
   Dim rng2                        As Range
   Dim rng3                        As Range
   Dim VTR                         As String
   Dim W                           As Integer
   Dim R                           As Integer
   Dim NOC                         As Range
   Dim NOC1                        As Integer
   Dim rSelect                     As Range
   Dim rCell                       As Range


   Set sh1 = Sheets("Request")
   Set sh2 = Sheets("Request")

   C = 2
   Set NOC = sh2.Range("D2")
   NOC1 = NOC.Value

   LR = Worksheets("ORT").Range("A" & Rows.Count).End(xlUp).Row
   Set Rstatus1 = Worksheets("ORT").Range("G2:G" & LR)
   Set Rstatus = Worksheets("ORT").Range("A1:G" & LR)
   N = sh1.Cells(Rows.Count, "C").End(xlUp).Row

   Sheets("CSV").Cells.NumberFormat = "@"
   For i = 2 To N
      v = sh1.Cells(i, 3).Value
      If v <> "" Then
         st = st & v & ","
      End If
   Next i
   st = Mid(st, 1, Len(st) - 1)
   Arr1 = Split(st, ",")
   Sheets("ORT").Activate
   For i = LBound(Arr1) To UBound(Arr1)
      Sheets("ORT").AutoFilterMode = False
      With Sheets("ORT").Range("A:G")
         .AutoFilter Field:=3, Criteria1:=Arr1(i), Operator:=xlFilterValues
      End With

      Fr = Worksheets("ORT").Range("C" & Rows.Count).End(xlUp).Row - 1

      ' No rows filtered then Fr = 0

      If Fr > 0 Then

         With Rstatus
            Set rVis = .Resize(.Rows.Count - 1, 1).Offset(1).SpecialCells(xlCellTypeVisible)
         End With

         For Each rCell In rVis.Cells
            If rSelect Is Nothing Then
               Set rSelect = rCell.Resize(, Rstatus.Columns.Count)
            Else
               Set rSelect = Union(rSelect, rCell.Resize(, Rstatus.Columns.Count))
            End If
            lCounter = lCounter + 1
            If lCounter >= NOC1 Then Exit For
         Next rCell

         rSelect.Copy
         Sheets("CSV").Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues

      ElseIf Fr = 0 Then
      End If

      Set NOC = NOC.Offset(1)
      NOC1 = NOC.Value
   Next i
   Sheets("ORT").AutoFilterMode = False

   Sheets("Request").Select
   Range("E2").Select
   ActiveCell.FormulaR1C1 = "=COUNTIF('CSV'!C[-2],'Request'!RC[-2])"
   On Error Resume Next
   Selection.AutoFill Destination:=Range("E2:E" & Range("C" & Rows.Count).End(xlUp).Row), Type:=xlFillCopy
   Columns("E:E").Select
   Selection.Copy
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                                   :=False, Transpose:=False

   Range("A1").Select
   Sheets("Control").Select
   Range("A1").Select


End Sub

回答by Gary's Student

If row #1 is the header row and you want to select the visible range of the AutoFilter and there is no "junk" below the filter in column Athen:

如果第 1 行是标题行，并且您想要选择自动筛选器的可见范围，并且A列中的筛选器下方没有“垃圾”，则：

Sub SelectVisibleA()
    Dim NLastVisible As Long, r As Range
    NLastVisible = Cells(Rows.Count, "A").End(xlUp).Row
    Set r = Range("A2:A" & NLastVisible).Cells.SpecialCells(xlCellTypeVisible)
    r.Select
End Sub

will select the visible material in column A...........you need to RESIZEto get additional columns.

将选择A列中的可见材料.......您需要调整大小以获取其他列。