Hiding Rows Based on Text

[geotek]

I'm hopeful that someone can help with this question. I'm not good with VBA but trying to learn.

I have a spreadsheet that gets periodic updates from a secondary program. I import the data onto Sheet3 and do some misc. edits (i.e., unmerge cells, and sort the data columns based on 2 fields). Generally I have between 300 and 400 rows (Varies) and 39 columns (Static). The Sheet1 references 3 of the columns from Sheet3 (Client ID; Status; Days at Status; and Notes) and graphs them. The data is sorted according to "Status" and then "Days at Status" for the graph to display correctly. I've created a example table.

Excel 2007
	A	B	C	D
1	Client No	Current Status	Days at Status	Notes
2	Client 001	Status 1	1	Follow up
3	Client 002	Status 1	2	Follow up
4	Client 003	Status 1	4	Follow up
5	Client 004	Status 1	10	Follow up
6	Client 005	Status 1	10	Follow up
7	Client 006	Status 1	12	Follow up
8	Client 007	Status 1	15	Follow up
9	Client 008	Status 2	3	Pending Response
10	Client 009	Status 2	5	Pending Response
11	Client 010	Status 2	5	Pending Response
12	Client 011	Status 2	7	Pending Response
13	Client 012	Status 2	14	Pending Response
14	Client 013	Status 2	21	Pending Response
15	Client 014	Status 2	23	Pending Response
16	Client 015	Status 2	33	Pending Response
17	Client 016	Status 3	4	Responded
18	Client 017	Status 3	8	Responded
19	Client 018	Status 3	12	Responded
20	Client 019	Status 3	12	Responded
21	Client 020	Status 3	12	Responded
22	Client 021	Status 3	18	Responded
23	Client 022	Status 3	19	Responded
24	Client 023	Status 3	19	Responded
25	Client 024	Status 4	4	To be Closed
26	Client 025	Status 4	4	To be Closed
27	Client 026	Status 4	4	To be Closed
28	Client 027	Status 4	4	To be Closed
29	Client 028	Status 4	4	To be Closed
30	Client 029	Status 4	4	To be Closed
Sheet1

I normally don't need to worry about any of the Status 4's so I select them and hide the rows. But every time I get a new data set (weekly or more frequently), i have to unhide the hidden rows and find all of the "Status 4's" again. I want to automate that process with a code. I had a very crude piece of VBA that unhide every row and then went through each row and looked for "status 4" and hid the row. This was very slow, and then I lost the code when the workbook crashed and had to go back to a much earlier version without the VBA. My data set varies in length week to week.

What I hope to accomplish is to have a VBA that will go to the first occurrence of Status 4 and then hide every row to the end of the data in the "Status" column. I expect that there will be additional Status' after "4" in the future but I won't have any need to deal with them for what I'm doing.

I started with this code I found posted, but haven't had much luck getting to where I need to be.

Sub Find_Last()
    Dim FindString As String
    Dim Rng As Range

    FindString = "Status 3"
    
    If Trim(FindString) <> "" Then
        With Sheets("Sheet1").Range("B:B")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(1), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False)
                 
            If Not Rng Is Nothing Then
                Application.Goto Rng, True
             
            Else
                MsgBox "Nothing found"
            End If
        End With
    End If
  
    
End Sub

Thanks for any help!

 

[onlyadrafter]

Hello,

if you truly mean to hide the rows then use

Sub HIDE_STATUS_4()
    Application.ScreenUpdating = False
    With Sheets("Sheet1")
        .Rows.Hidden = False
        For MY_ROWS = 2 To .Range("B" & Rows.Count).End(xlUp).Row
            If .Range("B" & MY_ROWS).Value = "Status 4" Then
                .Rows(MY_ROWS).Hidden = True
            End If
        Next MY_ROWS
    End With
    Application.ScreenUpdating = True
End Sub

 

[geotek]

Thank you, that works and hides the rows like I need.

 

[Smitty]

Welcome to the Board!

Why not just use AutoFilter?

 

[geotek]

I attempted an autofilter originally, but an issue I ran into is that the data is used by a graph. When the autofilter was in place, I still got the filtered rows showing up as blank regions on the graph. Which lead me to hiding the rows. This removed the data completely from the graph and prevented the large blank regions. If there is a way to get the autofilter to not have the blanks show on the graph I'm definitely interested.

During another search after getting the above code, I found this which is what I've adapted:

Sub HideStatus4()
'---------------------------------------
Dim row_1 As Long: row_1 = 1
Dim row_2 As Long: row_2 = 3000
Dim col As Integer: col = 3     'Col C
'---------------------------------------
On Error GoTo NoNo
row_1 = Application.WorksheetFunction.Match("4 - Payment Issued", 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

This sped up process a great deal. Also, I picked an end range well below the maximum of the anticipated rows and hid to that row. Another issue I had was that the cells that I'm looking into have become references to another sheet which seems to be an issue for the .FIND in my earlier code.