Constrain to visible area?

  • Thread starter Thread starter Ed
  • Start date Start date
E

Ed

I have a macro that searchs for a text string and hides any row not
containing the string (code at the end). (I got this from a post on a NG,
and tried a Google search so I could give big credits to the original
author, but I couldn't find the original post.) It works great, with one
small hitch.

If I filter my worksheet to show a range selected from the middle of the
sheet, because it starts from 2, it will search even what is not visible.
Would it be difficult to add some lines to constrain the search area to only
what is visible after filtering? It also does this if I try to run it
twice.

Sub SelectiveRowHide()

Dim myTarget As String
Dim myFind As Range
Dim i As Integer

myTarget = Application.InputBox("What text are you searching for?")

For i = 2 To Range("A65536").End(xlUp).Row
Rows(i).Select
Set myFind = Rows(i).Find(What:=myTarget)
If myFind Is Nothing Then
Selection.EntireRow.Hidden = True
End If
Next i

End Sub


Ed
 
Wouldn't autofilter be easier?
data>autofilter

record a macro to see what is going on and then adapt with your input box
for the criteria.
 
Sub SelectiveRowHide()

Dim myTarget As String
Dim myFind As Range
Dim i As Integer

myTarget = Application.InputBox("What text are you searching for?")

For i = 2 To Range("A65536").End(xlUp).Row
if not rows(i).Hidden then
Rows(i).Select
Set myFind = Rows(i).Find(What:=myTarget)
If myFind Is Nothing Then
Selection.EntireRow.Hidden = True
End If
end if
Next i

End Sub


would be one interpretation of what you said. If you mean only search the
visible cells in the filtered range

Sub SelectiveRowHide()

Dim myTarget As String
Dim myFind As Range
Dim i As Integer
Dim rng as range

myTarget = Application.InputBox("What text are you searching for?")
If activesheet.Autofiltermode then
set rng = Activesheet.Autofilter.Range
Else
set rng = Range(cells(2,1),cells(rows.count,1).End(xlup))
End if
For each cell in rng.specialcells(xlvisible)
i = cell.row
if not rows(i).Hidden then
Rows(i).Select
Set myFind = Rows(i).Find(What:=myTarget)
If myFind Is Nothing Then
Selection.EntireRow.Hidden = True
End If
End if
Next cell
End Sub

Regards,
Tom Ogilvy
 
The AutoFilter only works down one column. The macro searches every cell
across the entire row; only if the string is not found in *any* cell is the
row hidden. I would like to use the macro in conjunction with the
AutoFilter, but need to stay within the visible range after filtering to
find my "hidden string", rather than searching from Row 2. The visible
range, then, could change depending on what I have filtered for.

Ed
 
Tom:

I used your second example, and it works perfectly! I can filter to a
general catagory, then search for my string only within the filtered range.
Many thanks!

For information, I'm assuming the AutoFilter does not "hide" rows? And so
your first example would not work on a filtered range because it would not
detect any hidden rows? Is this correct?

Ed
 
Rows hidden by the autofilter are hidden. The only difference between the
two is that the second works only on the filtered range while the first
starts with row 2. You gave the impression that you have data in areas
outside the filtered data, so the first would address those as well. If the
whole page is filtered, then the first macro should work as well. The
second probably hides your header row, so you might want to adjust it to:


Sub SelectiveRowHide()

Dim myTarget As String
Dim myFind As Range
Dim i As Integer
Dim rng as range

myTarget = Application.InputBox("What text are you searching for?")
If activesheet.Autofiltermode then
set rng = Activesheet.Autofilter.Range
Else
' changed line
set rng = Range(cells(1,1),cells(rows.count,1).End(xlup))
End if
'Added line
set rng = rng.offset(1,0).Resize(rng.rows.count-1)
For each cell in rng.specialcells(xlvisible)
i = cell.row
if not rows(i).Hidden then
Rows(i).Select
Set myFind = Rows(i).Find(What:=myTarget)
If myFind Is Nothing Then
Selection.EntireRow.Hidden = True
End If
End if
Next cell
End Sub
 
**IGNORE THIS ONE** I accidently ran the previous version! Yours works
fine on only the selected visible ranges; it ignored all the others. Again,
many thanks.

Ed
 
Thanks, Tom. I appreciate the help. It did hide the headers, so I was
trying to figure where to put something like "If (i) = 1 Then Next (i)", but
it wouldn't work.

I thought the original "Range(cells(2,1)," would start the range at the
second row, though, and couldn't understand why it included the first row.
(Maybe it's time for something a bit more in-depth than "VBA for Dummies"?)

Ed
 
Back
Top