vba 包含隐藏行和隐藏列的范围中最后可见的行是什么
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45905018/
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
what is last visible row in a range that contain hidden rows and hidden columns
提问by mgae2m
I wrote below
我写在下面
ThisWorkbook.Worksheets("Sheet1").Range("tblInsurance").SpecialCells(xlCellTypeVisible).Rows.Count
to point last row of tblInsuranceTable.
指向tblInsurance表的最后一行。
I pass ThisWorkbook.Worksheets("Sheet1").Range("tblInsurance")in method argument as:
我传入ThisWorkbook.Worksheets("Sheet1").Range("tblInsurance")方法参数为：
General.CsvExportRange rngRange:=ThisWorkbook.Worksheets("Sheet1").Range("tblInsurance")
and CsvExportRangemethod contain rngRange.Rows.Countline for finding last row of the range.
和CsvExportRange方法包含rngRange.Rows.Count用于查找范围最后一行的行。
But when reaching the hidden row, it stops counting and returns the number of the last row before the hidden one.
但是当到达隐藏行时，它停止计数并返回隐藏行之前的最后一行的编号。
when I use below:
当我在下面使用时：
ThisWorkbook.Worksheets("Sheet1").Range("tblInsurance").SpecialCells(xlCellTypeVisible).Select
range is selected entirely by separated boundaries around the visible area in the range.
范围完全由范围内可见区域周围的分离边界选择。
Seems the problem is in .SpecialCells(xlCellTypeVisible).Rows.Count->countmethod, which stops counting when reaching hidden row.
似乎问题出在.SpecialCells(xlCellTypeVisible).Rows.Count-> count方法中，该方法在到达隐藏行时停止计数。
I was reading similar posts and web discussions but they not contain above major issue. (Counting range rows for finding last row number, when the range contains hidden rows.)
我正在阅读类似的帖子和网络讨论，但它们不包含上述主要问题。（当范围包含隐藏行时，计算范围行以查找最后一行编号。）
This issue is a similar one, when I try to count range columns that contain some hidden column, to -for example- reach the last one.
这个问题是一个类似的问题，当我尝试计算包含一些隐藏列的范围列时，例如，到达最后一个。
采纳答案by Dy.Lee
This issue is interesting. I never thought of this issue. Try this:
这个问题很有趣。我从来没有想过这个问题。尝试这个：
Sub test()
Dim rngDB As Range, rng As Range
Dim n As Integer, c As Integer, cnt As Integer
Set rngDB = ThisWorkbook.Worksheets("Sheet1").Range("tblInsurance").SpecialCells(xlCellTypeVisible)
c = rngDB.Columns.Count
For Each rng In rngDB
n = n + 1
Next
cnt = n / c
MsgBox cnt
End Sub
If you need to know the number of last cell's row, try this:
如果您需要知道最后一个单元格的行数，请尝试以下操作：
Sub test2()
Dim rngDB As Range, rng As Range
Dim n As Integer
Set rngDB = ThisWorkbook.Worksheets("Sheet1").Range("tblInsurance").SpecialCells(xlCellTypeVisible)
n = rngDB.SpecialCells(xlCellTypeLastCell).Row
MsgBox "My last row number is : " & n
End Sub
I tested of course.
我当然测试过。
EditI understand your problem. try like this.
编辑我理解你的问题。像这样尝试。
Sub test3()
Dim rngDB As Range, rng As Range
Dim n As Integer, r as Long, c as Integer
Set rngDB = ThisWorkbook.Worksheets("Sheet1").Range("tblInsurance").SpecialCells(xlCellTypeVisible)
With rngDB
r = .Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
C = .Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Set rng = Range("a1", .Cells(r, C))
End With
MsgBox "My last row number is : " & r
End Sub
回答by Charles Williams
You can use Range.end(xlup).row to find the last visible row; if a1:a100 contains data but rows A8:a100 are hidden then this code will show row 7
您可以使用 Range.end(xlup).row 来查找最后一个可见行；如果 a1:a100 包含数据但行 A8:a100 被隐藏，则此代码将显示第 7 行
Sub lastvis()
MsgBox Range("A1000").End(xlUp).Row
End Sub
Or if using a table try this to find the last visible row.
或者，如果使用表格，请尝试此操作以查找最后可见的行。
Sub lastvis2()
MsgBox Range("tblInsurance").End(xlDown).Row
End Sub
回答by Gary's Student
Sadly .Countdoes not get us much information. Say we have only a single column, A1-A100and we run:
遗憾的是.Count没有给我们太多信息。假设我们只有一个列，A1-A100，我们运行：
Sub dural()
Dim r As Range, r2 As Range
Set r = Range("A1:A100")
Set r2 = r.SpecialCells(xlCellTypeVisible)
MsgBox r2.Rows.Count
End Sub
and when run, the macro gives us 4.
运行时，宏给了我们4。
All you know is the somewhere in the table there are 4 visible rows. We have no idea which are the visible ones. We also have no idea if any of these visible rows contain data or are empty.
您所知道的是表中某处有 4 行可见。我们不知道哪些是可见的。我们也不知道这些可见行中是否有任何包含数据或为空。
The best we can do is to loop through the table , starting from the bottom.
我们能做的最好的事情就是从表的底部开始循环。
When we encounter a row that is both visible and non-empty, stop and report the row number.
当我们遇到既可见又非空的行时，停止并报告行号。
