vba 从过滤范围中获取最后一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12586848/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get Last Row From Filtered Range
提问by Alistair Weir
How do you find the last row of data when the data in your worksheet is filtered? I have been playing around with Special Cellsand Visible Cellsbut cannot find a solution. I think it must be some kind of variation on what I have below:
当你的工作表中的数据被过滤时，你如何找到最后一行数据？我一直在玩Special Cells，Visible Cells但找不到解决方案。我认为它一定是我下面的某种变化：
...
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
...
File can be found here:
文件可以在这里找到：
wikisend.com/download/443370/FindLRFilteredData.xls
wikisend.com/download/443370/FindLRFilteredData.xls
Edit:
编辑：
Realised after discussion with Siddharth I did not want the Last Rowproperty I needed to find a count of the number of visible rows which led on to Sid's solution below...
在与 Siddharth 讨论后意识到我不想要Last Row我需要的属性来找到导致 Sid 下面的解决方案的可见行数的计数......
采纳答案by Siddharth Rout
EDIT: Post Chat Followup
编辑：发布聊天跟进
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
回答by nutsch
After the filter, using the same formula for the lastrow will return the last filtered row:
过滤后，对 lastrow 使用相同的公式将返回最后过滤的行：
...
With ws
LR = .Range("A" & Rows.Count).End(xlUp).Row
.Range("A1:E" & LR).AutoFilter Field:=2, Criteria1:="=4"
LRfilt = .Range("A" & Rows.Count).End(xlUp).Row
Debug.Print LR
Debug.Print LRfilt
End With
...
回答by Peter M-B
This seems to work. When filters are on the normal .end(xlUp) gives the last row of a filtered range, but not the last row of the sheet. I suggest you use this technique to get the last row:
这似乎有效。当过滤器处于正常状态时， .end(xlUp) 给出过滤范围的最后一行，但不是工作表的最后一行。我建议您使用此技术获取最后一行：
Sub GetLastRow
' Find last row regardless of filter
If Not (ActiveSheet.AutoFilterMode) Then ' see if filtering is on if already on don't turn it on
Rows(1).Select ' Select top row to filter on
Selection.AutoFilter ' Turn on filtering
End if
b = Split(ActiveSheet.AutoFilter.Range.Address, "$") ' Split the Address range into an array based on "$" as a delimiter. The address would yeild something like $A:$H0
LastRow= Val(b(4)) ' The last value of the array will be "100" so find the value
End sub
回答by user3466047
This is simplest solution
这是最简单的解决方案
...
With ws
.Range("A1:E1").AutoFilter Field:=2, Criteria1:="=4"
LRfilt=.Range("A1", .Range("A1").End(xlDown)).End(xlDown).Row
Debug.Print LRfilt
End With
...
