Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Feb 2003
    Location
    Bournemouth, Dorset, England
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula Streamline (xl 2002)

    Can anyone help me streamline this formula:

    Sub Hide()
    If Range("B13") = "-" Then
    Rows("13:13").Select
    Selection.EntireRow.Hidden = True
    Else
    Rows("13:13").Select
    Selection.EntireRow.Hidden = False
    End If

    If Range("B14") = "-" Then
    Rows("14:14").Select
    Selection.EntireRow.Hidden = True
    Else
    Rows("14:14").Select
    Selection.EntireRow.Hidden = False
    End If

    If Range("B15") = "-" Then
    Rows("15:15").Select
    Selection.EntireRow.Hidden = True
    Else
    Rows("15:15").Select
    Selection.EntireRow.Hidden = False
    End If

    If Range("B16") = "-" Then
    Rows("16:16").Select
    Selection.EntireRow.Hidden = True
    Else
    Rows("16:16").Select
    Selection.EntireRow.Hidden = False
    End If

    Etc, etc, etc...

    End Sub

    I would like to be able to specify the number of rows (i.e. from B13 to B143), and for each cell that equals dash in column B the entire row needs to be hidden. If the cell in column B does not equal dash the entire row needs to be visible.

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula Streamline (xl 2002)

    You could try something like this:
    <pre>Option Explicit

    Sub myHide()
    Dim myC As Range
    Dim i As Integer
    Dim a As Integer
    Range("B13").Select
    Set myC = Range(Selection, Selection.End(xlDown))
    For i = 1 To myC.Cells.Count
    a = i + 12
    If Range("B" & a) = "-" Then
    Rows(a).EntireRow.Hidden = True
    End If
    Next i
    End Sub</pre>

    Regards,
    Rudi

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Streamline (xl 2002)

    Try this:

    <pre>Public Sub HideRows()
    Dim oCell As Range
    For Each oCell In Range("B13:B143")
    If oCell.Value = "-" Then
    oCell.EntireRow.Hidden = True
    Else
    oCell.EntireRow.Hidden = False
    End If
    Next oCell
    End Sub
    </pre>

    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •