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

redelmab

New Member
Joined
May 28, 2010
Messages
4
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 :)!
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

SamTYler

Well-known Member
Joined
Mar 10, 2004
Messages
784
This will hide everything between the first and last

Pseudocode: (Meaning, you have to flesh it out.)
Code:
'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
Code:
.Rows.Hidden = True 'Hide all
Rows(First).Hidden = False 'Show First
Rows(Last).Hidden = False 'Show Last
 

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,953
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Or:

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

redelmab

New Member
Joined
May 28, 2010
Messages
4
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,299

ADVERTISEMENT

Try...

Code:
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

New Member
Joined
May 28, 2010
Messages
4
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,299

ADVERTISEMENT

Try...

Code:
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

New Member
Joined
May 28, 2010
Messages
4
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,299
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,918
Messages
5,525,623
Members
409,657
Latest member
19JimRon72

This Week's Hot Topics

Top