Using a Variable Range to Hide Rows

[Aprez76]

Looking for some help hidding rows. I have a data set that is variable in size but no larger than 3000 rows. I would like to write some VB code that based on a value in column A (which is already sorted by Yes then NO) finds the first row with a value of "No" and hides that row and all rows that follow, all the way to Row 3000. Ultimatly hidding all rows in the data set that have the value NO in Column A. Since the data is sorted I'm sure I do not have to loop through each value in column A but rather find the first "No" value and hide everything that follows.

Any help is appreciated.

 

[DonkeyOte]

Welcome to the Board.

What you ask can be done with VBA, however, if this is the only data on the sheet you could use Auto Filter to display everything but No's by selecting Custom option on appropriate Column and setting conditional rule -> does not equal No.

 

[Aprez76]

I'm looking to do this using VBA versus the auto filter approach as it ties into a larger project that includes some VBA.

 

[DonkeyOte]

Sub Standard()
'---------------------------------------
Dim row_1 As Long: row_1 = 1
Dim row_2 As Long: row_2 = 3000
Dim col As Integer: col = 1     'Col A
'---------------------------------------
On Error GoTo NoNo
row_1 = Application.WorksheetFunction.Match("No", Range(Cells(row_1, col), Cells(row_2, col)), 0) + (row_1 - 1)
Rows(row_1 & ":" & row_2).Hidden = True
Exit Sub
'nothing to hide ?
NoNo:
End Sub

 

[Aprez76]

This works pefectly. Thanks for the help