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

    Looping Code (xl97)

    Is there a better way of writing this:

    If Range("C74") = "" Then
    Rows("74:74").Select
    Selection.EntireRow.Hidden = True
    End If

    If Range("C75") = "" Then
    Rows("75:75").Select
    Selection.EntireRow.Hidden = True
    End If

    If Range("C76") = "" Then
    Rows("76:76").Select
    Selection.EntireRow.Hidden = True
    End If

    If Range("C77") = "" Then
    Rows("77:77").Select
    Selection.EntireRow.Hidden = True
    End If

    .....and so on all the way to row 115.
    At each row it needs to see if the cell in column C is empty, and if it is then hide the entire row.

    I can of course just carry on as I am manually entering each row number, but there must be a quicker way.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Looping Code (xl97)

    Try this:

    Dim lngRow As Long
    For lngRow = 74 To 115
    If Range("C" & lngRow) = "" Then
    Range("C" & lngRow).EntireRow.Hidden = True
    End If
    Next lngRow

    Instead of Range("C" & lngRow), you can also use Cells(lngRow, 3). Note that the code doesn't actually select anything, it just specifies a range to be hidden. This is usually more efficient than selecting each range in turn.

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

    Re: Looping Code (xl97)

    Many thanks Hans.
    Perfect!

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

    Re: Looping Code (xl97)

    Yet another way:

    <pre>Dim ocell As Range
    For Each ocell In ActiveSheet.Range("C74:C115")
    If ocell.Value = "" Then ocell.EntireRow.Hidden = True
    Next ocell
    </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
  •