If a cell is blank in a range
This tutorial shows how to test if a cell is blank in a range and return a value if the test is True or False through the use of an Excel formula, with the IF and COUNTBLANK functions, or VBA
Hard coded formula
Cell reference formula
|
=IF(COUNTBLANK(C5:E5)>0,"Need Stock","Stocked")
=IF(COUNTBLANK(C5:E5)>0,$C$5,$C$6)
|
GENERIC FORMULA
=IF(COUNTBLANK(range)>0, value_if_true, value_if_false)
ARGUMENTS GENERIC FORMULA
=IF(COUNTBLANK(range)>0, value_if_true, value_if_false)
ARGUMENTS EXPLANATION This formula uses a combination of the IF and COUNTBLANK functions to assess if there is at least one blank (empty) cell in a specific range. The COUNTBLANK function will count the number of empty cells in a range. Therefore, if the COUNTBLANK function returns a value greater than 0, it means that the selected range contains a blank cell. Alternatively, if the COUNTBLANK function returns a value of 0, it means that there are no blank cells in the selected range.
The IF function will then assess if the COUNTBLANK function has returned a value of greater than 0 and if so then the formula will return a value that has been assigned as the true value, alternatively if the COUNTBLANK has returned a value of 0 the formula will return a value assigned as the false value.
With this formula you can enter the values, that will be returned if a range does or does not contain any empty cells, directly into the formula or reference them to specific cells that capture these values.
Click on either the Hard Coded or Cell Reference button to view the formula that has the return values directly entered into the formula or referenced to specific cells that capture these values, respectively.
In this example the formula tests if the selected range contains any blank cells. If the range contains at least one blank cell the formula will return a value of "Need Stock" (hard coded example) or value in cell C5 (cell reference example). If the cell is not empty the formula will return a value of "Stocked" (hard coded example) or value in cell C6 (cell reference example).
If you are using the formula with values entered directly in the formula and want to return a numerical value, instead of a text value, you do not need to apply the double quotation marks around the values that are to be returned e.g. (=IF(COUNTBLANK(C5:E5)>0,1,0)). |
Hard coded against single cell
Sub If_cell_is_blank_in_a_range()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'calculate if a cell is blank in a range
If ws.Application.WorksheetFunction.CountBlank(ws.Range("C5:E5")) > 0 Then
If ws.Application.WorksheetFunction.CountBlank(ws.Range("C5:E5")) > 0 Then
ws.Range("F5") = "Need Stock"
Else
ws.Range("F5") = "Stocked"
End If
End Sub
Cell reference against single cell
Sub If_cell_is_blank_in_a_range()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'calculate if a cell is blank in a range
If ws.Application.WorksheetFunction.CountBlank(ws.Range("C9:E9")) > 0 Then
If ws.Application.WorksheetFunction.CountBlank(ws.Range("C9:E9")) > 0 Then
ws.Range("F9") = ws.Range("C5")
Else
ws.Range("F9") = ws.Range("C6")
End If
End Sub
Hard coded against range of cells
Sub If_cell_is_blank_in_a_range()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'calculate if a cell is blank in a range by looping through each cell in the specified range
For x = 5 To 11
If ws.Application.WorksheetFunction.CountBlank(ws.Range(ws.Cells(x, 3), ws.Cells(x, 5))) > 0 Then
ws.Cells(x, 6) = "Need Stock"
Else
ws.Cells(x, 6) = "Stocked"
End If
Next x
Next x
End Sub
Cell reference against range of cells
Sub If_cell_is_blank_in_a_range()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'calculate if a cell is blank in a range by looping through each cell in the specified range
For x = 9 To 15
If ws.Application.WorksheetFunction.CountBlank(ws.Range(ws.Cells(x, 3), ws.Cells(x, 5))) > 0 Then
ws.Cells(x, 6) = ws.Range("C5")
Else
ws.Cells(x, 6) = ws.Range("C6")
End If
Next x
Next x
End Sub
KEY PARAMETERS
Output Range: Select the output range by changing the cell reference ("F5") in the VBA code.
Range to Test: Select the range that is to be tested by changing the range reference ("C5:E5") in the VBA code.
Worksheet Selection: Select the worksheet which captures the range of cells that you want to test if they are blank and return a specific 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.
True and False Results: In this example if a single cell is blank in a range the VBA code will return a value of "Need Stock". However, if none of the cells in the selected range are blank the VBA code will return a value of "Stocked". Both of these values can be changed to whatever value you desire by directly changing them in the VBA code.
Output Range: Select the output range by changing the cell reference ("F5") in the VBA code.
Range to Test: Select the range that is to be tested by changing the range reference ("C5:E5") in the VBA code.
Worksheet Selection: Select the worksheet which captures the range of cells that you want to test if they are blank and return a specific 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.
True and False Results: In this example if a single cell is blank in a range the VBA code will return a value of "Need Stock". However, if none of the cells in the selected range are blank the VBA code will return a value of "Stocked". Both of these values can be changed to whatever value you desire by directly changing them in the VBA code.
NOTES
Note 1: If a single cell in a range that is being tested returns a value of ("") this VBA code will identify that cell as blank.
Note 2: If your True or False result is a text value it will need to be captured within quotation marks (""). However, if the result is a numeric value, you can enter it without the use of quotation marks.
Note 1: If a single cell in a range that is being tested returns a value of ("") this VBA code will identify that cell as blank.
Note 2: If your True or False result is a text value it will need to be captured within quotation marks (""). However, if the result is a numeric value, you can enter it without the use of quotation marks.
KEY PARAMETERS
Output Range: Select the output range by changing the cell reference ("F9") in the VBA code.
Range to Test: Select the range that is to be tested by changing the range reference ("C9:E9") in the VBA code.
Worksheet Selection: Select the worksheet which captures the range of cells that you want to test if they are blank and return a specific 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.
True and False Results: In this example if a single cell is blank in a range the VBA code will return a value stored in cell C5. However, if none of the cells in a range are blank the VBA code will return a value stored in cell C6. Both of these values can be changed to whatever value you desire by either referencing to a different cell that captures the value that you want to return or change the values in those cells.
Output Range: Select the output range by changing the cell reference ("F9") in the VBA code.
Range to Test: Select the range that is to be tested by changing the range reference ("C9:E9") in the VBA code.
Worksheet Selection: Select the worksheet which captures the range of cells that you want to test if they are blank and return a specific 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.
True and False Results: In this example if a single cell is blank in a range the VBA code will return a value stored in cell C5. However, if none of the cells in a range are blank the VBA code will return a value stored in cell C6. Both of these values can be changed to whatever value you desire by either referencing to a different cell that captures the value that you want to return or change the values in those cells.
NOTES
Note 1: If a single cell in a range that is being tested returns a value of ("") this VBA code will identify that cell as blank.
Note 1: If a single cell in a range that is being tested returns a value of ("") this VBA code will identify that cell as blank.
KEY PARAMETERS
Output and Data Rows: Select the output rows and the rows that captures the cells that are to be tested by changing the x values (5 to 11). This example assumes that both the output and the associated test cell will be in the same row.
Test Columns: Select the columns that capture the cells that are to be tested by changing numbers 3 and 5, in ws.Range(ws.Cells(x, 3), ws.Cells(x, 5)).
Output Column: Select the output column by changing number 6, in ws.Cells(x, 6).
Worksheet Selection: Select the worksheet which captures the range of cells that you want to test if they are blank and return a specific 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.
True and False Results: In this example if a single cell is blank in a range the VBA code will return a value of "Need Stock". However, if none of the cells in the selected range are blank the VBA code will return a value of "Stocked". Both of these values can be changed to whatever value you desire by directly changing them in the VBA code.
Output and Data Rows: Select the output rows and the rows that captures the cells that are to be tested by changing the x values (5 to 11). This example assumes that both the output and the associated test cell will be in the same row.
Test Columns: Select the columns that capture the cells that are to be tested by changing numbers 3 and 5, in ws.Range(ws.Cells(x, 3), ws.Cells(x, 5)).
Output Column: Select the output column by changing number 6, in ws.Cells(x, 6).
Worksheet Selection: Select the worksheet which captures the range of cells that you want to test if they are blank and return a specific 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.
True and False Results: In this example if a single cell is blank in a range the VBA code will return a value of "Need Stock". However, if none of the cells in the selected range are blank the VBA code will return a value of "Stocked". Both of these values can be changed to whatever value you desire by directly changing them in the VBA code.
NOTES
Note 1: If a cell that is being tested is returning a value of ("") this VBA code will identify the cell as blank.
Note 2: If your True or False result is a text value it will need to be captured within quotation marks (""). However, if the result is a numeric value, you can enter it without the use of quotation marks.
Note 1: If a cell that is being tested is returning a value of ("") this VBA code will identify the cell as blank.
Note 2: If your True or False result is a text value it will need to be captured within quotation marks (""). However, if the result is a numeric value, you can enter it without the use of quotation marks.
KEY PARAMETERS
Output and Data Rows: Select the output rows and the rows that captures the cells that are to be tested by changing the x values (9 to 15). This example assumes that both the output and the associated test cell will be in the same row.
Test Columns: Select the columns that capture the cells that are to be tested by changing numbers 3 and 5, in ws.Range(ws.Cells(x, 3), ws.Cells(x, 5)).
Output Column: Select the output column by changing number 6, in ws.Cells(x, 6).
Worksheet Selection: Select the worksheet which captures the range of cells that you want to test if they are blank and return a specific 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.
True and False Results: In this example if a single cell is blank in a range the VBA code will return a value stored in cell C5. However, if none of the cells in a range are blank the VBA code will return a value stored in cell C6. Both of these values can be changed to whatever value you desire by either referencing to a different cell that captures the value that you want to return or change the values in those cells.
Output and Data Rows: Select the output rows and the rows that captures the cells that are to be tested by changing the x values (9 to 15). This example assumes that both the output and the associated test cell will be in the same row.
Test Columns: Select the columns that capture the cells that are to be tested by changing numbers 3 and 5, in ws.Range(ws.Cells(x, 3), ws.Cells(x, 5)).
Output Column: Select the output column by changing number 6, in ws.Cells(x, 6).
Worksheet Selection: Select the worksheet which captures the range of cells that you want to test if they are blank and return a specific 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.
True and False Results: In this example if a single cell is blank in a range the VBA code will return a value stored in cell C5. However, if none of the cells in a range are blank the VBA code will return a value stored in cell C6. Both of these values can be changed to whatever value you desire by either referencing to a different cell that captures the value that you want to return or change the values in those cells.
NOTES
Note 1: If a cell that is being tested is returning a value of ("") this VBA code will identify the cell as blank.
Note 1: If a cell that is being tested is returning a value of ("") this VBA code will identify the cell as blank.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| If a cell is blank | How to test if a cell is blank and return a specified value using Excel and VBA methods | |
| If a cell is not blank | How to test if a cell is not blank and return a value using Excel and VBA methods | |
| If a cell is not blank in a range | How to test if a cell is not blank in a range and return a value using Excel and VBA methods | |
| Count cells that are blank | How to count cells that are blank using Excel and VBA methods | |
| Count cells that are not blank | How to count cells that are not blank using Excel and VBA methods |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| IF Function | The Excel IF function performs a test on specified conditions entered into the formula and returns a specified value if the result is TRUE or another specified value if the result is FALSE | |
| COUNTBLANK Function | The Excel COUNTBLANK function returns the number of empty cells from a specified range |