VBA to Hide Rows Based on Cell Value in Excel (14 Examples)

Sometimes we need to hide some specific rows based on various values from different cells in our large dataset in Excel for better readability. Implementing VBA macro is the most effective, quickest, and safest method to run any operation in Excel. In this article, we will show you how to hide rows based on cell value in Excel with the VBA macro.


Download Workbook

You can download the free practice Excel workbook from here.


14 Examples to Hide Rows Based on Cell Value with VBA in Excel

In this section, we will discuss 14 effective examples with VBA to hide rows based on cell value in Excel.

Dataset for VBA to Hide Rows Based on Cell Value in Excel

Above is the example dataset that we will be using throughout this whole article.


1. Embed VBA to Hide Rows Based on Cell Text Value in Excel

Suppose you want to hide rows that hold a specific text value in a particular cell. In our case, we will give you an example with the row that contains the word “Chemistry” in Cell D6 and provide you with the code on how to hide that row, row 6 based on the text.

Steps:

  • In the beginning, press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

  • Next, in the pop-up code window, from the menu bar, click Insert -> Module.

  • Then, copy the following code and paste it into the code window.
Sub HideRowCellTextValue()
    StartRow = 4
    LastRow = 10
    iCol = 4
    For i = StartRow To LastRow
        If Cells(i, iCol).Value <> "Chemistry" Then
        Cells(i, iCol).EntireRow.Hidden = False
        Else
        Cells(i, iCol).EntireRow.Hidden = True
        End If
    Next i
End Sub

Your code is now ready to run.

VBA to Hide Rows Based on Cell Text Value in Excel

Here,

  • StartRow = 4 -> First row of the dataset.
  • LastRow = 10 -> Last row of the dataset.
  • iCol = 4 -> The column address that holds the text value.
  • Press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Run icon in the sub-menu bar to run the macro.

After the successful code execution, look at the image below to find out the result.

Result of VBA to Hide Rows Based on Cell Text Value in Excel

As a result, row number 6 which consists of the word “Chemistry” in Cell D6 is now hidden from our dataset.

VBA Code Explanation

Sub HideRowCellTextValue()

First, provide a name for the sub-procedure of the macro.

StartRow = 4

Then, declare the first row of the dataset. We set StartRow = 4 because our data starts from row 4. You must modify this line according to your dataset.

LastRow = 10

Next, declare the last row of the dataset. We set LastRow = 10 because our data ends in row 10. You must modify this line according to your dataset.

iCol = 4

After that, declare the column address that holds the text value. We will hide rows based on the value residing in Column D, so we set iCol = 4. You must modify this line according to your dataset.

For i = StartRow To LastRow
    If Cells(i, iCol).Value <> "Chemistry" Then
        Cells(i, iCol).EntireRow.Hidden = False
    Else
        Cells(i, iCol).EntireRow.Hidden = True
    End If
Next i

Now, starts looping from the declared first row (4) to the declared last row (10). If the iteration variable i finds the word “Chemistry” in any row of the declared column (D), then hides the entire rows. It continues iterating until it reaches all the rows from that column.

End Sub

Finally, end the sub-procedure of the macro.

Read More: Hide Rows Based on Cell Value with Conditional Formatting in Excel


2. Apply Macro to Hide Rows Based on Cell Numeric Value in Excel

Previously you have seen how to hide a row based on cell text value, this time you will learn how to do that when the value is numeric. In our case, we will give you an example with the row that contains the numeric value “87” in Cell D7 and provide you with the code on how to hide that row, row 7 based on that numeric value.

Steps:

  • Firstly, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • Secondly, copy the following code and paste it into the code window.
Sub HideRowCellNumValue()
    StartRow = 4
    LastRow = 10
    iCol = 4
    For i = StartRow To LastRow
        If Cells(i, iCol).Value <> "87" Then
        Cells(i, iCol).EntireRow.Hidden = False
        Else
        Cells(i, iCol).EntireRow.Hidden = True
        End If
    Next i
End Sub

Your code is now ready to run.

VBA to Hide Rows Based on Cell Numeric Value in Excel

Here,

  • StartRow = 4 -> First row of the dataset.
  • LastRow = 10 -> Last row of the dataset.
  • iCol = 4 -> The column address that holds the text value.
  • After that, Run this piece of code.

Result of VBA to Hide Rows Based on Cell Numeric Value in Excel

Finally, row number 7 which consists of the numeric value “87” in Cell D7 is now hidden.

VBA Code Explanation

Sub HideRowCellNumValue()

First, provide a name for the sub-procedure of the macro.

StartRow = 4

Then, declare the first row of the dataset. We set StartRow = 4 because our data starts from row 4. You must modify this line according to your dataset.

LastRow = 10

Next, declare the last row of the dataset. We set LastRow = 10 because our data ends in row 10. You must modify this line according to your dataset.

iCol = 4

After that, declare the column address that holds the text value. We will hide rows based on the value residing in Column D, so we set iCol = 4. You must modify this line according to your dataset.

For i = StartRow To LastRow
    If Cells(i, iCol).Value <> "87" Then
        Cells(i, iCol).EntireRow.Hidden = False
    Else
        Cells(i, iCol).EntireRow.Hidden = True
    End If
Next i

Now, starts looping from the declared first row (4) to the declared last row (10). If the iteration variable i finds the number “87” in any row of the declared column (D), then hides the entire rows. It continues iterating until it reaches all the rows from that column.

End Sub

Finally, end the sub-procedure of the macro.

Read More: How to Hide Rows in Excel (6 Effective Methods)


3. Implement VBA to Hide All Rows Where Cells Contain Texts in Excel

If you want to hide all rows where cells contain text values only, then follow the steps provided below.

Steps:

  • Firstly, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • Secondly, copy the following code and paste it into the code window.
Sub HideAllRowsContainsText()
    LastRow = 1000 'Let's assume there are 1000 rows in the dataset
    For i = 1 To LastRow 'Loop through each row and check for required condition
    'To hide all the rows with the text data
    If IsNumeric(Range("C" & i)) = False Then Rows(i).EntireRow.Hidden = True
    Next
End Sub

Your code is now ready to run.

VBA to Hide Rows Based on Cell All Text Value in Excel

Here,

  • IsNumeric(Range(“C” & i)) = The data in our dataset starts from column C, so we passed C inside the Range method.
  • After that, Run this piece of code and the result is shown in the image below.

Finally, you will notice all the rows where cells contain text values are now hidden from the dataset.

VBA Code Explanation

Sub HideAllRowsContainsText()

First, provide a name for the sub-procedure of the macro.

LastRow = 1000

Then, let’s assume there are 1000 rows in the dataset.

For i = 1 To LastRow

Next, start looping through each row from the first row to the last row and check for the required condition.

If IsNumeric(Range("C" & i)) = False Then Rows(i).EntireRow.Hidden = True

If there are non-numeric values in Column C then hide all the rows from that column that hold the numeric values. Our data starts from Column C, that’s why we pass Column C as the parameter. You must modify this line according to your dataset.

Next

To continue the FOR Loop till it reaches all the rows.

End Sub

Finally, end the sub-procedure of the macro.

Read More: Hide Duplicate Rows Based on One Column in Excel (4 Methods)


4. Insert VBA to Hide All Rows Where Cells Contain Numbers in Excel

If you want to hide all rows where cells contain numeric values only with VBA code, then follow the steps provided below.

Steps:

  • At first, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • Then, copy the following code and paste it into the code window.
Sub HideAllRowsContainsNumbers()
    LastRow = 1000 'Let's assume there are 1000 rows in the dataset
    For i = 4 To LastRow 'Loop through each row and check for required condition
    'We set i = 4 because our data starts from row 4
    'To hide all the rows with the numeric data
    If IsNumeric(Range("C" & i)) = True Then Rows(i).EntireRow.Hidden = True
    Next
End Sub

Your code is now ready to run.

VBA to Hide Rows Based on Cell Numbers Value in Excel

Here,

  • IsNumeric(Range(“C” & i)) = The data in our dataset starts from column C, so we passed C inside the Range method.
  • Lastly, Run this code and notice the image below.

Subsequently, all the rows where cells contain numeric values are now hidden.

VBA Code Explanation

Sub HideAllRowsContainsNumbers()

First, provide a name for the sub-procedure of the macro.

LastRow = 1000

Then, let’s assume there are 1000 rows in the dataset.

For i = 4 To LastRow

Next, start looping through each row from the first row to the last row and check for the required condition. We set i = 4 because our data starts from row 4. You must modify this line according to your dataset.

If IsNumeric(Range("C" & i)) = True Then Rows(i).EntireRow.Hidden = True

If there are numeric values in Column C then hide all the rows from that column that hold the numeric values. Our data starts from Column C, that’s why we pass Column C as the parameter. You must modify this line according to your dataset.

Next

To continue the FOR Loop till it reaches all the rows.

End Sub

Finally, end the sub-procedure of the macro.

Read More: How to Hide Blank Rows in Excel VBA (4 Useful Methods)


5. Embed Macro to Hide Rows Based on Zero (0) as Cell Value

Suppose you want to hide only the rows from a specific column where a particular cell is holding 0 (zero). Look at the following dataset where the E column holds 0 in Cell E7 of row 7 and 82 in Cell E10 of row 10.

Dataset for VBA to Hide Rows Based on Cell Value Zero in Excel

We will learn how to hide only the row that is holding 0 (row 7) with VBA in Excel.

Steps:

  • First, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • Second, copy the following code and paste it into the code window.
Sub HideRowContainsZero()
    LastRow = 1000 'Let's assume there are 1000 rows in the dataset
    For i = 4 To LastRow 'Loop through each row and check for required condition
    'We set i = 4 because our data starts from row 4
        'To hide the row containing 0 in E column
        If Range("E" & i) = 0 Then Rows(i).EntireRow.Hidden = True
    Next
End Sub

Your code is now ready to run.

VBA to Hide Rows Based on Zero as Cell Value in Excel

  • Third, Run this code, and let’s see what happened as the result.

Result of VBA to Hide Rows Based on Zero as Cell Value in Excel

As a result, the row (row 7) that was containing 0 in Cell E7 from column E is now hidden whereas row 10 which is carrying 82 in Cell E10 is unhidden.

VBA Code Explanation

Sub HideRowContainsZero()

First, provide a name for the sub-procedure of the macro.

LastRow = 1000

Then, let’s assume there are 1000 rows in the dataset.

For i = 4 To LastRow

Next, start looping through each row from the first row to the last row and check for the required condition. We set i = 4 because our data starts from row 4. You must modify this line according to your dataset.

If Range("E" & i) = 0 Then Rows(i).EntireRow.Hidden = True

If Column E holds 0, then hide all the rows from that column that hold the value. You can modify this line by passing the column address according to your dataset.

Next

To continue the FOR Loop till it reaches all the rows.

End Sub

Finally, end the sub-procedure of the macro.

Read More: Formula to Hide Rows in Excel (7 Methods)


6. Implement VBA to Hide Rows Based on Cell’s Negative Value

Just as you can hide rows that contain zero from the same column, you can hide rows where cells are carrying negative values as well. With the dataset shown below where column E contains both negative and positive values in Cell E7 and E10 respectively, we will see the code that hides only the negative one.

Dataset for VBA to Hide Rows Based on Negative Cell Value in Excel

Steps:

  • Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • Then, copy the following code and paste it into the code window.
Sub HideRowContainsNegative()
    LastRow = 1000 'Let's assume there are 1000 rows in the dataset
    For i = 4 To LastRow 'Loop through each row and check for required condition
    'We set i = 4 because our data starts from row 4
        'To hide the rows containing negative values in E column
        If IsNumeric(Range("E" & i)) = True Then
            If Range("E" & i) < 0 Then Rows(i).EntireRow.Hidden = True
        End If
    Next
End Sub

Your code is now ready to run.

VBA to Hide Rows Based on Negative Cell Value in Excel

  • Next, Run this piece of code.

Result of VBA to Hide Rows Based on Negative Cell Value in Excel

As you can see from the image above, row 7 which was containing the negative value (-10) in Cell E7 from column E is now hidden whereas row 10 which is carrying 82 in Cell E10 is unhidden.

VBA Code Explanation

Sub HideRowContainsNegative()

First, provide a name for the sub-procedure of the macro.

LastRow = 1000

Then, let’s assume there are 1000 rows in the dataset.

For i = 4 To LastRow

Next, start looping through each row from the first row to the last row and check for the required condition. We set i = 4 because our data starts from row 4. You must modify this line according to your dataset.

If IsNumeric(Range("E" & i)) = True Then
    If Range("E" & i) < 0 Then Rows(i).EntireRow.Hidden = True
End If

If Column E holds numeric values which are less than 0, that means which are negative values, then hide the entire rows that carry negative values from that column.

Next

To continue the FOR Loop till it reaches all the rows.

End Sub

Finally, end the sub-procedure of the macro.

Read More: VBA to Hide Rows in Excel (14 Methods)


7. Insert VBA Macro to Hide Rows Based on Cell’s Positive Value

This time with the dataset given below where column E contains both zero and positive values in Cell E10 and E7 respectively, we will see the VBA code that hides only the positive one.

Dataset for VBA to Hide Rows Based on Positive Cell Value in Excel

Steps:

  • Firstly, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • Secondly, copy the following code and paste it into the code window.
Sub HideRowContainsPositive()
    LastRow = 1000 'Let's assume there are 1000 rows in the dataset
    For i = 4 To LastRow 'Loop through each row and check for required condition
    'We set i = 4 because our data starts from row 4
        'To hide the rows containing positive values in E column
        If IsNumeric(Range("E" & i)) = True Then
            If Range("E" & i) > 0 Then Rows(i).EntireRow.Hidden = True
        End If
    Next
End Sub

Your code is now ready to run.

VBA to Hide Rows Based on Positive Cell Value in Excel

  • After that, Run this code and the result is shown in the following image.

Result of VBA to Hide Rows Based on Positive Cell Value in Excel

Finally, row 7 which was containing the positive value (55) in Cell E7 of column E is now hidden whereas row 10 which is carrying 0 in Cell E10 is unhidden.

VBA Code Explanation

Sub HideRowContainsPositive()

First, provide a name for the sub-procedure of the macro.

LastRow = 1000

Then, let’s assume there are 1000 rows in the dataset.

For i = 4 To LastRow

Next, start looping through each row from the first row to the last row and check for the required condition. We set i = 4 because our data starts from row 4. You must modify this line according to your dataset.

If IsNumeric(Range("E" & i)) = True Then
    If Range("E" & i) > 0 Then Rows(i).EntireRow.Hidden = True
End If

If Column E holds numeric values which are greater than 0, that means which are positive values, then hide the entire rows that carry positive values from that column.

Next

To continue the FOR Loop till it reaches all the rows.

End Sub

Finally, end the sub-procedure of the macro.

Read More: Unhide All Rows Not Working in Excel (5 Issues & Solutions)


8. Apply Macro to Secrete Rows Where Cells Contain Odd Numbers

You can hide rows that have odd numbers as their cell value. In the example that we showed at the beginning of this article, column E is holding both odd and even numbers in Cell E7 of row 7 and in Cell E10 of row 10. We will learn the code of hiding the row (row 7) that holds odd numbers only as the cell value.

Steps:

  • In the beginning, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • Then, copy the following code and paste it into the code window.
Sub HideRowContainsOdd()
    LastRow = 1000 'Let's assume there are 1000 rows in the dataset
    For i = 4 To LastRow 'Loop through each row and check for required condition
    'We set i = 4 because our data starts from row 4
        'To hide the rows containing odd values in E column
        If IsNumeric(Range("E" & i)) = True Then
            If Range("E" & i) Mod 2 = 1 Then Rows(i).EntireRow.Hidden = True
        End If
    Next
End Sub

Your code is now ready to run.

VBA to Hide Rows Based on Odd Cell Value in Excel

  • Later, Run this piece of code.

Finally, only row 7 which was containing the odd number (55) in Cell E7 from column E is now hidden whereas row 10 which is carrying the even number (82) in Cell E10 is unhidden.

VBA Code Explanation

Sub HideRowContainsOdd()

First, provide a name for the sub-procedure of the macro.

LastRow = 1000

Then, let’s assume there are 1000 rows in the dataset.

For i = 4 To LastRow

Next, start looping through each row from the first row to the last row and check for the required condition. We set i = 4 because our data starts from row 4. You must modify this line according to your dataset.

If IsNumeric(Range("E" & i)) = True Then
    If Range("E" & i) Mod 2 = 1 Then Rows(i).EntireRow.Hidden = True
End If

If Column E holds numeric values which produce 1 as the remainder when divided by 2, that means which are odd values, then hide the entire rows that carry odd numbers from that column.

Next

To continue the FOR Loop till it reaches all the rows.

End Sub

Finally, end the sub-procedure of the macro.

Read More: [Fixed!] Excel Rows Not Showing but Not Hidden (3 Reasons & Solutions)


9. Implement VBA to Cloak Rows Where Cells Contain Even Numbers

Similarly, you can hide rows that have even numbers as their value. In the example that we showed at the beginning of this article, column F is holding both odd and even numbers in Cell F7 of row 7 and in Cell F10 of row 10. We will learn the code of hiding the row (row 10) that holds even numbers only as the cell value.

Steps:

  • First, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • Second, copy the following code and paste it into the code window.
Sub HideRowContainsEven()
    LastRow = 1000 'Let's assume there are 1000 rows in the dataset
    For i = 4 To LastRow 'Loop through each row and check for required condition
    'We set i = 4 because our data starts from row 4
        'To hide the rows containing even values in F column
        If IsNumeric(Range("F" & i)) = True Then
            If Range("F" & i) Mod 2 = 0 Then Rows(i).EntireRow.Hidden = True
        End If
    Next
End Sub

Your code is now ready to run.

VBA to Hide Rows Based on Even Cell Value in Excel

  • Third, Run this code and see the result in the following image.

Lastly, only row 10 which was containing the even number (75) in Cell F10 from column F is now hidden whereas row 7 which is carrying the even number (100) in Cell F7 is unhidden.

VBA Code Explanation

Sub HideRowContainsEven()

First, provide a name for the sub-procedure of the macro.

LastRow = 1000

Then, let’s assume there are 1000 rows in the dataset.

For i = 4 To LastRow

Next, start looping through each row from the first row to the last row and check for the required condition. We set i = 4 because our data starts from row 4. You must modify this line according to your dataset.

If IsNumeric(Range("F" & i)) = True Then
    If Range("F" & i) Mod 2 = 0 Then Rows(i).EntireRow.Hidden = True
End If

If Column F holds numeric values which produce 0 as the remainder when divided by 2, that means which are even values, then hide the entire rows that carry even numbers from that column.

Next

To continue the FOR Loop till it reaches all the rows.

End Sub

Finally, end the sub-procedure of the macro.


10. Insert VBA to Hide Rows Where Cell Value Is Greater Than a Specific Condition

You can hide rows where the cells are carrying values that are greater than a specific value with VBA Excel. Suppose you want to hide the rows from column E where the cell value is greater than 80. Here’s how to do that with VBA macro.

Steps:

  • At first, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • Then, copy the following code and paste it into the code window.
Sub HideRowContainsGreater()
    LastRow = 1000 'Let's assume there are 1000 rows in the dataset
    For i = 4 To LastRow 'Loop through each row and check for required condition
    'We set i = 4 because our data starts from row 4
        'To hide the rows containing values greater than 80 in E column
        If IsNumeric(Range("E" & i)) = True Then
            If Range("E" & i) > 80 Then Rows(i).EntireRow.Hidden = True
        End If
    Next
End Sub

Your code is now ready to run.

VBA to Hide Rows Based on Cell Value Greater than condition in Excel

  • Later, Run this code.

As a result, only row 10 which was containing 82 (which is greater than 80) in Cell E10 from column E is now hidden whereas row 7 which is carrying 55 in Cell E7 is unhidden.

VBA Code Explanation

Sub HideRowContainsGreater()

First, provide a name for the sub-procedure of the macro.

LastRow = 1000

Then, let’s assume there are 1000 rows in the dataset.

For i = 4 To LastRow

Next, start looping through each row from the first row to the last row and check for the required condition. We set i = 4 because our data starts from row 4. You must modify this line according to your dataset.

If IsNumeric(Range("E" & i)) = True Then
    If Range("E" & i) > 80 Then Rows(i).EntireRow.Hidden = True
End If

If Column E holds numeric values that are greater than 80, then hide the entire rows that carry those numbers from that column.

Next

To continue the FOR Loop till it reaches all the rows.

End Sub

Finally, end the sub-procedure of the macro.

Read More: How to Unhide All Rows in Excel (All Possible Ways)


11. Embed Macro to Conceal Rows Where Cell Value Is Less Than a Specific Condition

You can also hide rows where cells are holding values that are less than a specific value with VBA Excel. Suppose you want to hide the rows from column E where the cell value is less than 80. Here’s how to do that with VBA.

Steps:

  • Firstly, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • Secondly, copy the following code and paste it into the code window.
Sub HideRowContainsLess()
    LastRow = 1000 'Let's assume there are 1000 rows in the dataset
    For i = 4 To LastRow 'Loop through each row and check for required condition
    'We set i = 4 because our data starts from row 4
        'To hide the rows containing values less than 80 in E column
        If IsNumeric(Range("E" & i)) = True Then
            If Range("E" & i) < 80 Then Rows(i).EntireRow.Hidden = True
        End If
    Next
End Sub

Your code is now ready to run.

VBA to Hide Rows Based on Cell Value less than condition in Excel

  • Thirdly, Run this piece of code. The result is shown in the image below.

As a result of the successful code execution, only row 7 which was containing 55 (which is less than 80) in Cell E7 from column E is now hidden whereas row 10 which is carrying 82 in Cell E10 is unhidden.

VBA Code Explanation

Sub HideRowContainsLess()

First, provide a name for the sub-procedure of the macro.

LastRow = 1000

Then, let’s assume there are 1000 rows in the dataset.

For i = 4 To LastRow

Next, start looping through each row from the first row to the last row and check for the required condition. We set i = 4 because our data starts from row 4. You must modify this line according to your dataset.

If IsNumeric(Range("E" & i)) = True Then
    If Range("E" & i) < 80 Then Rows(i).EntireRow.Hidden = True
End If

If Column E holds numeric values that are less than 80, then hide the entire rows that carry those numbers from that column.

Next

To continue the FOR Loop till it reaches all the rows.

End Sub

Finally, end the sub-procedure of the macro.


12. Implement VBA to Hide Rows Based on Inserted Condition in Cell

In this section, you will learn how to hide rows based on the criteria that you provide in a specified cell in the working sheet in Excel. Consider the following image. We will hide the row based on the value inserted in Cell E12.

Steps to execute that are given below.

Steps:

  • At first, right-click on the worksheet of interest. A list of options will appear.
  • From the appeared option list, select View Code.

  • You will be redirected to the code window of the specific worksheet. Copy the following code and paste it into the code window.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    StartRow = 4
    LastRow = 10
    iCol = 5
    For i = StartRow To lastRow
        If Cells(i, iCol).Value = Range("E12").Value Then
            Cells(i, iCol).EntireRow.Hidden = True
    Else
        Cells(i, iCol).EntireRow.Hidden = False
        End If
    Next i
End Sub

Your code is now ready to run.

VBA to Hide Rows Based on Inserted Cell Value in Excel

Here,

  • StartRow = 4 -> First row of the dataset.
  • LastRow = 10 -> Last row of the dataset.
  • iCol = 5 -> The column address that holds the value; based on which we will hide the row.
  • Don’t run this code, save it.
  • Now, go back to the worksheet of interest. Insert any value from Column E in Cell E12. You will see that the whole row that contains the value will be hidden from that worksheet.

As you can see from the gif above, we inserted “Biology” in Cell E12 and as a result, the whole row 6 is now hidden.

VBA Code Explanation

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

First, initiating an event under the specific worksheet where Target is passed as an argument with Range type. When the Range changes, the event occurs.

StartRow = 4

Then, declare the first row of the dataset. We set StartRow = 4 because our data starts from row 4. You must modify this line according to your dataset.

LastRow = 10

Next, declare the last row of the dataset. We set LastRow = 10 because our data ends in row 10. You must modify this line according to your dataset.

iCol = 5

After that, declare the column address that holds the text value. We will hide rows based on the value residing in Column E, so we set iCol = 5. You must modify this line according to your dataset.

For i = StartRow To lastRow
    If Cells(i, iCol).Value = Range("E12").Value Then
        Cells(i, iCol).EntireRow.Hidden = True
    Else
        Cells(i, iCol).EntireRow.Hidden = False
    End If
Next i

Now, starts looping from the declared first row (4) to the declared last row (10). If the iteration variable i finds any value in Cell E12 and if the value matches with any value in the declared column (E), then hides the entire rows. It continues iterating until it reaches all the rows from that column.

End Sub

Finally, end the sub-procedure of the macro.

Read More: How to Hide and Unhide Rows in Excel (6 Easiest Ways)


13. Embed VBA to Hide Rows Based on User-Input Cell Value in Excel

Look at the following image. What we are going to learn in this section is that if we insert 1 in Cell E12 then rows from 4 to 5 will be hidden, if we insert 2 then rows from 6 to 8 will be hidden and if we insert 3 in Cell E12 then rows from 9 to 10 will be hidden.

Let’s see how to do that with VBA in Excel.

Steps:

  • At first, right-click on the worksheet of interest. A list of options will appear.
  • From the appeared option list, select View Code.

  • You will be redirected to the code window of the specific worksheet. Copy the following code and paste it into the code window.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
i = Range("E12").Value
Select Case i
Case 1: Rows("4:5").EntireRow.Hidden = False
Rows("4:5").EntireRow.Hidden = True
Case 2: Rows("6:8").EntireRow.Hidden = False
Rows("6:8").EntireRow.Hidden = True
Case 3: Rows("9:10").EntireRow.Hidden = False
Rows("9:10").EntireRow.Hidden = True
Case Else
Rows("4:10").EntireRow.Hidden = False
End Select
End Sub

Your code is now ready to run.

VBA to Hide Rows Based on User Input Cell Value in Excel

  • Don’t run this code, save it.
  • Now, go back to the worksheet of interest. Insert any value from 1, 2 or 3 in Cell E12. You will see that the rows are automatically hiding according to the inserted cell value.

Result of VBA to Hide Rows Based on User Input Cell Value in Excel

As you can see from the gif above, when we insert 1, rows from 4 to 5 are being hidden; when we insert 2, rows from 6 to 8 are being hidden and when we insert 3, rows from 9 to 10 are being hidden.

VBA Code Explanation

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

First, initiating an event under the specific worksheet where Target is passed as an argument with Range type. When the Range changes, the event occurs.

i = Range("E12").Value

Then, store the cell address – E12 – where we will insert values in a variable.

Select Case i
Case 1: Rows("4:5").EntireRow.Hidden = False
Rows("4:5").EntireRow.Hidden = True
Case 2: Rows("6:8").EntireRow.Hidden = False
Rows("6:8").EntireRow.Hidden = True
Case 3: Rows("9:10").EntireRow.Hidden = False
Rows("9:10").EntireRow.Hidden = True
Case Else
Rows("4:10").EntireRow.Hidden = False
End Select

Next, initiate the Select Case Statement. If the user inserts 1 in Cell E12, Case 1 executes – hides row from 4 to 5; If the user inserts 2 in Cell E12, Case 2 executes – hides row from 6 to 8 and if the user inserts 3 in Cell E12, Case 3 executes – hides row from 9 to 10. Otherwise, rows 4 to 10 stay unhidden.

End Sub

Finally, end the sub-procedure of the macro.


14. Apply VBA to Hide Rows If Cell Value Is Changed in Excel

In this section, you will learn how to hide rows when you update the value of a cell. We will see how to hide all the rows if we change the value in cell C4 from “John” to 0 with VBA in Excel with the dataset we introduced at the beginning of this article.

Steps:

  • At first, right-click on the worksheet of interest. A list of options will appear.
  • From the appeared option list, select View Code.

  • You will be redirected to the code window of the specific worksheet. Copy the following code and paste it into the code window.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim iCell As Range: Set iCell = Me.Range("C4")
    If Intersect(iCell, Target) Is Nothing Then Exit Sub
    If IsNumeric(iCell.Value) Then
        RowHide iCell
    End If
End Sub
Sub RowHide(ByVal SourceCell As Range)
    If SourceCell.Value = 0 Then
        SourceCell.Worksheet.Rows("4:10").Hidden = True
    Else
        SourceCell.Worksheet.Rows("4:10").Hidden = False
    End If
End Sub

Your code is now ready to run.

VBA to Hide Rows Based on Changed Cell Value in Excel

  • Don’t run this code, save it.
  • Now, go back to the worksheet of interest. Insert 0 in Cell C4. You will see that all the rows will be hidden from that worksheet.

Result of VBA to Hide Rows Based on Changed Cell Value in Excel

As you can see from the gif above, when we modified the cell value from John” to 0 in Cell C4, all the rows become hidden from that worksheet.

VBA Code Explanation

Option Explicit

First, Forces to declare all the variables explicitly of the file.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Then, initiating an event under the specific worksheet where Target is passed as an argument with Range type. When the Range changes, the event occurs.

Dim iCell As Range: Set iCell = Me.Range("C4")

Next, declare a variable and store Cell C4 – the cell whose value will be changed – in that variable.

If Intersect(iCell, Target) Is Nothing Then Exit Sub
  If IsNumeric(iCell.Value) Then
    RowHide iCell
  End If

To execute the macro according to the RowHide sub-procedure coded below.

End Sub

End the sub-procedure of the event.

Sub RowHide(ByVal SourceCell As Range)

Initiate the sub-procedure of the RowHide macro. Variable SourceCell is passed as a Range type argument.

If SourceCell.Value = 0 Then
    SourceCell.Worksheet.Rows("4:10").Hidden = True
Else
    SourceCell.Worksheet.Rows("4:10").Hidden = False
End If

If the value inserted through the SourceCell variable in Cell C4 is 0, then hide rows 4 to 10. Otherwise, keep the rows from 4 to 10 unhidden.

End Sub

Finally, end the sub-procedure of the macro.

Read More: Hidden Rows in Excel: How to Unhide or Delete Them?


Conclusion

This article showed you 14 different examples of how to hide rows based on cell value in Excel with VBA. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.


Related Articles

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

2 Comments
  1. Dear, This is a very interesting article.
    In my case I want to do the reverse of the options shown.
    I want to enter e.g. a “Text Value” in a certain field and this should make all rows disappear that don’t match with the “Text Value” in a specific Column.
    Can you help me.
    With kind regards,
    Gert Renkin

    • Hello, GERT RENKIN!
      Try this code to hide rows except matching values. Hope this will help you!

      Sub Hide_Rows()
      Dim rng As Long
      With Sheets(“Sheet1”)
      For rng = 1 To 8
      If Cells(5, 1).Value <> Cells(rng, 1).Value Then
      .Rows(rng).EntireRow.Hidden = True
      End If
      Next rng
      End With
      End Sub

Leave a reply

ExcelDemy
Logo