Excel Macro: Hide Rows Based on Cell Text in Excel (3 Simple Methods)

In this article, I’ll show you how you can develop a Macro to hide rows based on cell text with VBA in Excel.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


3 Methods to Develop a Macro to Hide Rows Based on Cell Text in Excel

Here we’ve got a data set with the names, marks, and grades of some students of a school in a kindergarten.

Data Set to Develop a Macro to Hide Rows Based On Cell Text in Excel

Our objective today is to develop a Macro to hide rows based on cell texts on this data set.


1. Develop a Macro to Hide Rows When Cell Text Is Equal to a Certain Text

First of all, we’ll develop a Macro to hide the rows when the cell text is equal to a certain text.

For example, let’s try to hide the rows with the students where the grade is F.

I’m showing you the step-by-step procedure to write down the code.

⧪ Step 1: Inserting the Inputs

First of all, we’ve to enter the inputs to the code. The inputs are the range of cells where we’ll search for the specific text (D4:D13), and the specific text (“F”).

Set Rng = Range("D4:D13")
Text = "F"

⧪ Step 2: Iterating through a For-Loop

Next, we’ll start a for-loop that will iterate through each cell of the input range.

For i = 1 To Rng.Rows.Count
    For j = 1 To Rng.Rows.Count

⧪ Step 3: Hiding the Rows Based on the Cell Texts

This is the most important step. We’ll analyze the value of each cell in the input range, and if it equals the input text, the whole row will be hidden.

        If Rng.Cells(i, j) = Text Then
            Rng.Cells(i, j).EntireRow.Hidden = True
        End If

⧪ Step 4: Ending the Iterations

Finally, we have to end the for-loops.

    Next j
Next i

Therefore, the complete VBA code will be:

⧭ VBA Code:

Sub Hide_Rows_Equal_to_a_Certain_Text()

Set Rng = Range("D4:D13")
Text = "F"

For i = 1 To Rng.Rows.Count
    For j = 1 To Rng.Rows.Count
        If Rng.Cells(i, j) = Text Then
            Rng.Cells(i, j).EntireRow.Hidden = True
        End If
    Next j
Next i

End Sub

VBA Code to Develop Macro to Hide Rows Based on Cell Text in Excel

⧭ Output:

Run the code (Obviously after changing the inputs according to your need). It’ll hide the rows where the cell text within the range is equal to the specific text (“F” in this example).

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


Similar Readings


2. Create a Macro to Hide Rows When Cell Text Is Not Equal to a Certain Text

We’ve learned to create a Macro to hide the rows when the cell text is equal to a certain text.

Now, we’ll develop a Macro to hide the rows when the cell text is not equal to a certain text.

Let’s try to hide the rows with the students where the grade is not “A+”.

The VBA code will be:

⧭ VBA Code:

Sub Hide_Rows_Not_Equal_to_a_Certain_Text()

Set Rng = Range("D4:D13")
Text = "A+"

For i = 1 To Rng.Rows.Count
    For j = 1 To Rng.Rows.Count
        If Rng.Cells(i, j) <> Text Then
            Rng.Cells(i, j).EntireRow.Hidden = True
        End If
    Next j
Next i

End Sub

VBA Code to Develop a Macro to Hide Rows Based On Cell Text in Excel

⧭ Output:

Run the code after changing the inputs. It’ll hide the rows where the cell text within the range is not equal to the specific text (“A+” in this example).

Output to Develop a Macro to Hide Rows Based on Cell Text in Excel

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


Similar Readings


3. Embed a Macro to Hide Rows Based on Partial Match of the Cell Text

We’ve learned to create a Macro to hide the rows when the cell text is equal to a certain text and not equal to a certain text.

Finally, we’ll embed a Macro to hide rows based on the partial match.

Let’s try to hide the rows with the students where the grade contains A (A+, A, or A-). That means, we’ll hide the rows where the grade matches partially with A.

The VBA code will be:

⧭ VBA Code:

Sub Hide_Rows_on_Partial_Match()

Set Rng = Range("D4:D13")
Text = "A"

For i = 1 To Rng.Rows.Count
    For j = 1 To Rng.Rows.Count
        For k = 1 To Len(Rng.Cells(i, j))
            If Mid(Rng.Cells(i, j), k, Len(Text)) = Text Then
                Rng.Cells(i, j).EntireRow.Hidden = True
                Exit For
            End If
        Next k
    Next j
Next i

End Sub

⧭ Output:

Run the code after changing the inputs. It’ll hide the rows where the cell text matches partially with the input text(“A” in this example, A+, A, and A-).

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


Things to Remember

While developing the code for the partial match, we’ve used the Mid function of VBA. Click on it to know it in detail.

All the codes mentioned here are case-sensitive. If you want a case-insensitive match, wrap the comparing values within the If-block inside an LCase function.

For example, in the 1st code, replace the 1st line of step 3 with:

If LCase(Rng.Cells(i, j) )= LCase(Text) Then

Do the same for all the codes.


Conclusion

So, these are the methods to develop a Macro to hide rows based on cell text in Excel. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo