Count number of cells in a range
This tutorial shows how to count the total number of cells in a range through the use of an Excel formula or VBA
Example: Count number of cells in a range
Sub Count_number_of_cells_in_range()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'count the total number of cells in a specific range
ws.Range("B5") = ws.Range("E5:K10").Rows.Count * ws.Range("E5:K10").Columns.Count
ws.Range("B5") = ws.Range("E5:K10").Rows.Count * ws.Range("E5:K10").Columns.Count
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("B5") in the VBA code.
Range: Select the range from which you want to count number of cells by changing the range reference ("E5:K10") in the VBA code.
Worksheet Selection: Select the worksheet from which to count number of cells by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Output Range: Select the output range by changing the cell reference ("B5") in the VBA code.
Range: Select the range from which you want to count number of cells by changing the range reference ("E5:K10") in the VBA code.
Worksheet Selection: Select the worksheet from which to count number of cells by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
EXPLANATION
This tutorial shows how to count cells with specific value in odd rows through the use of an Excel formula or VBA.
Both the Excel formula and VBA approach make use of the ROWS and COLUMNS function to count the number of rows and columns in a selected range. The ROWS and COLUMNS functions are populated with the same range and then multiplied to return the total number of rows and columns in the specified range.
Using the VBA method you will also need to combine the Rows and Columns functions with the Count function to return the total number of rows and columns in a selected range.
FORMULA
=ROWS(array)*COLUMNS(array)
=ROWS(array)*COLUMNS(array)
ARGUMENTS
array: An array or reference to a range of cells.
array: An array or reference to a range of cells.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Count number of columns in a range | How to count the total number of columns in a range using Excel and VBA methods | |
| Count number of rows in a range | How to count the total number of rows in a range using Excel and VBA methods | |
| Count numeric characters in a cell | How to count the total number of numeric characters in a cell using Excel and VBA methods | |
| Count number of characters in a cell | How to count the total number of characters, including spaces, in a cell using Excel and VBA methods | |
| Count number of characters in a cell excluding spaces | How to count the total number of characters in a cell, excluding spaces, using Excel and VBA methods |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| ROWS Function | The Excel ROWS function returns the number of rows in a specified array | |
| COLUMNS Function | The Excel COLUMNS function returns the number of columns in a specified array |