Hi,

(Using Excel in Win 98.)

I need to loop through a named range, search for each instance of the
character "~", hide each row with that character, then stop the VB code
automatically when it can't find any more.

How to have it exit automatically?

Thanks.

--

David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King

One way, without looping:

   Public Sub HideTildes()
        Const MYCOL As String = "A"
        Const STARTROW As Long = 1
        Const MYWHAT As String = "*~~*"
        Dim rng As Range

        Application.ScreenUpdating = False
        With ActiveSheet
            .Rows(STARTROW).Insert
            .UsedRange
            .Cells(STARTROW, MYCOL) = "temp"
            With Intersect(.Cells(STARTROW, MYCOL).Resize(Rows.Count - _
                    STARTROW), .UsedRange)
                .AutoFilter Field:=1, Criteria1:="=" & MYWHAT
                On Error Resume Next
                Set rng = .SpecialCells(xlCellTypeVisible)
                On Error GoTo 0
            End With
            .Cells(STARTROW, MYCOL).AutoFilter
        End With
        If Not rng Is Nothing Then rng.EntireRow.Hidden = True
        ActiveSheet.Rows(STARTROW).Delete
        Application.ScreenUpdating = True
    End Sub

JE's answer will be much quicker if your named range is one column, but here's
one way out of the looping when you're doing find's (slightly modified from
VBA's Help for .find):

Option Explicit
Sub testme03()

    Dim FoundCell As Range
    Dim FirstAddress As String
    Dim myRange As Range
    Dim HideMe As Range

    Set myRange = Worksheets("sheet1").Range("myNamedRange")

    With myRange
        Set FoundCell = .Find("~~", LookIn:=xlValues)
        If Not FoundCell Is Nothing Then
            FirstAddress = FoundCell.Address
            Do
                If HideMe Is Nothing Then
                    Set HideMe = FoundCell
                Else
                    Set HideMe = Union(FoundCell, HideMe)
                End If
                Set FoundCell = .FindNext(FoundCell)
            Loop While Not FoundCell Is Nothing _
                And FoundCell.Address <> FirstAddress
        End If
    End With

    If HideMe Is Nothing Then
        'do nothing
    Else
        HideMe.EntireRow.Hidden = True
    End If

End Sub

(you just keep finding the same thing and checking the address against the the
address of the first one you found.  When the current one matches the first one,
you know you've gone through them all.)

--

Dave Peterson

something like this:

Sub HideRows()
    Dim cell As Range
    Const Tilde As String = "~"
    For Each cell In Range("A1:A100")
        If InStr(cell.Value, Tilde) > 0 Then
            Rows(cell.Row).Hidden = True
        End If
    Next
End Sub

--
Patrick Molloy
Microsoft Excel MVP
----------------------------------

Martin King

Thanks, Dave Peterson! This code does everything I wanted, and more!

However, I'd like to understand the code a little better. Could someone
pretend that I've never seen VBA code before, and please annotate each
line?

Thanks very much.

JE's answer will be much quicker if your named range is one column, but
here's one way out of the looping when you're doing find's (slightly
modified from VBA's Help for .find):

Option Explicit
Sub testme03()

    Dim FoundCell As Range
    Dim FirstAddress As String
    Dim myRange As Range
    Dim HideMe As Range

    Set myRange = Worksheets("sheet1").Range("myNamedRange")

    With myRange
        Set FoundCell = .Find("~~", LookIn:=xlValues)
        If Not FoundCell Is Nothing Then
            FirstAddress = FoundCell.Address
            Do
                If HideMe Is Nothing Then
                    Set HideMe = FoundCell
                Else
                    Set HideMe = Union(FoundCell, HideMe)
                End If
                Set FoundCell = .FindNext(FoundCell)
            Loop While Not FoundCell Is Nothing _
                And FoundCell.Address <> FirstAddress
        End If
    End With

    If HideMe Is Nothing Then
        'do nothing
    Else
        HideMe.EntireRow.Hidden = True
    End If

End Sub

(you just keep finding the same thing and checking the address against the
the address of the first one you found.  When the current one matches the
first one, you know you've gone through them all.)

--

David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King

Maybe not each line....(and interspersed)

The option explicit forces me to declare (with Dim lines) each of my variables.

Some/most of these variables are range objects--like a cell or a group of cells
in the worksheet.  One is a string (just a bunch of letters/numbers/special
characters).

use a variable that refers to the named range on sheet1.  By using a variable,
you can make changes to the code easier (usually).  By using variables that are
dimmed appropriately, you get the intellisense (autocomplete) when you're
writing the code, too!

The with statement means each thing that starts with a dot (.) has that thing
prefixed to it.  So .find("~~").... could also be written as:
myrange.find("~~")......

It saves typing and makes it easier to update (I think).

Look for ~ (this is a special character so we have to double up on it.  To find
an asterisk--not the wildcard, but the asterisk, you have to use ~* (same with
?, ~?).  Same with tilde.

This is like hitting ctrl-F (or edit|Find) after selecting myNamed range.

This checks to see if it actually found anything.  In excel, you'd get a message
saying it wasn't found.  In VBA, you have to check yourself.  (If it wasn't
found, that foundcell variable is never set.  So it's still nothing.)

But if it was found, keep track of the first one you found--use the address.

Do this loop until ~ isn't found or until the one you found has the same address
as the first one you found.

Keep track of the cells that you found in a range "accumulator".  If it's never
been set, set it to the firstcell found.  If it has been set, then just merge
that next found cell into that accumulator range.

Look again!

end of the do loop

Check the accumulator range.  If you never added anything to it, don't do
anything.

If you did find something, then hide every row in that range.e

--

Dave Peterson