Md. Araf Bin Jayed
Sometimes, in Excel, we need to hide cells or hide entire rows or columns. We want to input data into those cells later. Or sometimes, we have to hide the contents of the cells. In this article, you will see how to hide cells in Excel until data is entered.
Download Practice Workbook
You can download the free Excel workbook here and practice on your own.
2 Handy Approaches to Hide Cells in Excel Until Data Entered
In this article, we will use the data set given below. Here we have some information regarding some people. Among this information, some are numeric and some are of textual value. We will demonstrate two different methods to accomplish our task. In our first procedure, we will change the cell format of some particular cells, and in our second one, we will apply VBA to hide rows and columns of the data set, respectively.
1. Changing Cell Format to Hide Cells in Excel Until Data Entered
In our first approach, we will hide the cells in our data set by changing the cell format. To do that, execute the following steps.
Step 1:
- First of all, select the cells that you want to hide.
Step 2:
- Secondly, right-click on the cells after selecting them and then choose the Format Cells… command.
Step 3:
- Then, you will see a dialogue box named Format Cells.
- From that box, choose the Custom command under the Number tab.
- Then, after choosing the command, type three semicolons (;) in the Type box.
- Finally, press OK.
Step 4:
- Finally, after typing, you will find the selected cells in the data set empty.
- Furthermore, if you want to unhide the cells, then select the Format Cells… command again.
- Then, choose the original number format of the data.
- Here, it is in the Text format in our example.
- Then, press OK.
- Finally, you will see the data in the cells again.
Read More: How to Hide Cell Contents in Excel Formula Bar (5 Methods)
2. Applying VBA to Hide Cells in Excel
In our previous approach, we hid some cells in our data set by changing the cell format. In our second method, we will apply VBA to hide an entire row or column based on their cell values.
2.1 Inserting Cell Text Value to Hide Rows Automatically
In this approach, we will hide an entire row automatically by applying VBA. To hide the row, we will specify a text value from the data set in the code. For a better understanding, go through the following steps.
Step 1:
- Firstly, take the following data set for applying VBA.
- Then, from the data set, choose any specific text value from any cell.
- For example, we will choose the name Jack which is in row 7.
Step 2:
- Secondly, choose the Visual Basic command from the Developer tab of the ribbon.
- Then, choose the Module command from the Insert tab in the pop-up window.
- Then, copy the following code and paste it into the module.
'Set the name of the function
Sub Inserting_Text_Value_to_Hide_Row()
'Declaring the first row of the data set
StartRow = 4
'Declaring the last row of the data set
LastRow = 10
'Addressing the column number that holds the text value
iCol = 2
'Finding the specific word of the above from first row to last row of the data set and hide the row
For i = StartRow To LastRow
'Start to find the specific word Jack from the adressed row by looping. If the word is not found, then the rows wont'be hidden
If Cells(i, iCol).Value <> "Jack" Then
Cells(i, iCol).EntireRow.Hidden = False
'If the word is found in the specific column then the rows will be hidden
Else
Cells(i, iCol).EntireRow.Hidden = True
End If
Next i
End Sub
➤ We will use the function name of the VBA as Inserting_Text_Value_to_Hide_Row.
➤ Then, we will address the first row which is StartRow= 4 because we can find our data set from row 4.
➤ After that, we will set the last row of the data set which is LastRow=10 as we can our last data in row 10.
➤ Next, we will give the number of columns in which the text value is in by iCol=2.
➤ Then, with the IF fucntion, the code starts to look for the word “Jack” from the addressed first row (4) to the last row (10) of the data set. Now the loop variable i finds the word “Jack” in any row of the declared column (B), then hides the entire row from the data set automatically. It continues to loop until it searches in all the rows.
➤ Finally, with the ELSE function the code will assure that, if the specific word is not found then, any row will not be hidden.
Step 3:
- Thirdly, save the code and press the play button to run.
- Consequently, you will see that row 7, containing the word Jack has been hidden.
Step 4:
- Furthermore, if you want to show the specific row again, then click on between rows 6 and 8.
- Then again right-click on the selection and choose the Unhide command.
- Finally, you will see the row again.
2.2 Inserting Cell Numeric Value to Hide Columns Automatically
We can also hide an entire column based on a cell value by applying VBA similar to the previous method. In this approach, we will insert a numeric value from the data set into the code to hide the column. Follow the below steps to understand the process.
Step 1:
- First of all, we will select a random numerical value from the data set.
- For our working purpose, we will take the numerical value 24 which is in column C.
Step 2:
- Secondly, go to the Developer tab of the ribbon and choose the Visual Basic command.
- Then, copy the following code and paste it into the Module tab like in the previous procedure.
'Address the function name
Sub Inserting_Numeric_Value_to_Hide_Column()
'Declare the first column, last column and the row number that holds the numeric value in the data set
StartColumn = 2
LastColumn = 5
iRow = 7
'Finding the specific number from first column to last column of the data set and hide the column
For i = StartColumn To LastColumn
If Cells(iRow, i).Value <> "24" Then
Cells(iRow, i).EntireColumn.Hidden = False
Else
Cells(iRow, i).EntireColumn.Hidden = True
End If
Next i
End Sub
➤ We will use the function name of the VBA as Inserting_Numeric_Value_to_Hide_Column.
➤ Then, we will address the first column which is StartColumn= 2 because we can find our data set from column 2.
➤ After that, we will set the last row of the data set which is LastColumn= 5 as we can our last data in row 5.
➤ Next, we will give the number of row in which the numeric value is in by iRow=7.
➤ Then, with the IF fucntion, the code starts to look for the number “24” from the addressed first column (2) to the last column (5) of the data set. Now the loop variable i finds the number “24” in any column of the declared row (7), then hides the entire column from the data set automatically. It continues to loop until it searches in all the columnc.
➤ Finally with the ELSE function the code will assure that, if the specific number is not found then, any column will not be hidden.
Step 3:
- Thirdly, save the code and press F5 or the play button.
- Consequently, you will find column C hidden from the data set in which you have seen the numerical value 24.
Step 4:
- Furthermore, to unhide the column, click in between columns C and D.
- Then, right-click on the selection and choose the Unhide command.
- Therefore, the column will be visible in the data set again.
Read More: How to Hide Extra Cells in Excel (with Quick Steps)
Conclusion
That’s the end of this article. I hope you find this article helpful. After reading this article, you will be able to hide cells in Excel until data entered by using any of the methods. Please share any further queries or recommendations with us in the comments section below.
