excel 如何在应用筛选器后从表中选择和复制前5行

egdjgwm8 于 2022-12-01 发布在其他

关注(0)|答案(1)|浏览(143)

在表格中套用筛选后，我想复制前5列（至储存格M7）。我尝试过在互联网上找到的宏，但它在我的档案中无法运作。

Sub TopNRows()
Dim i As Long
Dim r As Range
Dim rWC As Range

Set r = Range("B16", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)

For Each rWC In r
    i = i + 1
    If i = 5 Or i = r.Count Then Exit For
Next rWC
Range(r(2), rWC).Resize(, 7).SpecialCells(xlCellTypeVisible).Copy Sheet7.[M7]
End Sub

我试着定制它们，我的表有x行（我动态操作）和7列。标题在（B15：H15）中。但是，它们并不总是有效。错误在

Range(r(2), rWC).Resize(, 7).SpecialCells(xlCellTypeVisible).Copy Sheet7.[M7]

excel

来源：https://stackoverflow.com/questions/74592430/how-to-select-and-copy-the-first-5-rows-from-a-table-after-applying-filters

1条答案

按热度按时间

hfyxw5xn1#

请尝试以下操作...

Sub TopNRows()

    Dim rng As Range
    Dim filt As Range
    Dim topRows As Range
    Dim currentCell As Range
    Dim count As Long
    
    Set rng = Range("B15", Range("B" & Rows.count).End(xlUp))
    
    With rng
        On Error Resume Next
        Set filt = .Offset(1, 0).Resize(.Rows.count - 1).SpecialCells(xlCellTypeVisible)
        If filt Is Nothing Then
            MsgBox "No records found!", vbExclamation
            Exit Sub
        End If
        On Error GoTo 0
    End With
    
    count = 0
    For Each currentCell In filt.Cells
        If topRows Is Nothing Then
            Set topRows = currentCell
        Else
            Set topRows = Union(topRows, currentCell)
        End If
        count = count + 1
        If count >= 5 Then Exit For
    Next currentCell
    
    topRows.Copy Sheet7.[M7]

End Sub

赞(0）回复(0）举报 2022-12-01

我来回答

excel 如何在应用筛选器后从表中选择和复制前5行

1条答案

相关问题

热门标签

最新问答