Results 1 to 10 of 10
  1. #1
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Hiding Non-Contiguous Rows in Target (Excel 2002)

    I seem to be having problems in the worksheet change event when I have non-contiguous rows in the target object.

    Once I select the non-contiguous rows I press the delete key. The change event checks if all the cells in the target row are blank. If so, hide that row. If not do not hide it but check the next row in the target until all the rows are checked.

    The following code works for one row but doesn
    You know it's time to diet when you push away from the table and the table moves.

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

    Re: Hiding Non-Contiguous Rows in Target (Excel 2002)

    If you look up the Rows property in the online help, you will see that if your range consists of multiple non-contiguous areas, Rows only represents the rows in the first area. You must loop through the areas of the range, then through the rows of the area:
    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rCell As Range
    Dim rArea As Range
    Dim rTarget As Range
    Dim booHide As Boolean
    Dim rRow As Range
    Dim i As Long

    Set rTarget = Intersect(Target, Range("C8:M23"))
    If Not rTarget Is Nothing Then
    For Each rArea In rTarget.Areas
    'Process one row at a time in target
    For i = rArea.Row To rArea.Row + rArea.Rows.Count - 1
    MsgBox "Processing row " & i
    booHide = True
    'Check if all cells between columns C and M are non blank
    'on the target row
    For Each rCell In Range("C" & i & ":" & "M" & i)
    'If not blank, do not hide row
    If rCell <> "" Then
    booHide = False
    End If
    Next rCell
    If booHide Then
    Range(i & ":" & i).EntireRow.Hidden = True
    End If
    Next i
    Next rArea
    End If
    Set rCell = Nothing
    Set rArea = Nothing
    Set rTarget = Nothing
    End Sub
    </pre>

    This should work, although it would make me very nervous as a user.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Hiding Non-Contiguous Rows in Target (Excel 2002)

    Noncontiguous ranges are different than contiguous ranges. Each "contiguous" part of the non-contiguous range is an "area" if you go thru as you are doing, VB only works with areas(1). If your first area had only 1 row (which I suspect) your code only worked on this 1 region. try this:

    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rCell As Range
    Dim booHide As Boolean
    Dim rRow As Range
    Dim rArea As Range

    Dim i As Long

    If Not Intersect(Target, Range("C8:M23")) Is Nothing Then
    'Process one row at a time in target
    For Each rArea In Target.Areas
    For i = rArea.Row To rArea.Row + rArea.Rows.Count - 1 '< Problem here???
    MsgBox "Processing row " & i
    booHide = True
    'Check if all cells between columns C and M are non blank
    'on the target row
    For Each rCell In Range("C" & i & ":" & "M" & i)
    'If not blank, do not hide row
    If rCell <> "" Then
    booHide = False
    End If
    Next rCell

    If booHide Then
    Range(i & ":" & i).EntireRow.Hidden = True
    End If
    Next i
    Next
    End If
    End Sub

    Steve

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

    Re: Hiding Non-Contiguous Rows in Target (Excel 2002)

    You need to modify your code a little. When the selection is made up of multiple areas, the Target object is made up of a collection of areas. Try this:

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rCell As Range
    Dim booHide As Boolean
    Dim rRow As Range
    Dim i As Long
    Dim oRng As Range
    If Not Intersect(Target, Range("C8:M23")) Is Nothing Then
    'Process one row at a time in target
    For Each oRng In Target.Areas
    For i = oRng.Row To oRng.Row + oRng.Rows.Count - 1
    MsgBox "Processing row " & i
    booHide = True
    'Check if all cells between columns C and M are non blank
    'on the target row
    For Each rCell In Range("C" & i & ":" & "M" & i)
    'If not blank, do not hide row
    If rCell <> "" Then
    booHide = False
    Exit For
    End If
    Next rCell
    If booHide Then
    Range(i & ":" & i).EntireRow.Hidden = True
    End If
    Next i
    Next oRng
    End If
    End Sub
    </pre>

    Legare Coleman

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

    Re: Hiding Non-Contiguous Rows in Target (Excel 2002)

    You should get the idea by now! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  6. #6
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Hiding Non-Contiguous Rows in Target (Excel 2002)

    Thank you Hans. It makes sense that there must be something like an Areas object to handle non-contiguous rows as different areas. I have one more problem. Right in the middle of the code there is this statement: If rCell <> "" then ... It's giving me a type mismatch. The cell that is having a problem has a vlookup formula in it that returns #N/A. It doesn't seem to like comparing the rCell error value to zero-length string. The other cells are okay. It there a way to get around the problem?
    You know it's time to diet when you push away from the table and the table moves.

  7. #7
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Hiding Non-Contiguous Rows in Target (Excel 2002)

    Thank you Steve. The Areas object was right on. I'll remember that object next time.
    You know it's time to diet when you push away from the table and the table moves.

  8. #8
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Hiding Non-Contiguous Rows in Target (Excel 2002)

    Hi Legare. The Areas object did the trick. It makes sense since I am dealing with two or more different areas of the spreadsheet. Thank you for the solution!
    You know it's time to diet when you push away from the table and the table moves.

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

    Re: Hiding Non-Contiguous Rows in Target (Excel 2002)

    Comparing an error value to anything results in an error. Try

    If IsError(rCell) Then
    booHide = False
    ElseIf rCell <> "" Then
    booHide = False
    End If

    If the cell contains an error value, it is not empty, so the row should not be hidden.

  10. #10
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Hiding Non-Contiguous Rows in Target (Excel 2002)

    You are right that it shouldn't be hidden. That's exactly what I will do. I had to take into account that situation just in case. Thanks again for the speed reply.

    Wow, three answers and all three were right on. You guys are great! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    You know it's time to diet when you push away from the table and the table moves.

Posting Permissions

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