VBA to Hide Rows Based On Cell Values

In Excel, you can simplify the view of your data by hiding rows. The simplified view can help you focus on the necessary data for your task. Hiding rows using VBA is fast and efficient.

In this tutorial, I will show you six examples of using VBA to hide rows based on cell values.

Note: You should provide an apparent reason for hiding rows in Excel, especially when collaborating with others. Hiding rows is not a security measure but simply a visual aid.

VBA to Hide Rows Based on Cell Values (Exact Match)

Suppose you have the dataset below on Sheet1:

You can use the VBA code below to hide all rows with ‘Desktops’ in column C:

Sub HideRowsExactMatch()
Dim ws As Worksheet
Dim cell As Range
Set ws = ThisWorkbook.Sheets("Sheet1")
For Each cell In ws.Range("C2:C" & ws.Cells(ws.Rows.Count, "C").End(xlUp).Row)
If cell.Value = "Desktops" Then
    cell.EntireRow.Hidden = True
End If
Next cell
End Sub

When you execute the subroutine, it hides all rows in the dataset that have ‘Desktops’ in column C:

Explanation of the Code

The VBA code hides entire rows on Sheet1 where the value in column C is ‘Desktops.’

The following explains some of the code’s lines:

1. ‘For Each cell In ws.Range(“C2:C” & ws.Cells(ws.Rows.Count, “C”).End(xlUp).Row)‘: This statement initiates a loop that goes through each cell in the specified range in column C. The range starts from C2 and goes down to the last non-empty cell in column C.

Note: The ‘ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row’ part of the statement finds the last non-empty row in column C, ensuring the loop only covers the relevant range.

2. ‘If cell.Value = “Desktops” Then‘: This line checks if the value in the current cell is precisely equal to the string ‘Desktops.’
3. ‘cell.EntireRow.Hidden = True‘: If the condition is true (i.e., the cell contains ‘Desktops’), the code hides the entire row of the current cell. Should you want to unhide the rows, all you need to do is assign ‘False’ to the ‘cell.EntireRow.Hidden’ property and rerun the code.

You can modify the worksheet name, column reference, and criteria to suit your requirements.

Also read: VBA to Delete Rows Based On Cell Value

VBA to Hide Rows Based on Cell Values (Partial Match)

Assume you have the below dataset on Sheet1:

Notice the variations in naming the ‘Dell’ brand due to inconsistencies in data entry.

You can use the following VBA code to hide all rows that contain the brand ‘Dell’ in column B, regardless of variations in the naming:

Sub HideRowsPartialMatch()
Dim ws As Worksheet
Dim cell As Range
Set ws = ThisWorkbook.Sheets("Sheet1")
For Each cell In ws.Range("B2:B" & ws.Cells(ws.Rows.Count, "B").End(xlUp).Row)
If InStr(1, cell.Value, "Dell", vbTextCompare) > 0 Then
    cell.EntireRow.Hidden = True
End If
Next cell
End Sub

When you run the code, it hides all the rows containing the substring ‘Dell’ in column B:

Explanation of the Code

The VBA code hides entire rows on Sheet1 where the value in column B contains the substring ‘Dell.’

The statement ‘If InStr(1, cell.Value, “Dell”, vbTextCompare) > 0 Then’ checks if the value in the current cell contains the substring ‘Dell.’

The ‘InStr’ function returns a value greater than 0 if there’s a partial match. The ‘vbTextCompare’ parameter makes the comparison case-insensitive.

You can adjust the worksheet name, cell range, and criteria to match your needs.

To unhide the rows, set ‘cell.EntireRow.Hidden’ to ‘False’ and rerun the code.

Also read: Find Last Row Using VBA

VBA to Hide Rows Based on Numeric Criteria

Suppose you have the dataset below on Sheet1:

You can use the VBA code below to hide the rows that have a value that is less than 500 in column D:

Sub HideRowsBasedOnNumericValues()
Dim ws As Worksheet
Dim cell As Range
Set ws = ThisWorkbook.Sheets("Sheet1")
For Each cell In ws.Range("D2:D" & ws.Cells(ws.Rows.Count, _ "D").End(xlUp).Row)
    If IsNumeric(cell.Value) And cell.Value < 500 Then
        cell.EntireRow.Hidden = True
    End If
Next cell
End Sub

When you execute the subroutine, it hides the rows containing values that are less than 500 in column D:

Explanation of the Code

The VBA code hides entire rows on Sheet1 where the value in column D is numeric and less than 500.

The line ‘If IsNumeric(cell.Value) And cell.Value < 500 Then’ checks if the value in the current cell is numeric and less than 500.

You can modify the worksheet name, cell range, and the criterion to fit your requirements.

If you want to show the hidden rows, change the value of ‘cell.EntireRow.Hidden’ to ‘False’ and execute the code again.

Also read: Autofill to Last Row Using VBA in Excel

VBA to Hide Rows Based on Date Comparison

Suppose you have the dataset below on Sheet1:

You can apply the VBA code below to hide all rows containing dates earlier than January 1, 2000, in column D:

Sub HideRowsBasedOnDate()
Dim ws As Worksheet
Dim cell As Range
Set ws = ThisWorkbook.Sheets("Sheet1")
For Each cell In ws.Range("D2:D" & ws.Cells(ws.Rows.Count, "D").End(xlUp).Row)
    If IsDate(cell.Value) And cell.Value < #1/1/2000# Then
        cell.EntireRow.Hidden = True
    End If
Next cell
End Sub

When you execute the code, it hides all rows containing dates earlier than January 1, 2000, in column D:

Explanation of the Code

The VBA code hides entire rows on Sheet1 where the value in column D is a valid date and earlier than January 1, 2000.

The statement ‘If IsDate(cell.Value) And cell.Value < #1/1/2000# Then’ checks if the value in the current cell is a valid date and earlier than January 1, 2000.

You can modify the worksheet name, cell range, and the criterion to fit your requirements.

To display the hidden rows, set the ‘cell.EntireRow.Hidden’ value to ‘False’ and rerun the code.

Also read: Count Rows using VBA in Excel

VBA to Hide Rows With Blank Cells

Suppose you have the dataset below on Sheet1:

Notice the blank cells C3, C5, and C8.

You can utilize the VBA code below to hide the rows with blank cells in column C:

Sub HideRowsWithBlankCells()
Dim ws As Worksheet
Dim cell As Range
Set ws = ThisWorkbook.Sheets("Sheet1")
For Each cell In ws.UsedRange.Columns("C").Cells
    If IsEmpty(cell.Value) Then
        cell.EntireRow.Hidden = True
    End If  
Next cell
End Sub

When you execute the code, it hides the rows with blank cells in column C:

Explanation of the Code

The VBA code hides entire rows on Sheet1 where the value in column C is blank. The subroutine utilizes the ‘UsedRange’ property to loop through all used cells in the specified column.

You can modify the worksheet name and the column reference to suit your requirements.

To display hidden rows, set ‘cell.EntireRow.Hidden’ to ‘False’ and rerun the code.

Also read: Delete Blank Rows Using VBA

VBA to Hide Rows Based on Multiple Conditions

Suppose you have the dataset below on Sheet1:

You can use the VBA code below to hide the rows that contain ‘Dell’ in column B and a date earlier than January 1, 2000, in column D:

Sub HideRowsMultipleConditions()
Dim ws As Worksheet
Dim ColumnBCell As Range, ColumnDCell As Range 
Set ws = ThisWorkbook.Sheets("Sheet1")
For Each ColumnBCell In ws.Range("B2:B" & ws.Cells(ws.Rows.Count, _ "B").End(xlUp).Row)
  Set ColumnDCell = ColumnBCell.Offset(0, 2)
  If ColumnBCell.Value = "Dell" And ColumnDCell.Value < #1/1/2000# _   Then
        ColumnBCell.EntireRow.Hidden = True
    End If
Next ColumnBCell
End Sub

When you execute the code, it hides the row that has ‘Dell’ in column B and a date earlier than January 1, 2000, in column D:

Explanation of the Code

The VBA code hides entire rows on Sheet1 where the value in column B is ‘Dell’ and the corresponding value in column D is earlier than January 1, 2000.

The following explains the less apparent lines in the code:

1. ‘Set ColumnDCell = ColumnBCell.Offset(0, 2)‘: This statement sets ‘ColumnDCell’ as the cell in column D corresponding to the current cell in column B. The’ Offset(0, 2)’ moves two columns to the right.
2. ‘If ColumnBCell.Value = “Dell” And ColumnDCell.Value < #1/1/2000# Then‘: This line checks if the current cell in column B contains ‘Dell’ and the corresponding value in column D is earlier than January 1, 2000.

To reveal hidden rows in the dataset, you must set the ‘cell.EntireRow.Hidden’ property to ‘False’ and then run the code.

Also read: VBA to Freeze Top Row in Excel

Benefits of Hiding Rows in Excel

You can hide rows in Excel to achieve the following benefits:

1. Hiding rows allows you to simplify the view of your data by temporarily removing data that is not relevant to the current task.
2. If you need to hide sensitive information in your worksheet, you can temporarily conceal it by hiding rows. This action is not a secure method but can be a quick solution.
3. When preparing a document for printing, hiding rows can be part of optimizing the print layout. This action ensures you include only the relevant information in the printed version.
4. During collaborative editing or reviewing, you may choose to hide rows that are not relevant to the reviewer, making the document more concise and easier to navigate.

In this tutorial, I showed you some examples of using VBA to hide rows based on cell values. I hope you found the tutorial helpful.

Other Excel articles you may also like:

• What Does VBA mean in Excel?
• Microsoft Excel Terminology (Glossary)
• How to Hide Columns Based on Cell Value in Excel
• VBA to Insert Row in Excel