Count cells that are not blank
This tutorial shows how to count cells that are not blank through the use of an Excel formula or VBA
Example: Count cells that are not blank
|
=COUNTA(C5:C11)
|
This formula uses the Excel COUNTA function to count the number of non-empty cells in range (C5:C11).
|
Sub Count_cells_that_are_not_blank()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'apply the formula to count cells that are not blank
ws.Range("C13") = Application.WorksheetFunction.CountA(ws.Range("C5:C11"))
ws.Range("C13") = Application.WorksheetFunction.CountA(ws.Range("C5:C11"))
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("C13") in the VBA code.
Range: Select the range from which you want to count cells that are not blank by changing the range reference ("C5:C11") in the VBA code.
Worksheet Selection: Select the worksheet which captures a range of cells from which you want to count the number of non blank 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 ("C13") in the VBA code.
Range: Select the range from which you want to count cells that are not blank by changing the range reference ("C5:C11") in the VBA code.
Worksheet Selection: Select the worksheet which captures a range of cells from which you want to count the number of non blank 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
Both the Excel and VBA methods use the COUNTA function to count the number of non blank cells in a selected range.
Both the Excel and VBA methods use the COUNTA function to count the number of non blank cells in a selected range.
FORMULA
=COUNTA(value1, value2, ...)
=COUNTA(value1, value2, ...)
ARGUMENTS
value1: Any value, cell reference or range of cells.
value2: Any value, cell reference or range of cells.
value1: Any value, cell reference or range of cells.
value2: Any value, cell reference or range of cells.
ADDITIONAL NOTES
Note 1: In Excel 2007 and later the COUNTA function can accept up to 255 value arguments. In Excel 2003 the COUNTA function can only accept up to 30 value arguments.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| If a cell contains text | How to test if a cell contains text and return a specified value using Excel and VBA methods | |
| Count cells that contain text | How to count cells that contain text using Excel and VBA methods | |
| Count cells that do not contain a specific value | How to count cells that do not contain a specific value using Excel and VBA methods | |
| Count cells that contain a specific value | How to count cells that contain a specific value using Excel and VBA methods | |
| Count cells that are blank | How to count cells that are blank using Excel and VBA methods |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| COUNTA Function | The Excel COUNTA function returns the number of non-empty cells from a specified range |