从过滤范围获取最后一行 [英] Get Last Row From Filtered Range

问题描述

当您的工作表中的数据被过滤时，如何找到最后一行数据？我一直在玩特殊单元格和可见单元格，但找不到解决方案。我认为这对我有以下的一些变化：

How do you find the last row of data when the data in your worksheet is filtered? I have been playing around with Special Cells and Visible Cells but cannot find a solution. I think it must be some kind of variation on what I have below:

    ...
    With ws
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        .Range("A1:E" & LR).AutoFilter Field:=2, Criteria1:="=4"
        LRfilt = .Range("A" & Rows.SpecialCells(xlCellTypeVisible).Count).End(xlUp).Row
        Debug.Print LR
        Debug.Print LRfilt
    End With
    ...

文件可以在这里找到：

wikisend.com/download/443370/FindLRFilteredData.xls

wikisend.com/download/443370/FindLRFilteredData.xls

编辑：

在与Siddharth讨论后实现我不想要 Last Row 属性我需要找到可见行数这导致了Sid的解决方案。

Realised after discussion with Siddharth I did not want the Last Row property I needed to find a count of the number of visible rows which led on to Sid's solution below...

推荐答案

编辑：发布聊天跟进

Option Explicit

Sub FilterTest()
    Dim rRange As Range, fltrdRng As Range, aCell As Range, rngToCopy As Range
    Dim ws As Worksheet
    Dim LR As Long

    '~~> Change this to the relevant sheet
    For Each ws In ThisWorkbook.Worksheets
        If Not ws.Name = "Sheet1" Then
            With ws                    
                '~~> Remove any filters
                .AutoFilterMode = False

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

                '~~> Change this to the relevant range
                Set rRange = .Range("A1:E" & LR)

                With rRange
                    '~~> Some Filter. Change as applicable
                    .AutoFilter Field:=2, Criteria1:=">10"

                    '~~> Get the filtered range
                    Set fltrdRng = .SpecialCells(xlCellTypeVisible)
                End With

                For Each aCell In fltrdRng
                    If aCell.Column = 1 Then
                        If rngToCopy Is Nothing Then
                            Set rngToCopy = aCell
                        Else
                            Set rngToCopy = Union(rngToCopy, aCell)
                        End If
                    End If
                Next

                Debug.Print ws.Name
                Debug.Print rngToCopy.Address

                'rngToCopy.Copy

                Set rngToCopy = Nothing

                '~~> Remove any filters
                .AutoFilterMode = False
            End With
        End If
    Next
End Sub

这篇关于从过滤范围获取最后一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！