Hiding Rows with Excel 2013

2
votes

So I am attempting to hide rows in Excel 2013 using VBA based a several different conditions:

  • If title of section is "Unused" hide section. Each section is a named range to make this easier.
  • If row is part of the "Cblank" named range hide it.
  • Now for the hard part -- For each Cell in Range("CNonTest") if C.Value = "" and C.Columns(41).Value = "" Then hide them.

Range("CNonTest") is in Col C the extra column that should be check is Col AQ.

For added difficulty I need this macro to run every time any 1 of 8 different validation boxes changes.

Below is the code I currently have:

    Sub CompHide()

    With Sheets("Comparison").Cells
       .EntireRow.Hidden = False

    If Range("C9").Value = "Unused" Then
        Range("CMarket1").EntireRow.Hidden = True
    End If

    If Range("C115").Value = "Unused" Then
        Range("CMarket2").EntireRow.Hidden = True
    End If

    If Range("C221").Value = "Unused" Then
        Range("CMarket3").EntireRow.Hidden = True
    End If

    If Range("C329").Value = "Unused" Then
        Range("CMarket4").EntireRow.Hidden = True
    End If

    If Range("C437").Value = "Unused" Then
        Range("CMarket5").EntireRow.Hidden = True
    End If

    If Range("C545").Value = "Unused" Then
        Range("CMarket6").EntireRow.Hidden = True
    End If

    If Range("C653").Value = "Unused" Then
        Range("CMarket7").EntireRow.Hidden = True
    End If

    If Range("C761").Value = "Unused" Then
        Range("CMarket8").EntireRow.Hidden = True
    End If

    If Range("C869").Value = "Unused" Then
        Range("CMarket9").EntireRow.Hidden = True
    End If

    If Range("C977").Value = "Unused" Then
        Range("CMarket10").EntireRow.Hidden = True
    End If

    For Each C In Range("CNonTest")
        If C.Value = "" And C.Columns(41).Value = "" Then
            C.EntireRow.Hidden = True
        End If
    Next



    Range("CBlank").EntireRow.Hidden = True

    End With
End Sub

Then on the Sheet I have this code:

    Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Me.Range("A4")) Is Nothing _
    Or _
    Intersect(Target, Me.Range("D4")) Is Nothing _
    Or _
    Intersect(Target, Me.Range("G4")) Is Nothing _
    Or _
    Intersect(Target, Me.Range("K4")) Is Nothing _
    Or _
    Intersect(Target, Me.Range("AO4")) Is Nothing _
    Or _
    Intersect(Target, Me.Range("AR4")) Is Nothing _
    Or _
    Intersect(Target, Me.Range("AU4")) Is Nothing _
    Or _
    Intersect(Target, Me.Range("AY4")) Is Nothing _
    Then Exit Sub

    Application.EnableEvents = False 'to prevent endless loop
    Application.ScreenUpdating = False

    Call CompHide

    Application.ScreenUpdating = True
    Application.EnableEvents = True


End Sub

For the Sheet Code I have also tried this to no avail

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Me.Range("A4")) Is Nothing Then Exit Sub

    Application.EnableEvents = False 'to prevent endless loop
    Application.ScreenUpdating = False

    Call CompHide

    Application.ScreenUpdating = True
    Application.EnableEvents = True



If Intersect(Target, Me.Range("D4")) Is Nothing Then Exit Sub

    Application.EnableEvents = False 'to prevent endless loop
    Application.ScreenUpdating = False

    Call CompHide

    Application.ScreenUpdating = True
    Application.EnableEvents = True



If Intersect(Target, Me.Range("G4")) Is Nothing Then Exit Sub

    Application.EnableEvents = False 'to prevent endless loop
    Application.ScreenUpdating = False

    Call CompHide

    Application.ScreenUpdating = True
    Application.EnableEvents = True



If Intersect(Target, Me.Range("K4")) Is Nothing Then Exit Sub

    Application.EnableEvents = False 'to prevent endless loop
    Application.ScreenUpdating = False

    Call CompHide

    Application.ScreenUpdating = True
    Application.EnableEvents = True



If Intersect(Target, Me.Range("AO4")) Is Nothing Then Exit Sub

    Application.EnableEvents = False 'to prevent endless loop
    Application.ScreenUpdating = False

    Call CompHide

    Application.ScreenUpdating = True
    Application.EnableEvents = True



If Intersect(Target, Me.Range("AR4")) Is Nothing Then Exit Sub

    Application.EnableEvents = False 'to prevent endless loop
    Application.ScreenUpdating = False

    Call CompHide

    Application.ScreenUpdating = True
    Application.EnableEvents = True



If Intersect(Target, Me.Range("AU4")) Is Nothing Then Exit Sub

    Application.EnableEvents = False 'to prevent endless loop
    Application.ScreenUpdating = False

    Call CompHide

    Application.ScreenUpdating = True
    Application.EnableEvents = True


If Intersect(Target, Me.Range("AY4")) Is Nothing Then Exit Sub

    Application.EnableEvents = False 'to prevent endless loop
    Application.ScreenUpdating = False

    Call CompHide

    Application.ScreenUpdating = True
    Application.EnableEvents = True


End Sub

This code all seems to work fine and when I step through CompHide using F8 it works perfectly. So I am thinking the issue is from the code on the sheet itself. You will see a comment in that code that mentions to prevent endless loop that comment came from some hand me down code not quite sure what it is for but figured based on the comment I would leave it.

When I change a validation box it no longer hides the all the right things only some of them. Luckily I have not seen it hide something it was not suppose to yet. I say no longer because at first this code only looked at the first validation box but now it looks at all 8.

2
vbaexcelexcel-2013

2 Answers

4
votes

Some adjustments to your event handler:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range

    On Error GoTo haveError

    Set rng = Application.Intersect(Target, Me.Range("A4,D4,G4,K4,AO4,AR4,AU4,AY4"))

    If Not rng Is Nothing Then
        Application.EnableEvents = False 'to prevent endless loop
        Application.ScreenUpdating = False
        CompHide
        Application.EnableEvents = True
    End If
    Exit Sub

haveError:
    'always re-enable events
    '  (screenupdating setting is not persistent)...
    Application.EnableEvents = True

End Sub

and the other part:

Sub CompHide()

    Dim sht As Worksheet, C As Range

    Set sht = Sheets("Comparison")
    sht.Rows.Hidden = False

    SetRowVis "C9", "CMarket1"
    SetRowVis "C115", "CMarket2"
    '...and the rest

    For Each C In sht.Range("CNonTest")
        If C.Value = "" And C.EntireRow.Columns(43).Value = "" Then
            C.EntireRow.Hidden = True
        End If
    Next

    sht.Range("CBlank").EntireRow.Hidden = True
End Sub

'utility sub...
Sub SetRowVis(addr As String, rngName As String)
    With Sheets("Comparison")
        If .Range(addr).Value = "Unused" Then
            .Range(rngName).EntireRow.Hidden = True
        End If
    End With
End Sub
1
votes

1st, you have referencing issue on your CompHide Sub.
You need to fully reference all Range object call to the worksheet.

With Sheets("Comparison")
    .Cells.EntireRow.Hidden = False
    'Notice the dot in front of the Range object
    If .Range("C9").Value = "Unused" Then .Range("CMarket1").EntireRow.Hidden = True
    'Also notice that I used a one liner IF which I think is applicable for you
    'Rest of your code go here
    '.
    '.
    '.

End With

2nd, take a look on Tim's post. He beats me to it. :)