excel在選定區(qū)域中進(jìn)行數(shù)量統(tǒng)計(jì)的VBA代碼

如果在Excel中選擇了一個(gè)或多個(gè)區(qū)域，用下面的VBA代碼可以統(tǒng)計(jì)當(dāng)前工作表所選區(qū)域中單元格（或行、列）的數(shù)量：

1. 統(tǒng)計(jì)選定區(qū)域中的單元格數(shù)量：

Sub CountCellsInSelection()
Dim CellsNum As Integer
CellsNum = Selection.Count
MsgBox "所選區(qū)域中的單元格數(shù)量為： " & CellsNum
End Sub

2.統(tǒng)計(jì)選定區(qū)域中所包含的行數(shù)，如果選擇了多個(gè)區(qū)域，則統(tǒng)計(jì)行數(shù)之和。

Sub CountRowsInSelection()
Dim RowsNum As Integer
For i = 1 To Selection.Areas.Count
RowsNum = RowsNum + Selection.Areas(i).Rows.Count
Next i
MsgBox "所選區(qū)域中的行數(shù)為： " & RowsNum
End Sub

3.統(tǒng)計(jì)選定區(qū)域中所包含的列數(shù)，如果選擇了多個(gè)區(qū)域，則統(tǒng)計(jì)列數(shù)之和。

Sub CountColumnsInSelection()
Dim ColumnsNum As Integer
For i = 1 To Selection.Areas.Count
ColumnsNum = ColumnsNum + Selection.Areas(i).Columns.Count
Next i
MsgBox "所選區(qū)域中的列數(shù)為： " & ColumnsNum
End Sub

4.統(tǒng)計(jì)選定區(qū)域中的非空單元格數(shù)量：

Sub CountNonBlankInSelection()
Dim NonBlankNum As Integer
NonBlankNum = Application.CountA(Selection)
MsgBox "所選區(qū)域中包含非空單元格有" & NonBlankNum & "個(gè)。"
End Sub

5.統(tǒng)計(jì)選定區(qū)域中有填充色的單元格數(shù)量：

Sub CountColorCellsInSelection()
Dim ColorCellsNum As Integer
Dim rCell As Range
For Each rCell In Selection
If rCell.Interior.ColorIndex > 0 Then
ColorCellsNum = ColorCellsNum + 1
End If
Next rCell
MsgBox "所選區(qū)域中填充了顏色的單元格有" & ColorCellsNum & "個(gè)。"
End Sub

6.統(tǒng)計(jì)選定區(qū)域中包含公式的單元格數(shù)量：

Sub CountFormulaInSelection()
Dim FormulaNum As Integer
Dim rCell As Range
For Each rCell In Selection
If rCell.HasFormula Or rCell.HasArray Then
FormulaNum = FormulaNum + 1
End If
Next rCell
MsgBox "所選區(qū)域中包含公式的單元格有" & FormulaNum & "個(gè)。"
End Sub