vba ActiveSheet.UsedRange.Columns.Count - 8 是什么意思？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21172108/
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
ActiveSheet.UsedRange.Columns.Count - 8 what does it mean?
提问by strangeQuirks
what does ActiveSheet.UsedRange.Columns.Count - 8 mean in vba?
ActiveSheet.UsedRange.Columns.Count - 8 在 vba 中是什么意思？
How does vba know the usedRange?
vba 如何知道usedRange？
回答by Bernard Saucier
Here's the exact definition of UsedRange(MSDN reference) :
这是UsedRange（MSDN参考）的确切定义：
Every Worksheet object has a UsedRange property that returns a Range object representing the area of a worksheet that is being used. The UsedRange property represents the area described by the farthest upper-left and farthest lower-right nonempty cells in a worksheet and includes all cells in between.
每个 Worksheet 对象都有一个 UsedRange 属性，该属性返回一个 Range 对象，该对象表示正在使用的工作表的区域。UsedRange 属性表示由工作表中最远的左上角和最远的右下角非空单元格描述的区域，并包括其间的所有单元格。
So basically, what that line does is :
所以基本上，该行的作用是：
.UsedRange-> "Draws" a box around the outer-most cells with content inside..Columns-> Selects the entire columns of those cells.Count-> Returns an integer corresponding to how many columns there are (in this selection)- 8-> Subtracts 8 from the previous integer.
.UsedRange-> 在最外面的单元格周围“画”一个框，里面有内容。.Columns-> 选择这些单元格的整列.Count-> 返回与有多少列相对应的整数（在此选择中）- 8-> 从前一个整数中减去 8。
I assume VBA calculates the UsedRange by finding the non-empty cells with lowest and highest index values.
我假设 VBA 通过查找具有最低和最高索引值的非空单元格来计算 UsedRange。
Most likely, you're getting an error because the number of lines in your range is smaller than 3, and therefore the number returned is negative.
最有可能的是，您收到错误是因为您范围内的行数小于 3，因此返回的数字为负数。
回答by Siddharth Rout
BernardSaucier has already given you an answer. My post is not an answer but an explanation as to why you shouldn't be using UsedRange.
BernardSaucier 已经给了你答案。我的帖子不是答案，而是解释为什么不应该使用UsedRange.
UsedRangeis highly unreliable as shown HERE
UsedRange是非常不可靠如图该处
To find the last column which has data, use .Findand then subtract from it.
要找到包含数据的最后一列，请使用.Find然后从中减去。
With Sheets("Sheet1")
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
lastCol = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
Else
lastCol = 1
End If
End With
If lastCol > 8 Then
'Debug.Print ActiveSheet.UsedRange.Columns.Count - 8
'The above becomes
Debug.Print lastCol - 8
End If
回答by whytheq
Seems like you want to move around. Try this:
似乎你想四处走动。尝试这个：
ActiveSheet.UsedRange.select
results in....
结果是....
If you want to move that selection 3 rows up then try this
如果您想将该选择向上移动 3 行，请尝试此操作
ActiveSheet.UsedRange.offset(-3).select
does this...
做这个...
回答by pnuts
I think if you try:
我想如果你尝试：
Sub Macro3()
a = ActiveSheet.UsedRange.Columns.Count - 3
End Sub
with a watch on ayou will see it does make a difference.
戴上手表，a您会发现它确实有所作为。
回答by Leszek
UsedRange represents not only nonempty cells, but also formatted cells without any value. And that's why you should be very vigilant.
UsedRange 不仅表示非空单元格，还表示没有任何值的格式化单元格。这就是为什么你应该非常警惕。
