VBA to Hide Rows in Excel (14 Methods)

Sometimes we need to hide some specific rows when using 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 in Excel based on different criteria utilizing the VBA macro.


Download Workbook

You can download the free practice Excel workbook from here.


14 Methods with VBA to Hide Rows in Excel

In this section, we will discuss 12 different methods with VBA to hide rows in Excel. The example that we will be using throughout the whole article is shown below.

Dataset of Hide row with VBA in Excel

1. Embed VBA to Hide 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:

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

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

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

Here,

  • Worksheets(“Single”) = Set the worksheet name.
  • Range(“5:5”) = Pass row number 5 inside the Range method.

VBA to Hide Single Row in Excel

  • Press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

Notice in the image below, row number 5 is hidden after executing the VBA code.

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


2. Insert Macro 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 that 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.
  • In the code window, copy the following code and paste it.
Sub HideContiguousRows()
    Worksheets("Contiguous").Range("5:7").EntireRow.Hidden = True
End Sub

Your code is now ready to run.

Here,

  • Worksheets(“Contiguous”) = Set the worksheet name.
  • Range(“5:7”) = Pass row number 5 to 7 inside the Range method.

VBA to Hide Contiguous Rows in Excel

  • Run this code and see in the following picture that rows 5 to 7 are hidden now.

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


3. Embed Macro to Secrete Non-Contiguous Rows

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.
  • In the code window, copy the following code and paste it.
Sub HideNonContiguousRows()
    Worksheets("Non-Contiguous").Range("5:6, 8:9").EntireRow.Hidden = True
End Sub

Your code is now ready to run.

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.

VBA to Hide Non-Contiguous Rows in Excel

  • Run this piece of code and see that rows 5 to 6 and 8 to 9 are hidden in the following picture.

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


4. Macro 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:

  • Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • In the code window, copy the following code and paste it.
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.

Here,

  • IsNumeric(Range(“C” & i)) = The data in our dataset starts from column C, so we passed C inside the Range method.

VBA Macro to Hide All Rows Containing Texts in Excel

  • Run this code and you will notice all the rows that were containing text values are now hidden.

Read More: How to Unhide Multiple Rows in Excel (9 Methods)


5. 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:

  • Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • In the code window, copy the following code and paste it.
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.

Here,

  • IsNumeric(Range(“C” & i)) = The data in our dataset starts from column C, so we passed C inside the Range method.

VBA to Hide All Rows Containing Numbers in Excel

  • Run this code and notice that all the rows that were containing numeric values before are now hidden.

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


6. 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 of VBA to Hide Rows Containing Zero (0) in Excel

Steps:

  • Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • In the code window, copy the following code and paste it.
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 Containing Zero (0) in Excel

  • Run this code and notice that the row (row 7) that were containing 0 in column E is now hidden whereas row 10 that is carrying 82 is unhidden.

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


7. Implement Macro to Hide Rows Holding Negative Values in Excel

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 code that hides only the negative one.

Dataset of VBA Macro to Hide Rows Holding Negative Values in Excel

Steps:

  • Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • In the code window, copy the following code and paste it.
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 row 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 Macro to Hide Rows Holding Negative Values in Excel

  • Run this code and notice that row 7 which was containing a negative value (-10) in column E is now hidden whereas row 10 that is carrying 82 is unhidden.


8. Embed VBA to Conceal Rows Containing Positive Values in Excel

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

Dataset of VBA to Hide Rows Containing Positive Values in Excel

Steps:

  • Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • In the code window, copy the following code and paste it.
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 row 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 Containing Positive Values in Excel

  • Run this code and notice that row 7 which was containing a positive value (55) in column E is now hidden whereas row 10 that is carrying 0 is unhidden.

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


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:

  • Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • In the code window, copy the following code and paste it.
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 row containing positive 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 Macro to Hide Rows that Contain Odd Numbers in Excel

  • Run this piece of code and see that only row 7 which was containing an odd number (55) in column E is now hidden whereas row 10 that is carrying an even number (82) is unhidden.


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:

  • Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • In the code window, copy the following code and paste it.
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 row containing positive 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 Macro to Hide Rows that Contain Even Numbers in Excel

  • Run this code and see that only row 7 which was containing an even number (100) in column F is now hidden whereas row 10 that is carrying an odd number (75) is unhidden.

Read More: Excel VBA: Unhide All Rows in Excel (5 Practical Examples)


11. Insert Macro to Hide Rows that is Greater Than a Specific Condition

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:

  • Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • In the code window, copy the following code and paste it.
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 row containing positive values 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 Macro to Hide Rows that is Greater Than a Specific Condition in Excel

  • Run this code and see that only row 10 which was containing 82 (which is greater than 80) in column E is now hidden whereas row 7 that is carrying 55 is unhidden.

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


12. Embed Macro to Cover Rows that is 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:

  • Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • In the code window, copy the following code and paste it.
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 row containing positive values 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 Macro to Hide Rows that is Less Than a Specific Condition in Excel

  • Run this code and see that only row 7 which was containing 55 (which is less than 80) in column E is now hidden whereas row 10 that is carrying 82 is unhidden.


13. Conceal Rows Based on Cell Text Value with Macro

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:

  • Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • In the code window, copy the following code and paste it.
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.

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.

VBA to Hide Rows Based on Cell Text Value in Excel

  • Run this code and notice that row number 6 which consists of the word “Chemistry” is hidden.


14. 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:

  • Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • In the code window, copy the following code and paste it.
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.

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.

VBA to Hide Rows Based on Cell Numeric Value in Excel

  • Run this code and notice that row number 7 which consists of the numeric value “87” is hidden.

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


Conclusion

This article showed you how to hide rows 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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo