VBA 计算范围内的单元格数
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/24117636/
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
VBA Count number of cells in range
提问by user3450844
Trying to count the number of cells within a range to display on the status bar. Not sure how to count the number of cells within the range to use as the denominator for the progress bar. Any thoughts?
尝试计算要显示在状态栏上的范围内的单元格数量。不确定如何计算范围内用作进度条分母的单元格数量。有什么想法吗?
For Each cell In Range("E11:G28,E33:G50,E57:G74,E79:G96,E101:G118,E130:G147,E152:G169,E175:G192,E198:G215,E221:G238")
**lTotal = Range.Cells.Count**
Application.StatusBar = "Processing cell " & cell.AddressLocal & _
" " & Format((lCounter / lTotal), "0%")
cell.EntireRow.Hidden = (cell.Value = "")
lCounter = lCounter = 1
Next cell
Many thanks
非常感谢
回答by brettdj
This version looks at your range, row by row, rather than by cell by cell
此版本逐行查看您的范围,而不是逐个单元格
It also turns off ScreenUpdating
to avoid flicker, resets the StatusBar
etc
它还会关闭ScreenUpdating
以避免闪烁,重置StatusBar
等
Sub ReCut()
Dim rData As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rcell As Range
Dim lngCnt As Long
Dim lngCalc As Long
Set rData = Range("E11:G28,E33:G50,E57:G74,E79:G96,E101:G118,E130:G147,E152:G169,E175:G192,E198:G215,E221:G238")
For Each rng1 In rData.Areas
ltotal = ltotal + rData.Rows.Count
Next
With Application
.ScreenUpdating = False
.EnableEvents = False
lngCalc = .Calculation
.Calculation = xlCalculationManual
For Each rng2 In rData.Areas
For Each rcell In rng2.Rows
rcell.EntireRow.Hidden = (.CountBlank(rcell) = 3)
lngCnt = lngCnt + 1
.StatusBar = "Processing row " & lngCnt & " " & Format((lngCnt / ltotal), "0%")
Next rcell
Next
.ScreenUpdating = True
.EnableEvents = True
.Calculation = lngCalc
.StatusBar = vbNullString
End With
End Sub
回答by n3cao
Try this -
尝试这个 -
Range(Selection.Address).Count
回答by L42
Try this:
尝试这个:
Sub Home()
Dim cell As Range, N As Long
For Each cell In Range("E11:G28,E33:G50,E57:G74,E79:G96,E101:G118,E130:G147,E152:G169,E175:G192,E198:G215,E221:G238").Areas
N = N + cell.Cells.Count
Next cell
Debug.Print N
End Sub
This counts all the cells in your range.
这会计算您范围内的所有单元格。
回答by Rory
For example:
例如:
Dim rData As Range
Set rData = Range("E11:G28,E33:G50,E57:G74,E79:G96,E101:G118,E130:G147,E152:G169,E175:G192,E198:G215,E221:G238")
lTotal = rData.Count
For Each cell In rData.Cells
Application.StatusBar = "Processing cell " & cell.AddressLocal & _
" " & Format((lCounter / lTotal), "0%")
cell.EntireRow.Hidden = (cell.Value = "")
lCounter = lCounter + 1
Next cell