在选定区域中进行数量统计的VBA代码

来源:转载

如果在Excel中选择了一个或多个区域,用下面的VBA代码可以统计当前工作表所选区域中单元格(或行、列)的数量:

1. 统计选定区域中的单元格数量:

Sub CountCellsInSelection()

Dim CellsNum As Integer

CellsNum = Selection.Count

MsgBox "所选区域中的单元格数量为: " & CellsNum

End Sub

2.统计选定区域中所包含的行数,如果选择了多个区域,则统计行数之和。

Sub CountRowsInSelection()

Dim RowsNum As Integer

For i = 1 To Selection.Areas.Count

RowsNum = RowsNum + Selection.Areas(i).Rows.Count

Next i

MsgBox "所选区域中的行数为: " & RowsNum

End Sub

3.统计选定区域中所包含的列数,如果选择了多个区域,则统计列数之和。

Sub CountColumnsInSelection()

Dim ColumnsNum As Integer

For i = 1 To Selection.Areas.Count

ColumnsNum = ColumnsNum + Selection.Areas(i).Columns.Count

Next i

MsgBox "所选区域中的列数为: " & ColumnsNum

End Sub

4.统计选定区域中的非空单元格数量:

Sub CountNonBlankInSelection()

Dim NonBlankNum As Integer

NonBlankNum = Application.CountA(Selection)

MsgBox "所选区域中包含非空单元格有" & NonBlankNum & "个。"

End Sub

5.统计选定区域中有填充色的单元格数量:

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 "所选区域中填充了颜色的单元格有" & ColorCellsNum & "个。"

End Sub

6.统计选定区域中包含公式的单元格数量:

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 "所选区域中包含公式的单元格有" & FormulaNum & "个。"

End Sub

分享给朋友:
您可能感兴趣的文章:
随机阅读: