VBA Code to hide rows if Cells = 0 or blank

R

robertk93

New Member
Joined
Dec 19, 2023
Messages
34
Office Version
  1. 365
Platform
  1. Windows
I am working on two different sheets within the same workbook. I have cell headers for certain sections- that when sub items have a value, the header auto fills, thus I need to use a formula similar to this above (I would guess). Any help is much appreciated!

I have one sheet where I input the value and if a certain line item has a number in it (not blank), then it would move over to the other sheet with the line item description, etc. I also have different sections that certain line items fall under.
 
Sort by date Sort by votes
Joe4 said:
To suppress the screen from flickering, place this line of code at the very beginning:
Rich (BB code): 
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    ...
and then place this line of code at the very end:
Rich (BB code): 
    ...
    Application.ScreenUpdating = True
End Sub
Click to expand...
It still flickers. The further along I go down the sheet- the worse the flickering gets. Could it be because I have some formulas on the Proforma sheet that carry over to the customer sheet? It only started doing this when I added the protection. Thanks!
 
Last edited:
Upvote 0

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
When posting your code, PLEASE used the code tags, as shown here: How to Post Your VBA Code
Posting, long unformatted code like you have makes the code very hard to read/decipher.

robertk93 said:
It still flickers. The further along I go down the sheet- the worse the flickering gets. Could it be because I have some formulas on the Proforma sheet that carry over to the customer sheet?
Click to expand...
I am not sure what else you may have going on that might be interfering (perhaps other VBA code).
You can add that logic from my previous post in EVERY VBA procedure you have.
You may want to try that and see if that helps.
 
Upvote 0
Joe4 said:
When posting your code, PLEASE used the code tags, as shown here: How to Post Your VBA Code
Posting, long unformatted code like you have makes the code very hard to read/decipher.


I am not sure what else you may have going on that might be interfering (perhaps other VBA code).
You can add that logic from my previous post in EVERY VBA procedure you have.
You may want to try that and see if that helps.
Click to expand...
Apologies. Here you go. Where would I put things? Thank you!

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Sheets("Customer").Unprotect Password:="Encore1"
'   Only run if one single cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub
'   Only run in cell is range B24:B33 is updated
    If Not Intersect(Target, Range("24:33")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(24).Hidden = False
        Else
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(24).Hidden = True
    
    End If
    End If
    Sheets("Customer").Protect Password:="Encore1"
    '   Only run if one single cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub
'       Only run in cell is range B34:B46 is updated
    If Not Intersect(Target, Range("34:46")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(34).Hidden = False
        Else
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(34).Hidden = True
    End If
    End If
    Sheets("Customer").Protect Password:="Encore1"
    '   Only run if one single cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub
'       Only run in cell is range B47:B53 is updated
    If Not Intersect(Target, Range("47:53")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(47).Hidden = False
        Else
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(47).Hidden = True
    End If
    End If
    Sheets("Customer").Protect Password:="Encore1"
    '   Only run if one single cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub
'       Only run in cell is range 54:59 is updated
    If Not Intersect(Target, Range("54:59")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(54).Hidden = False
        Else
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(54).Hidden = True
    End If
    End If
    Sheets("Customer").Protect Password:="Encore1"
    '   Only run if one single cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub
'       Only run in cell is range B60:B73 is updated
    If Not Intersect(Target, Range("60:73")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(60).Hidden = False
        Else
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(60).Hidden = True
    End If
    End If
    Sheets("Customer").Protect Password:="Encore1"
    '   Only run if one single cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub
'       Only run in cell is range B74:B78 is updated
    If Not Intersect(Target, Range("74:78")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(74).Hidden = False
        Else
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(74).Hidden = True
    End If
    End If
    Sheets("Customer").Protect Password:="Encore1"
    '   Only run if one single cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub
'       Only run in cell is range B79:B82 is updated
    If Not Intersect(Target, Range("79:82")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(79).Hidden = False
        Else
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(79).Hidden = True
    End If
    End If
    Sheets("Customer").Protect Password:="Encore1"
    '   Only run if one single cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub
'       Only run in cell is range B83:B87 is updated
    If Not Intersect(Target, Range("83:87")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(83).Hidden = False
        Else
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(83).Hidden = True
    End If
    End If
    Sheets("Customer").Protect Password:="Encore1"
    '   Only run if one single cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub
'       Only run in cell is range B88:B96 is updated
    If Not Intersect(Target, Range("88:96")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(88).Hidden = False
        Else
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(88).Hidden = True
    End If
    End If
    Sheets("Customer").Protect Password:="Encore1"
    '   Only run if one single cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub
'       Only run in cell is range B97:B127 is updated
    If Not Intersect(Target, Range("97:127")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(97).Hidden = False
        Else
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(97).Hidden = True
    End If
    End If
        Sheets("Customer").Protect Password:="Encore1"
Application.ScreenUpdating = True

End Sub
 
Upvote 0
You should NOT have this line of code multiple times in your code, just once at the top:
VBA Code: 
    If Target.CountLarge > 1 Then Exit Sub

So, I think it would be best to restructure the beginning of your code like this:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

'   Only run if one single cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub

    Application.ScreenUpdating = False

    Sheets("Customer").Unprotect Password:="Encore1"
    ...
The "Target" range is constant throughout the whole code, it does not change.
So there is no need to check it more than once in the code.

robertk93 said:
Apologies. Here you go. Where would I put things? Thank you!
Click to expand...
What I am saying is to add the two lines of code mentioned in post 30 to EVERY single VBA procedure you have in your workbook.
So for example, in your "account" procedure, you would update it like this:
Rich (BB code): 
Sub account()
    Application.ScreenUpdating = False
    ...
    Application.ScreenUpdating = True
End Sub
and every other procedure in this manner.
 
Upvote 0
Joe4 said:
You should NOT have this line of code multiple times in your code, just once at the top:
VBA Code: 
    If Target.CountLarge > 1 Then Exit Sub

So, I think it would be best to restructure the beginning of your code like this:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

'   Only run if one single cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub

    Application.ScreenUpdating = False

    Sheets("Customer").Unprotect Password:="Encore1"
    ...
The "Target" range is constant throughout the whole code, it does not change.
So there is no need to check it more than once in the code.


What I am saying is to add the two lines of code mentioned in post 30 to EVERY single VBA procedure you have in your workbook.
Click to expand...
so, something like this? I did that, and everytime I enter a value, i get the blue loading circle and it flickers.

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Sheets("Customer").Unprotect Password:="Encore1"
'   Only run if one single cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub
'   Only run in cell is range B24:B33 is updated
    If Not Intersect(Target, Range("24:33")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(24).Hidden = False
        Else
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(24).Hidden = True
 Application.ScreenUpdating = True
    End If
    End If
Application.ScreenUpdating = False
    Sheets("Customer").Protect Password:="Encore1"
    '   Only run if one single cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub
'       Only run in cell is range B34:B46 is updated
    If Not Intersect(Target, Range("34:46")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(34).Hidden = False
        Else
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(34).Hidden = True
            Application.ScreenUpdating = True
    End If
    End If
Application.ScreenUpdating = False
    Sheets("Customer").Protect Password:="Encore1"
    '   Only run if one single cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub
'       Only run in cell is range B47:B53 is updated
    If Not Intersect(Target, Range("47:53")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(47).Hidden = False
        Else
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(47).Hidden = True
            Application.ScreenUpdating = True
    End If
    End If
    Application.ScreenUpdating = False
    Sheets("Customer").Protect Password:="Encore1"
    '   Only run if one single cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub
'       Only run in cell is range 54:59 is updated
    If Not Intersect(Target, Range("54:59")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(54).Hidden = False
        Else
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(54).Hidden = True
            Application.ScreenUpdating = True
    End If
    End If
    Application.ScreenUpdating = False
    Sheets("Customer").Protect Password:="Encore1"
    '   Only run if one single cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub
'       Only run in cell is range B60:B73 is updated
    If Not Intersect(Target, Range("60:73")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(60).Hidden = False
        Else
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(60).Hidden = True
            Application.ScreenUpdating = True
    End If
    End If
    Application.ScreenUpdating = False
    Sheets("Customer").Protect Password:="Encore1"
    '   Only run if one single cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub
'       Only run in cell is range B74:B78 is updated
    If Not Intersect(Target, Range("74:78")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(74).Hidden = False
        Else
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(74).Hidden = True
            Application.ScreenUpdating = True
    End If
    End If
    Application.ScreenUpdating = False
    Sheets("Customer").Protect Password:="Encore1"
    '   Only run if one single cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub
'       Only run in cell is range B79:B82 is updated
    If Not Intersect(Target, Range("79:82")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(79).Hidden = False
        Else
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(79).Hidden = True
            Application.ScreenUpdating = True
    End If
    End If
    Application.ScreenUpdating = False
    Sheets("Customer").Protect Password:="Encore1"
    '   Only run if one single cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub
'       Only run in cell is range B83:B87 is updated
    If Not Intersect(Target, Range("83:87")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(83).Hidden = False
        Else
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(83).Hidden = True
            Application.ScreenUpdating = True
    End If
    End If
    Application.ScreenUpdating = False
    Sheets("Customer").Protect Password:="Encore1"
    '   Only run if one single cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub
'       Only run in cell is range B88:B96 is updated
    If Not Intersect(Target, Range("88:96")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(88).Hidden = False
        Else
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(88).Hidden = True
            Application.ScreenUpdating = True
    End If
    End If
    Application.ScreenUpdating = False
    Sheets("Customer").Protect Password:="Encore1"
    '   Only run if one single cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub
'       Only run in cell is range B97:B127 is updated
    If Not Intersect(Target, Range("97:127")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(97).Hidden = False
        Else
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(97).Hidden = True
            Application.ScreenUpdating = True
    End If
    End If
        Sheets("Customer").Protect Password:="Encore1"
Application.ScreenUpdating = True

End Sub
 
Upvote 0
Please re-read the entirety of my previous post!
You have not structured the code in the manner I recommended there. You need to have the CountLarge check BEFORE you shut off the screen updating.

But that should not affect the screen flickering at all. The second part of that post, asking you to add that logic to all your other procedures may help with that.
 
Upvote 0
Okay, does this look right before adding the rest? Where would I add the True statements?

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'   Only run if one single cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub
 Application.ScreenUpdating = False
    Sheets("Customer").Unprotect Password:="Encore1"
'   Only run in cell is range B24:B33 is updated
    If Not Intersect(Target, Range("24:33")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(24).Hidden = False
        Else
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(24).Hidden = True

    End If
    End If

    Sheets("Customer").Protect Password:="Encore1"

'       Only run in cell is range B34:B46 is updated
    If Not Intersect(Target, Range("34:46")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(34).Hidden = False
        Else
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(34).Hidden = True
         
    End If
    End If

    Sheets("Customer").Protect Password:="Encore1"

'       Only run in cell is range B47:B53 is updated
    If Not Intersect(Target, Range("47:53")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(47).Hidden = False
        Else
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(47).Hidden = True
            Application.ScreenUpdating = True
    End If
    End If

    Sheets("Customer").Protect Password:="Encore1"

'       Only run in cell is range 54:59 is updated
    If Not Intersect(Target, Range("54:59")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(54).Hidden = False
        Else
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(54).Hidden = True
            Application.ScreenUpdating = True
    End If
    End If

    Sheets("Customer").Protect Password:="Encore1"

'       Only run in cell is range B60:B73 is updated
    If Not Intersect(Target, Range("60:73")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(60).Hidden = False
        Else
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(60).Hidden = True
           
    End If
    End If
 
    Sheets("Customer").Protect Password:="Encore1"

'       Only run in cell is range B74:B78 is updated
    If Not Intersect(Target, Range("74:78")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(74).Hidden = False
        Else
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(74).Hidden = True
          
    End If
    End If
   
    Sheets("Customer").Protect Password:="Encore1"

'       Only run in cell is range B79:B82 is updated
    If Not Intersect(Target, Range("79:82")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(79).Hidden = False
        Else
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(79).Hidden = True
           
    End If
    End If
  
    Sheets("Customer").Protect Password:="Encore1"

'       Only run in cell is range B83:B87 is updated
    If Not Intersect(Target, Range("83:87")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(83).Hidden = False
        Else
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(83).Hidden = True
           
    End If
    End If
 
    Sheets("Customer").Protect Password:="Encore1"

'       Only run in cell is range B88:B96 is updated
    If Not Intersect(Target, Range("88:96")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(88).Hidden = False
        Else
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(88).Hidden = True
         
    End If
    End If

    Sheets("Customer").Protect Password:="Encore1"
 
'       Only run in cell is range B97:B127 is updated
    If Not Intersect(Target, Range("97:127")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(97).Hidden = False
        Else
'           Unhide similar row on Customer sheet
            Sheets("Customer").Unprotect Password:="Encore1"
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(97).Hidden = True
            Application.ScreenUpdating = True
    End If
    End If
        Sheets("Customer").Protect Password:="Encore1"
Application.ScreenUpdating = True

End Sub
 
Upvote 0
robertk93 said:
Okay, does this look right before adding the rest? Where would I add the True statements?
Click to expand...
I think you are getting confused.
Do you understand what a single Procedure is? A "Procedure" is another name for a "Macro".
A procedure begins with a line like "Private Sub", "Sub", or "Public Sub" and ends with "End Sub".

What you posted is just one, single Procedure.
As such, you should only have those lines in there at the very start and very end (like I showed you). You should not need them anywhere in the middle of the code, since you do not have any "Exit Sub" lines in the middle of your code.

So, remove all the "Application.ScreenUpdating..." lines you have in the middle of your code.

What I was recommending is if you still have screen flickering after doing that, is to add that logic to all your OTHER procedures.

On a side note, you also have other unnecessary redundancies in your code, like this line here:
VBA Code: 
           Sheets("Customer").Unprotect Password:="Encore1"
Since we have that "Unprotect" line at the beginning of our code, and a "Protect" line at the end of your code, you can get rid of all the unnecessary redundancies in the middle of your code.
 
Upvote 0
Joe4 said:
I think you are getting confused.
Do you understand what a single Procedure is? A "Procedure" is another name for a "Macro".
A procedure begins with a line like "Private Sub", "Sub", or "Public Sub" and ends with "End Sub".

What you posted is just one, single Procedure.
As such, you should only have those lines in there at the very start and very end (like I showed you). You should not need them anywhere in the middle of the code, since you do not have any "Exit Sub" lines in the middle of your code.

So, remove all the "Application.ScreenUpdating..." lines you have in the middle of your code.

What I was recommending is if you still have screen flickering after doing that, is to add that logic to all your OTHER procedures.

On a side note, you also have other unnecessary redundancies in your code, like this line here:
VBA Code: 
           Sheets("Customer").Unprotect Password:="Encore1"
Since we have that "Unprotect" line at the beginning of our code, and a "Protect" line at the end of your code, you can get rid of all the unnecessary redundancies in the middle of your code.I e
Click to expand...
Thank you, I was getting confused. Please see updated code and let me know. I am still getting screen flickering and lagging.

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'   Only run if one single cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub
 Application.ScreenUpdating = False
    Sheets("Customer").Unprotect Password:="Encore1"
'   Only run in cell is range B24:B33 is updated
    If Not Intersect(Target, Range("24:33")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
          
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(24).Hidden = False
        Else
'           Unhide similar row on Customer sheet
            
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(24).Hidden = True

    End If
    End If

   

'       Only run in cell is range B34:B46 is updated
    If Not Intersect(Target, Range("34:46")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(34).Hidden = False
        Else
'           Unhide similar row on Customer sheet
           
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(34).Hidden = True
         
    End If
    End If



'       Only run in cell is range B47:B53 is updated
    If Not Intersect(Target, Range("47:53")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(47).Hidden = False
        Else
'           Unhide similar row on Customer sheet
       
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(47).Hidden = True

    End If
    End If

   

'       Only run in cell is range 54:59 is updated
    If Not Intersect(Target, Range("54:59")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(54).Hidden = False
        Else
'           Unhide similar row on Customer sheet
         
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(54).Hidden = True

    End If
    End If

  

'       Only run in cell is range B60:B73 is updated
    If Not Intersect(Target, Range("60:73")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
         
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(60).Hidden = False
        Else
'           Unhide similar row on Customer sheet
          
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(60).Hidden = True
           
    End If
    End If
 
 

'       Only run in cell is range B74:B78 is updated
    If Not Intersect(Target, Range("74:78")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
           
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(74).Hidden = False
        Else
'           Unhide similar row on Customer sheet
           
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(74).Hidden = True
          
    End If
    End If
   
   

'       Only run in cell is range B79:B82 is updated
    If Not Intersect(Target, Range("79:82")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
           
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(79).Hidden = False
        Else
'           Unhide similar row on Customer sheet
          
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(79).Hidden = True
           
    End If
    End If
  


'       Only run in cell is range B83:B87 is updated
    If Not Intersect(Target, Range("83:87")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
            
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(83).Hidden = False
        Else
'           Unhide similar row on Customer sheet
          
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(83).Hidden = True
           
    End If
    End If
 
  
'       Only run in cell is range B88:B96 is updated
    If Not Intersect(Target, Range("88:96")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
          
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(88).Hidden = False
        Else
'           Unhide similar row on Customer sheet
        
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(88).Hidden = True
         
    End If
    End If

  
 
'       Only run in cell is range B97:B127 is updated
    If Not Intersect(Target, Range("97:127")) Is Nothing Then
'       See if non-blank value added in to cell
        If Target.Value <> "" Then
'           Unhide similar row on Customer sheet
          
            Sheets("Customer").Rows(Target.Row).Hidden = False
            Sheets("Customer").Rows(97).Hidden = False
        Else
'           Unhide similar row on Customer sheet
            
            Sheets("Customer").Rows(Target.Row).Hidden = True
            Sheets("Customer").Rows(97).Hidden = True
       
    End If
    End If
        Sheets("Customer").Protect Password:="Encore1"
Application.ScreenUpdating = True

End Sub
 
Upvote 0
robertk93 said:
I am still getting screen flickering and lagging.
Click to expand...
Did you add those lines of code to ALL of your other procedures, like I suggested?
Exactly how long does the code take to run?
I understand that there is flickering and lagging, but is it actually doing what it is supposed to?
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Hiding rows based on cell value
Replies
2
Views
112
smozgur
smozgur
N
  • Question
Formula help to search a table for a date greater than today that has a non-blank cell and return the date in a separate cell.
Replies
2
Views
167
Alex Blakenburg
A
J
  • Question
Help!!
Replies
1
Views
71
AlphaFrog
AlphaFrog
X
  • Question
VBA code to move data from Pivot Table to unhidden columns on a different sheet?
Replies
0
Views
141
xxbc320xx
X
M
  • Question
VBA hides/unhides columns based upon pivot table and value
Replies
0
Views
145
mapatton
M

Forum statistics

Threads
1,215,091
Messages
6,123,062
Members
449,089
Latest member
ikke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top