工作表中的行数 [英] Amount of rows in sheet
问题描述
目标:
变量应包含特定工作表中的行数.
Goal:
A variable should contain amount of rows from a specific sheet.
问题:
我需要在Excel VBA中使用什么语法代码来计算工作表中的行数?
Problem:
What syntax code in Excel VBA do I need to count amount of rows from sheet?
推荐答案
使用usedrange方法是我的最爱之一，但需要谨慎对待.它有一些缺陷/陷阱. excel不能很好地跟踪所使用的范围是一个已知的问题.通过VBA对使用范围的任何引用都会将该值重置为当前使用范围.因此，在获得使用范围时，请尝试运行此子过程:
Using the usedrange method is one of my favourites but it needs to be treated with care. It has a few flaws/gotchas. It is a known problem that excel does not keep track of the used range very well. Any reference to the used range via VBA will reset the value to the current used range. So try running this sub procedure when you are getting the used range:
Dim lRowCount as Long
Application.ActiveSheet.UsedRange
lRowCount = Worksheets("MySheet").UsedRange.Rows.Count
但是请注意，这将为您提供已使用的范围计数，因此，如果您在工作簿的顶部有空白行(人们通常会留空以放置诸如过滤条件之类的内容)，那么他们不会被计算在内. usedrange方法也可能会受到格式的影响.
But be aware this will give you the used range count, so if you have blank rows at the top of your workbook (which often people do to leave space for things like filter criteria etc) then they will not be counted. The usedrange method can also be affected by formatting.
如果您要使用最后一行，这是我认为想要的，那么可以使用更可靠的find方法:
If you want the last row used, which is what I think you want, then you can use the find method which is more reliable:
Dim rLastCell As Range
Dim lLastRow As Long
Set rLastCell = ActiveSheet.Cells.Find(What:="*", After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
If Not rLastCell Is Nothing Then lLastRow = rLastCell.Row
如果您知道其中至少有一个包含数据的单元格，则可以将以上内容简化为:
If you know that you have atleast one cell with data in it, then you can simplify the above to:
Dim lLastRow As Long
lLastRow = ActiveSheet.Cells.Find(What:="*", After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
这篇关于工作表中的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
