Home » Macros & Excel VBA » VBA to Hide Rows Based on Criteria in Excel (15 Useful Examples)

VBA to Hide Rows Based on Criteria in Excel (15 Useful Examples)

Sanjida Ahmed Sanjida Ahmed Feb 13, 2023 0
Get FREE Advanced Excel Exercises with Solutions!

Sometimes we need to hide some specific rows based on various criteria from 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 criteria in Excel with the VBA macro.

Table of Contents hide
Download Workbook
15 Methods to Hide Rows Based on Criteria with VBA in Excel
Criteria 1: Embed VBA to Hide a Single Row in Excel
Criteria 2: Insert VBA to Hide Contiguous Rows in Excel
Criteria 3: Embed VBA to Hide Non-Contiguous Rows in Excel
Criteria 4: Insert VBA to Hide All Rows Containing Texts in Excel
Criteria 5: Embed Macro to Cloak All Rows Containing Numbers in Excel
Criteria 6: Implement Macro to Hide Rows Containing Zero (0) in Excel
Criteria 7: Implement VBA Macro to Hide Rows Holding Negative Values
Criteria 8: Embed VBA to Conceal Rows Containing Positive Values
Criteria 9: Macro to Hide Rows That Contain Odd Numbers in Excel
Criteria 10: VBA to Hide Rows Containing Even Numbers in Excel
Criteria 11: Insert VBA to Hide Rows That Are Greater Than a Specific Condition in Excel
Criteria 12: Embed VBA to Hide Rows That Are Less Than a Specific Condition in Excel
Criteria 13: Macro to Hide Rows Based on Cell Text Value in Excel
Criteria 14: VBA Macro to Hide Rows Based on Cell Numeric Value in Excel
Criteria 15: Implement Macro to Hide Rows Based on Inserted Condition in Worksheet
Conclusion
Related Articles

Download Workbook

You can download the free practice Excel workbook from here.

15 Methods to Hide Rows Based on Criteria with VBA in Excel

In this section, we will discuss 15 different methods with VBA to hide rows based on different criteria in Excel.

Dataset for VBA to Hide Rows Based on Criteria in Excel

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

Criteria 1: Embed VBA to Hide a Single Row in Excel

If you want to hide a single row with VBA code, then follow the steps discussed below. In our case, we will hide row number 5 (Last Name) from our dataset.

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 HideSingleRow()
    Worksheets("Single").Range("5:5").EntireRow.Hidden = True
End Sub

Your code is now ready to run.

VBA to Hide Rows Based on Single Row Criteria in Excel

Here,

  • Worksheets(“Single”) = Set the worksheet name.
  • Range(“5:5”) = Pass row number 5 inside the Range method.
  • 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.

Finally, row number 5 is hidden after successfully executing the VBA code.

VBA Code Explanation

Sub HideSingleRow()

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

Worksheets("Single").Range("5:5").EntireRow.Hidden = True

Then, hide the entire 5th row from the worksheet named “Single“.

End Sub

Finally, end the sub-procedure of the macro.

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

Criteria 2: Insert VBA to Hide Contiguous Rows in Excel

You have learnt how to hide a single row with VBA. But suppose, you want to hide a range of rows which are contiguous. You can do that too with VBA in Excel. We will hide rows number 5 to 7 from our dataset shown above.

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 HideContiguousRows()
    Worksheets("Contiguous").Range("5:7").EntireRow.Hidden = True
End Sub

Your code is now ready to run.

VBA to Hide Rows Based on Contiguous Row Criteria in Excel

Here,

  • Worksheets(“Contiguous”) = Set the worksheet name.
  • Range(“5:7”) = Pass row number 5 to 7 inside the Range method.
  • After that, Run the macro as we showed you in the above section. The result is shown in the image below.

You can see from the above image that after successful code execution, rows 5 to 7 are hidden now.

VBA Code Explanation

Sub HideContiguousRows()

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

Worksheets("Contiguous").Range("5:7").EntireRow.Hidden = True

Then, hide entire rows from the 5th row to the 7th row from the worksheet named “Contiguous“.

End Sub

Finally, end the sub-procedure of the macro.

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

Similar Readings

Criteria 3: Embed VBA to Hide Non-Contiguous Rows in Excel

This time you will learn how to hide rows in Excel that are non-contiguous with VBA. With our dataset, the code to hide rows 5, 6, 8 and 9 are given below.

Steps:

  • As previously shown, 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 HideNonContiguousRows()
    Worksheets("Non-Contiguous").Range("5:6, 8:9").EntireRow.Hidden = True
End Sub

Your code is now ready to run.

VBA to Hide Rows Based on Non Contiguous Row Criteria in Excel

Here,

  • Worksheets(“Non-Contiguous”) = Set the worksheet name.
  • Range(“5:6, 8:9”) = Pass row number 5 to 6 and 8 to 9 inside the Range method.
  • After that, Run this piece of code and the result is shown in the following image.

As a result, rows 5 to 6 and 8 to 9 are now hidden after successful code execution.

VBA Code Explanation

Sub HideNonContiguousRows()

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

Worksheets("Non-Contiguous").Range("5:6, 8:9").EntireRow.Hidden = True

Then, hide the entire 5th, 6th, 8th and 9th rows from the worksheet named “Non-Contiguous“.

End Sub

Finally, end the sub-procedure of the macro.

Criteria 4: Insert VBA to Hide All Rows Containing Texts in Excel

If you want to hide all rows that are containing text values, 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 All Texts Criteria 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 code.

Lastly, you will notice all the rows that were containing 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.

Criteria 5: Embed Macro to Cloak All Rows Containing Numbers in Excel

And if you want to hide all rows that are containing numeric values 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 All Numbers Criteria 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.

Consequently, all the rows that were containing numeric values before 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)

Criteria 6: Implement Macro to Hide Rows Containing Zero (0) in Excel

Suppose you want to hide only the rows from a specific column that are holding 0 (zero). Look at the following dataset where the E column holds 0 in row 7 and 82 in row 10. We will learn how to hide only the row that is holding 0 (row 7) with VBA in Excel.

Dataset for VBA to Hide Rows Based on Zeros Criteria 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 Zeros Criteria in Excel

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

As a result, the row (row 7) that were containing 0 in column E is now hidden whereas row 10 which is carrying 82 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.

Criteria 7: Implement VBA Macro to Hide Rows Holding Negative Values

Just as you can hide rows that contain zero from the same column, you can hide rows that hold negative values as well. With the dataset shown below where column E contains both negative and positive values, we will see the VBA code that hides only the negative ones.

Dataset for VBA to Hide Rows Based on Negative Value Criteria 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 Value Criteria in Excel

  • Next, Run this piece of code.

As you can see from the image above, row 7 which was containing the negative value (-10) in column E is now hidden whereas row 10 which is carrying 82 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: Hide Duplicate Rows Based on One Column in Excel (4 Methods)

Criteria 8: Embed VBA to Conceal Rows Containing Positive Values

This time with the dataset given below where column E contains both zero and positive values, we will see the VBA code that hides only the positive ones.

Dataset for VBA to Hide Rows Based on Positive Value Criteria 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 Value Criteria in Excel

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

Finally, row 7 which was containing the positive value (55) in column E is now hidden whereas row 10 which is carrying 0 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.

Criteria 9: Macro to Hide Rows That Contain Odd Numbers in Excel

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

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 Number Criteria in Excel

  • Later, Run this piece of code.

Finally, only row 7 which was containing the odd number (55) in column E is now hidden whereas row 10 which is carrying the even number (82) 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.

Criteria 10: VBA to Hide Rows Containing Even Numbers in Excel

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 rows 7 and 10. We will learn the code of hiding the row that holds even numbers only.

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 Number Criteria 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 column F is now hidden whereas row 7 which is carrying the even number (100) 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.

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

Criteria 11: Insert VBA to Hide Rows That Are Greater Than a Specific Condition in Excel

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

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 Greater Than Criteria in Excel

  • Later, Run this code.

As a result, only row 10 which was containing 82 (which is greater than 80) in column E is now hidden whereas row 7 which is carrying 55 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: Hide Rows Based on Cell Value with Conditional Formatting in Excel

Criteria 12: Embed VBA to Hide Rows That Are Less Than a Specific Condition in Excel

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

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 Less Than Criteria 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 column E is now hidden whereas row 10 which is carrying 82 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.

Read More: How to Hide Rows Based on Cell Value in Excel (5 Methods)

Criteria 13: Macro to Hide Rows Based on Cell Text Value in Excel

Suppose you want to hide a row that holds a specific text value. In our case, we will give you an example with the row that contains the word “Chemistry”, row 6 and provide you with the code on how to hide that row.

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 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 Text Value Criteria 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.
  • Next, Run this code and the result is shown in the image below.

Subsequently, row number 6 which consists of the word “Chemistry” 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: Excel Macro: Hide Rows Based on Cell Text in Excel (3 Simple Methods)

Criteria 14: VBA 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”, row 7 and provide you with the code on how to hide that row.

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 Numeric Value Criteria 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.

Finally, row number 7 which consists of the numeric value “87” 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: VBA to Hide Rows Based on Cell Value in Excel (14 Examples)

Criteria 15: Implement Macro to Hide Rows Based on Inserted Condition in Worksheet

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

Dataset for VBA to Hide Rows Based on Inserted Criteria in Excel

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 Criteria 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.

Conclusion

This article showed you 15 different methods on how to hide rows based on criteria 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

Tags:

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.

Related Articles
We will be happy to hear your thoughts

Leave a reply

About ExcelDemy.com

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

Contact | Privacy Policy | TOS

Affiliate Disclosure:

ExcelDemy.com is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program. I earn a small commission if you buy any products using my affiliate links to Amazon.

DMCA.com Protection Status

Join ExcelDemy Forum

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo