Constrain to visible area?
 

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

Constrain to visible area?
 

Wouldn't autofilter be easier?
dataautofilter

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

--
Don Guillett
SalesAid Software
Granite Shoals, TX

"Ed" wrote in message
...
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

Constrain to visible area?
 

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


"Ed" wrote in message
...
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

Constrain to visible area?
 

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


"Don Guillett" wrote in message
...
Wouldn't autofilter be easier?
dataautofilter

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

--
Don Guillett
SalesAid Software
Granite Shoals, TX

"Ed" wrote in message
...
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

Constrain to visible area?
 

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

"Tom Ogilvy" wrote in message
...
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


"Ed" wrote in message
...
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

Constrain to visible area?
 

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

--
Regards,
Tom Ogilvy


"Ed" wrote in message
...
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

"Tom Ogilvy" wrote in message
...
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


"Ed" wrote in message
...
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

Constrain to visible area?
 

**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


"Ed" wrote in message
...
Okay - just ran the second one with the sheet filterd slightly different -
the filter returned two chunks out of the middle. The search started at
the
top of the first visible range, then continued searching through the rows
that were "hidden" by the filter, then down through the last visible
range.
(Say Col A has rows 1-5 "A", 6-10 "B", etc. through "E". If I filtered
for
"B" and "D", it would have searched B, C, and D, rather than only the
visible B and D ranges.)

Is this just a limitation of this beast?

Ed

Constrain to visible area?
 

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


"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy


"Ed" wrote in message
...
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

"Tom Ogilvy" wrote in message
...
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


"Ed" wrote in message
...
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