Count cells from entire column that contain value
This tutorial shows how to count cells from a single column that contain a value through the use of an Excel formula or VBA
Example: Count cells from entire column that contain value
|
=COUNTA(C:C)
|
The formula uses the Excel COUNTA function to count the number of non-empty cells from the selected column (C).
|
Sub Count_cells_from_entire_column_that_contain_value()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'count the number of cells in a single column that have a value
ws.Range("E5") = Application.WorksheetFunction.CountA(ws.Range("C:C"))
ws.Range("E5") = Application.WorksheetFunction.CountA(ws.Range("C:C"))
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("E5") in the VBA code.
Column Reference: Select the column that you want to count from by changing the range reference ("C:C") in the VBA code.
Worksheet Selection: Select the worksheet which captures the column from which you want to count the number of cells that contain a value 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 ("E5") in the VBA code.
Column Reference: Select the column that you want to count from by changing the range reference ("C:C") in the VBA code.
Worksheet Selection: Select the worksheet which captures the column from which you want to count the number of cells that contain a value 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 from a single column that contain a value using an Excel formula and VBA.
Both the Excel and VBA methods make use of the COUNTA function and selecting an entire column to count cells from a single column that contain a value. In this example, the formula counts the cells that contain a value from column C.
FORMULA
=COUNTA(column_reference)
=COUNTA(column_reference)
ARGUMENTS
column_reference: The column from which you want to count the cells that contain a value.
column_reference: The column from which you want to count the cells that contain a value.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Count cells from entire column that contain numbers | How to count cells from a single column that contain numbers (cells that are numeric values) using Excel and VBA methods | |
| Count cells from entire column that contain text | How to count cells from a single column that contain text (cells that are text values) using Excel and VBA methods | |
| Count cells from multiple columns that contain value | How to count cells from multiple columns that contain a value using Excel and VBA methods | |
| Count cells if greater than | How to count cells that are greater than a specific value using Excel and VBA methods | |
| Count cells if less than | How to count cells that are less than a specific value 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 |