Hiding Non-Contiguous Rows in Target (Excel 2002)

Topic

New Reply

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

Reply | Quote

Replies

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:

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

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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!

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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:

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

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

Reply | Quote

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

Reply | Quote

Thank you Steve. The Areas object was right on. I’ll remember that object next time.

Reply | Quote

Thank you Steve. The Areas object was right on. I’ll remember that object next time.

Reply | Quote

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

Reply | Quote

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:

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

Reply | Quote

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!

Reply | Quote

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!

Reply | Quote

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:

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

Reply | Quote

You should get the idea by now!

Reply | Quote

You should get the idea by now!

Reply | Quote