如果在Excel中選擇了一個或多個區(qū)域,用下面的VBA代碼可以統(tǒng)計當(dāng)前工作表所選區(qū)域中單元格(或行、列)的數(shù)量:
1. 統(tǒng)計選定區(qū)域中的單元格數(shù)量:
Sub CountCellsInSelection()
Dim CellsNum As Integer
CellsNum = Selection.Count
MsgBox "所選區(qū)域中的單元格數(shù)量為: " & CellsNum
End Sub
2.統(tǒng)計選定區(qū)域中所包含的行數(shù),如果選擇了多個區(qū)域,則統(tǒng)計行數(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)計選定區(qū)域中所包含的列數(shù),如果選擇了多個區(qū)域,則統(tǒng)計列數(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)計選定區(qū)域中的非空單元格數(shù)量:
Sub CountNonBlankInSelection()
Dim NonBlankNum As Integer
NonBlankNum = Application.CountA(Selection)
MsgBox "所選區(qū)域中包含非空單元格有" & NonBlankNum & "個。"
End Sub
5.統(tǒng)計選定區(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 & "個。"
End Sub
6.統(tǒng)計選定區(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 & "個。"
End Sub