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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-08 18:18:02  来源:igfitidea点击:

VBA Count number of cells in range

excelexcel-vbavba

提问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 ScreenUpdatingto avoid flicker, resets the StatusBaretc

它还会关闭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