Rifat Hassan
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.
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 iTherefore, 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
⧭ 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
- VBA to Hide Rows in Excel (14 Methods)
- VBA to Hide Rows Based on Cell Value in Excel (14 Examples)
- Hidden Rows in Excel: How to Unhide or Delete Them?
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
⧭ 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).
Read More: Hide Rows Based on Cell Value with Conditional Formatting in Excel
Similar Readings
- Formula to Hide Rows in Excel (7 Methods)
- Hide Duplicate Rows Based on One Column in Excel (4 Methods)
- How to Hide Blank Rows in Excel VBA (4 Useful Methods)
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) ThenDo 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.
