Searching a Range For Two Known Text Values and Hiding the Rows in between.

[redelmab]

Hello,

I am a beginner user of VBA and am having trouble finding a function to do what I need. I have a predefined range in excel (about 1000 cells) called Range 1. In this range I have the values "user" and "visitor". When a checkbox (chk1) is checked, I want a code to search Range 1 for the text value "user" and hide the rows between it and "visitor". I do not want user and visitor to be hidden though. Any help is GREATLY appreciated !

 

[SamTYler]

This will hide everything between the first and last

Pseudocode: (Meaning, you have to flesh it out.)

'first, unhide all rows
.Rows.Hidden = False 
Dim R As Long, _
First As Long, _
Last As Long, _
Visitor As String
 
Visitor = InputBox("Pls enter the visitor to show")
 
First = .Find(Visitor lookin:=xlValues).Row
Last = .FindNext.Row
 
If First > Last Then Exit Sub 'It looped around
 
For R = First + 1 To Last - 1
   Rows(R).Hidden = True
Next R

This will hide everything except the First and Last

.Rows.Hidden = True 'Hide all
Rows(First).Hidden = False 'Show First
Rows(Last).Hidden = False 'Show Last

 

[wigi]

Or:

Sub test()
    Rows(Range("range 1").Find("user", , xlValues, xlWhole).Row + 1, Range("range 1").Find("visitor").Row - 1).Hidden = True
End Sub

 

[redelmab]

Hello Wigi,
I have tried to use your code but when I do I get an application define or object define error.
This is what my code looke like:

Private Sub butContinue_Click()
'This will hide rows when a checkbox is selected.
If Me.Chkbox1.Value = True Then

Rows(Worksheets("Sheet 1").Range("Chosen").Find("user", , xlValues, xlWhole).Row + 1, Worksheets("Sheet 1").Range("Chosen").Find("visitor").Row - 1).Hidden = True

End If
End Sub

Any ideas as to what is wrong?

 

[Domenic]

Try...

Rows(Worksheets("Sheet 1").Range("Chosen").Find("user", , xlValues, xlWhole).Row + 1[COLOR=red] & ":" &[/COLOR] Worksheets("Sheet 1").Range("Chosen").Find("visitor").Row - 1).Hidden = True

 

[redelmab]

Thanks!!!! It worked. One other addendum. Say I found user, and only wanted to find the visitor term lower than user in the spreadsheet. At the moment the sub will find the first value it comes too...not the first value after the user cell is found. Any helpful ideas?

Thanks Again!

 

[Domenic]

Try...

Option Explicit
Sub test()
 
    Dim rUser As Range, rVisitor As Range
    Dim UserRow As Long, VisitorRow As Long
 
    With Worksheets("Sheet 1").Range("Chosen")
        .Parent.Rows.Hidden = False
        Set rUser = .Find("user", , xlValues, xlWhole)
        If Not rUser Is Nothing Then
            UserRow = rUser.Row
        Else
            MsgBox "No user row exists...", vbInformation
            Exit Sub
        End If
        Set rVisitor = .Find("visitor", rUser, xlValues, xlWhole)
        If Not rVisitor Is Nothing Then
            VisitorRow = rVisitor.Row
        Else
            MsgBox "No visitor row exists...", vbInformation
            Exit Sub
        End If
        If UserRow > VisitorRow Then
            MsgBox "No visitor row exists after the user row...", vbInformation
        ElseIf VisitorRow - UserRow = 1 Then
            MsgBox "No rows exist between the user row and visitor row...", vbInformation
        Else
            .Parent.Rows(UserRow + 1 & ":" & VisitorRow - 1).Hidden = True
        End If
    End With
 
End Sub

 

[redelmab]

Thanks Domenic. I couldnt get the second solution of yours to work, but I can modify my spreadsheet so that the original code you posted works everytime. Thank You!

 

[Domenic]

Thanks Domenic. I couldnt get the second solution of yours to work, but I can modify my spreadsheet so that the original code you posted works everytime. Thank You!

If you're still interested in pursuing the last solution I offered, describe what happens when you run the code. Does an error occur? If so, which one, and which line gets highlighted?