excel - VBA 计算范围内的单元格数

Question

试图计算一个范围内的单元格数以显示在状态栏上。不知道如何计算范围内的单元格数以用作进度条的分母。有什么想法吗？

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

非常感谢

Answer 1

此版本逐行查看您的范围，而不是逐个单元格

它还会关闭 ScreenUpdating为避免闪烁，重置 StatusBar ETC

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