Using VBA to Hide Rows Based on Cell Value in Excel

This is the sample dataset.

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

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

You want to hide rows that hold a specific text value in a particular cell. Here, the row that contains the word “Chemistry” in D6.

Steps:

Press Alt + F11 or go to Developer -> Visual Basic to open the Visual Basic Editor.

Click Insert -> Module.

Enter the following code 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

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 or click Run -> Run Sub/UserForm. You can also click the small Run icon in the sub-menu bar to run the macro.

This is the output.

VBA Code Explanation

Sub HideRowCellTextValue()

provides a name for the sub-procedure.

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

declares the last row of the dataset. LastRow = 10

iCol = 4

declares the column address that holds the text value.

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

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), it hides the entire rows. It continues iterating through all the rows in that column.

End Sub

ends the sub-procedure.

Example 2 – Applying a Macro to Hide Rows Based on Numeric Value in Excel

You want to hide the row that contains the numeric value “87” in D7.

Steps:

In the Developer tab, open the Visual Basic Editor and Insert a Module in the code window.
Enter the following code.

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

Here,

StartRow = 4 -> First row of the dataset.
LastRow = 10 -> Last row of the dataset.
iCol = 4 -> The column address that holds the numeric value.

Run the code.

Row number 7 with the numeric value “87” in D7 is hidden.

VBA Code Explanation

Sub HideRowCellNumValue()

provides a name for the sub-procedure.

StartRow = 4

declares the first row of the dataset: StartRow = 4.

LastRow = 10

declares the last row of the dataset. LastRow = 10.

iCol = 4

declares the column address that holds the numeric value.

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

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

End Sub

ends the sub-procedure.

Example 3 – Use VBA to Hide All Rows in which Cells Contain Text in Excel

Steps:

In the Developer tab, open the Visual Basic Editor and Insert a Module in the code window.
Enter the following code.

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

Here,

IsNumeric(Range(“C” & i)) = The data in the dataset starts in column C.

Run the code to see the result.

VBA Code Explanation

Sub HideAllRowsContainsText()

provides a name for the sub-procedure.

LastRow = 1000

assumes there are 1000 rows in the dataset.

For i = 1 To LastRow

starts looping through each row from the first row to the last row and check for the condition.

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

If there are non-numeric values in Column C, it hides all rows that hold numeric values.

Next

continues the FOR Loop till it reaches all rows.

End Sub

ends the sub-procedure.

Example 4 – Insert VBA to Hide All Rows in which Cells Contain Numbers in Excel

Steps:

In the Developer tab, open the Visual Basic Editor and Insert a Module in the code window.
Enter the following code.

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

Here,

IsNumeric(Range(“C” & i)) = The data in our dataset starts from column C.

Run the code to see the result.

VBA Code Explanation

Sub HideAllRowsContainsNumbers()

provides a name for the sub-procedure.

LastRow = 1000

assumes there are 1000 rows in the dataset.

For i = 4 To LastRow

starts looping through each row from the first row to the last row and checks for the condition.

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

If there are numeric values in Column C, it hides all the rows that hold numeric values.

Next

continues the FOR Loop till it reaches all rows.

End Sub

ends the sub-procedure.

Example 5 – Embed a Macro to Hide Rows Based on Zero (0) as Cell Value

You want to hide rows with cells holding 0 (zero).

Steps:

In the Developer tab, open the Visual Basic Editor and Insert a Module in the code window.
Enter the following code.

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

Run the code to see the result.

VBA Code Explanation

Sub HideRowContainsZero()

provides a name for the sub-procedure.

LastRow = 1000

assumes there are 1000 rows in the dataset.

For i = 4 To LastRow

starts looping through each row from the first row to the last row and checks for the condition.

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

If Column E holds 0, it hides all rows in that column that hold the value.

Next

continues the FOR Loop till it reaches all rows.

End Sub

ends the sub-procedure.

Example 6 – Applying VBA to Hide Rows Based on a Negative Value

Column E contains both negative and positive values.

Steps:

In the Developer tab, open the Visual Basic Editor and Insert a Module in the code window.
Enter the following code.

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

Run the code to see the result.

VBA Code Explanation

Sub HideRowContainsNegative()

provides a name for the sub-procedure.

LastRow = 1000

assumes there are 1000 rows in the dataset.

For i = 4 To LastRow

starts looping through each row from the first row to the last row and checks for the condition.

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 less than 0, it hides the entire rows.

Next

continues the FOR Loop till it reaches all rows.

End Sub

ends the sub-procedure.

Example 7 – Insert a VBA Macro to Hide Rows Based on a Positive Value

Steps:

In the Developer tab, open the Visual Basic Editor and Insert a Module in the code window.
Enter the following code.

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

Run the code to see the result.

VBA Code Explanation

Sub HideRowContainsPositive()

provides a name for the sub-procedure.

LastRow = 1000

assumes there are 1000 rows in the dataset.

For i = 4 To LastRow

starts looping through each row from the first row to the last row and checks for the condition.

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 greater than 0, it hides the entire rows.

Next

continues the FOR Loop till it reaches all the rows.

End Sub

ends the sub-procedure.

Example 8 – Apply a Macro to Secrete Rows in which Cells Contain Odd Numbers

Steps:

In the Developer tab, open the Visual Basic Editor and Insert a Module in the code window.
Enter the following code.

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

Run the code to see the result.

VBA Code Explanation

Sub HideRowContainsOdd()

provides a name for the sub-procedure.

LastRow = 1000

assumes there are 1000 rows in the dataset.

For i = 4 To LastRow

starts looping through each row from the first row to the last row and check for the condition.

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 (odd values), it hides the entire rows.

Next

continues the FOR Loop till it reaches all rows.

End Sub

ends the sub-procedure.

Example 9 – Use VBA to Cloak Rows in which Cells Contain Even Numbers

Steps:

In the Developer tab, open the Visual Basic Editor and Insert a Module in the code window.
Enter the following code.

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

Run the code to see the result.

VBA Code Explanation

Sub HideRowContainsEven()

provides a name for the sub-procedure.

LastRow = 1000

assumes there are 1000 rows in the dataset.

For i = 4 To LastRow

starts looping through each row from the first row to the last row and check for the condition.

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 (even values), it hides the entire rows.

Next

continues the FOR Loop till it reaches all rows.

End Sub

ends the sub-procedure.

Example 10 – Insert VBA to Hide Rows in which the Cell Value Is Greater Than a Specific Condition

Steps:

In the Developer tab, open the Visual Basic Editor and Insert a Module in the code window.
Enter the following code.

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

Run the code to see the result.

VBA Code Explanation

Sub HideRowContainsGreater()

provides a name for the sub-procedure.

LastRow = 1000

assumes there are 1000 rows in the dataset.

For i = 4 To LastRow

starts looping through each row from the first row to the last row and check for the condition.

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, it hides the entire rows.

Next

continues the FOR Loop till it reaches all rows.

End Sub

ends the sub-procedure.

Example 11 – Embed a Macro to Conceal Rows in which the Cell Value Is Less Than a Specific Condition

Steps:

In the Developer tab, open the Visual Basic Editor and Insert a Module in the code window.
Enter the following code.

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

Run the code to see the result.

VBA Code Explanation

Sub HideRowContainsLess()

provides a name for the sub-procedure.

LastRow = 1000

assumes there are 1000 rows in the dataset.

For i = 4 To LastRow

starts looping through each row from the first row to the last row and check for the condition.

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, it hides the entire rows.

Next

continues the FOR Loop till it reaches all rows.

End Sub

ends the sub-procedure.

Example 12 – Use VBA to Hide Rows Based on a Condition Inserted in a Cell

To hide rows based on the criteria that you provide in a specified cell:

Steps:

Right-click the worksheet.
Select View Code.

Enter the code 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

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 the code, save it.

Go back to the worksheet.
Insert any value from Column E in E12: the whole row that contains the value will be hidden.

Observe the GIF above.

VBA Code Explanation

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

initiates an event in the worksheet. Target is passed as an argument with Range type. When the Range changes, the event occurs.

StartRow = 4

declares the first row of the dataset. StartRow = 4

LastRow = 10

declares the last row of the dataset. LastRow = 10

iCol = 5

declares the column address that holds the text value.

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

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

End Sub

ends the sub-procedure.

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

Steps:

Right-click the worksheet.
Select View Code.

Enter the code 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

Don’t run the code, save it.
Go back to the worksheet. Insert any value from 1, 2 or 3 in E12. Rows hide automatically, according to the inserted value.

Observe the GIF above.

VBA Code Explanation

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

initiates an event in the specific worksheet. Target is passed as an argument with Range type. When the Range changes, the event occurs.

i = Range("E12").Value

stores the cell address – E12 – in which values will be inserted 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

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

End Sub

ends the sub-procedure.

Example 14 – Apply VBA to Hide Rows If the Cell Value Is Changed in Excel

Steps:

Right-click the worksheet.
Select View Code.

Enter the code 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

Don’t run the code, save it.
Go back to the worksheet. Insert 0 in C4. You will see that all rows will be hidden in the worksheet.

Observe the GIF above.

VBA Code Explanation

Option Explicit

forces to declare all the variables explicitly.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

initiates an event in the specific worksheet. 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")

declares a variable and stores 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

executes the macro according to the RowHide sub-procedure.

End Sub

ends the sub-procedure of the event.

Sub RowHide(ByVal SourceCell As Range)

Initiates 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 C4 is 0, it hides rows 4 to 10. Otherwise, it keeps the rows unhidden.

End Sub

ends the sub-procedure of the macro.

Download Practice Workbook

Hide Rows Based on Cell Value.xlsm

How to Hide Blank Rows in Excel VBA

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Gert Renkin Jul 20, 2022 at 2:31 PM

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
- Reply
  Sabrina Ayon Aug 31, 2022 at 5:40 PM
  
  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

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

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

Example 2 – Applying a Macro to Hide Rows Based on Numeric Value in Excel

Example 3 – Use VBA to Hide All Rows in which Cells Contain Text in Excel

Example 4 – Insert VBA to Hide All Rows in which Cells Contain Numbers in Excel

Example 5 – Embed a Macro to Hide Rows Based on Zero (0) as Cell Value

Example 6 – Applying VBA to Hide Rows Based on a Negative Value

Example 7 – Insert a VBA Macro to Hide Rows Based on a Positive Value

Example 8 – Apply a Macro to Secrete Rows in which Cells Contain Odd Numbers

Example 9 – Use VBA to Cloak Rows in which Cells Contain Even Numbers

Example 10 – Insert VBA to Hide Rows in which the Cell Value Is Greater Than a Specific Condition

Example 11 – Embed a Macro to Conceal Rows in which the Cell Value Is Less Than a Specific Condition

Example 12 – Use VBA to Hide Rows Based on a Condition Inserted in a Cell

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

Example 14 – Apply VBA to Hide Rows If the Cell Value Is Changed in Excel

Related Article

Leave a reply Cancel reply