Hide Rows based on text in cell

VH5150

Board Regular
Joined
Mar 1, 2004
Messages
60
I have a worksheet that has the word "Yes" in intermittent cells throughout my worksheet in column L. I would like to write a macro that hides the entire row if the word "Yes" is present in column L. What is the code for this. My worksheet starts at row 22 and ends at row 210. Thanks!

This is round #2! Nobody responded initially.
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
Try this:

Code:
Sub deleteRow()
    For i = 22 To 210
        If Range("L" & i) = "yes" Then
            Rows(i).Hidden = True
        End If
    Next i
End Sub
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,475
Sample Code

Here some sample code: Adjust cells and ranges accordingly;


Sub Foo()
Dim Rng As Range
Dim Cell As Range
Set Rng = Range(Cells(3, "L"), Cells(20, "L"))
For Each Cell In Rng
If Cell.Value = "Yes" Then
Cell.EntireRow.Hidden = True
End If
Next
End Sub
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hello, VH5150,
and hello, iggydarsa
(didn't we meet some hours ago ?)

take also a look at autofilter
custom "non equal to"

using code:
Range("L21:L210").AutoFilter Field:=1, Criteria1:="<>yes"
this would be faster then a loop when you have a lot of rows to check

kind regards,
Erik

EDIT: hello, jim may !
 

Watch MrExcel Video

Forum statistics

Threads
1,119,128
Messages
5,576,251
Members
412,709
Latest member
Rishu
Top