vba 如何将范围设置为没有第一行 + 另一列的 UsedRange 的交集？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11250216/
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
How to set a range as the intersection of UsedRange WITHOUT first row + Another Column?
提问by Alaa Elwany
I have a range variable "rng". I need to set rng to be the intersection of: (1) The usedrange on the sheet EXCLUDING the first column, AND (2) Column number 6, for example
我有一个范围变量“rng”。我需要将 rng 设置为以下项的交集：（1）工作表上的 usedrange 排除第一列，AND（2）列号 6，例如
Currently, I have:
目前，我有：
Set rng = Intersect(.UsedRange, .Columns(6)).SpecialCells(xlCellTypeVisible)
' Because the range is filtered and i only need to select visible cells
But this returns a column that also has the header row in it. I only need the numbers in the column.
但这会返回一列，其中也包含标题行。我只需要列中的数字。
(1) Any quick function/method/property to do that? (2) how do i find the size of this range? rng.rows.count always returns ONE, even though there are multiple cells in rng. Should I use rng.count? what's the difference?
(1) 任何快速功能/方法/属性来做到这一点？(2) 我如何找到这个范围的大小？rng.rows.count 始终返回 ONE，即使 rng 中有多个单元格。我应该使用 rng.count 吗？有什么不同？
Thank you very much,
非常感谢，
Al
铝
回答by Doug Glancy
I see that you've already accepted an answer, and yet I don't see how it answers your requirement that it doesn't include the header row. Here's my solution which does that. It also answers your question 2 of how to get the row count:
我看到您已经接受了一个答案，但我看不出它如何满足您的要求，即它不包含标题行。这是我的解决方案。它还回答了您关于如何获取行数的问题 2：
Sub GetRangeAndCountRows()
Dim rng As Excel.Range
Dim rngArea As Excel.Range
Dim RowCount As Long
With ActiveSheet
Set rng = Intersect(.UsedRange.Resize(.UsedRange.Rows.Count - 1, .UsedRange.Columns.Count).Offset(1, 0), .Columns(6)).SpecialCells(xlCellTypeVisible)
Debug.Print rng.Address
For Each rngArea In rng.Areas
RowCount = RowCount + rngArea.Rows.Count
Next rngArea
Debug.Print RowCount
End With
End Sub
回答by Scott Holtzman
Tim's comment above works well, of course.
当然，蒂姆上面的评论效果很好。
This answer is to keep in line with the code above, and also may be easier to read.
这个答案是为了与上面的代码保持一致，也可能更容易阅读。
Add .UsedRange.Offset(,1)instead of .UsedRangeto ignore the first column in the UsedRange in your Interesect formula.:
添加.UsedRange.Offset(,1)而不是.UsedRange以忽略 Interesect 公式中 UsedRange 中的第一列。：
Set rng = Intersect(.UsedRange.Offset(,1), .Columns(6)).SpecialCells(xlCellTypeVisible)
