VBA Row Hiding Code Not Responding As Expected

**StevenJ19** · 06-14-2023, 07:16 AM

Hello.

I'm currently using VBA which is aimed at a Cell, B5. Once the Cell updates the code below hides or un-hides rows. However the section for CPC 0700000 does not want to do anything, but the code for CPC 4000000 and 7100000 works perfectly.
Can anyone point me in the direction of why this is not working as I expect it to?

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("B5"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "CPC 4000000": Rows("30:35").EntireRow.Hidden = True
Rows("36").EntireRow.Hidden = False
Rows("37:39").EntireRow.Hidden = True
Rows("43:44").EntireRow.Hidden = False
Case Is = "CPC 0700000": Rows("32:36").EntireRow.Hidden = True
Rows("30:39").EntireRow.Hidden = False
Rows("43:44").EntireRow.Hidden = False
Case Is = "CPC 7100000": Rows("30:35").EntireRow.Hidden = False
Rows("36:39").EntireRow.Hidden = True
Rows("43:44").EntireRow.Hidden = True
End Select
End If
End Sub

**TMS** · 06-14-2023, 09:29 AM

Check the sequence of events here:

        Rows("32:36").EntireRow.Hidden = True    ' hide
        Rows("30:39").EntireRow.Hidden = False   ' unhide
        Rows("43:44").EntireRow.Hidden = False   ' unhide

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
' ActiveSheet.Activate  ' this is redundant

If Not Application.Intersect(Range("B5"), Target) Is Nothing Then
    Select Case Target.Value
    Case Is = "CPC 4000000"
        Rows("30:35").EntireRow.Hidden = True
        Rows("36").EntireRow.Hidden = False
        Rows("37:39").EntireRow.Hidden = True
        Rows("43:44").EntireRow.Hidden = False
    Case Is = "CPC 0700000"
        Rows("30:39").EntireRow.Hidden = False
        Rows("43:44").EntireRow.Hidden = False
        Rows("32:36").EntireRow.Hidden = True
    Case Is = "CPC 7100000"
        Rows("30:35").EntireRow.Hidden = False
        Rows("36:39").EntireRow.Hidden = True
        Rows("43:44").EntireRow.Hidden = True
    End Select
End If

End Sub

VBA Row Hiding Code Not Responding As Expected

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VBA Row Hiding Code Not Responding As Expected

Re: VBA Row Hiding Code Not Responding As Expected

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] why the code is not responding on the second sheet?

[SOLVED] ListBox and ComboBox in UserForm not responding to code

Command buttons despite VBA code are not responding.

Code stall at .Pictures.Insert URL - server not responding?

[SOLVED] Hiding column causes VBA code to behave not as expected

[SOLVED] Non Responding Excel Workbook VBA Code

[SOLVED] Excel not responding, for this code.

Bookmarks

Bookmarks

Posting Permissions