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
robertk93 said:
Thank you. Makes sense. So here is the first block of 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

' Only run in cell is range B24:B127 is updated
If Not Intersect(Target, Range("B24:B127")) 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
Else
' Unhide similar row on Customer sheet
Sheets("Customer").Rows(Target.Row).Hidden = True
End If
End If
End Sub

Where and how would I start the next section of code? I keep getting errors when adding it in.
Click to expand...
Okay- I figured it out. I had to put in on the code for the customer sheet- which is fine- it is working.

My last question- I have this code, but how do I make sure it runs automatically without having to press F5?

Sub account()
If Application.WorksheetFunction.CountIf(Range("B25:B33"), "") < 9 Then
Rows("24").Hidden = False
Else
Rows("24").Hidden = True
End If
End Sub
Sub depository()
If Application.WorksheetFunction.CountIf(Range("B35:B46"), "") < 12 Then
Rows("34").Hidden = False
Else
Rows("34").Hidden = True
End If
End Sub
Sub information()
If Application.WorksheetFunction.CountIf(Range("B48:B53"), "") < 6 Then
Rows("47").Hidden = False
Else
Rows("47").Hidden = True
End If
End Sub
Sub rdc()
If Application.WorksheetFunction.CountIf(Range("B55:B59"), "") < 5 Then
Rows("54").Hidden = False
Else
Rows("54").Hidden = True
End If
End Sub
Sub ach()
If Application.WorksheetFunction.CountIf(Range("B61:B73"), "") < 13 Then
Rows("60").Hidden = False
Else
Rows("60").Hidden = True
End If
End Sub
Sub funds()
If Application.WorksheetFunction.CountIf(Range("B75:B78"), "") < 4 Then
Rows("74").Hidden = False
Else
Rows("74").Hidden = True
End If
End Sub
Sub payables()
If Application.WorksheetFunction.CountIf(Range("B80:B82"), "") < 3 Then
Rows("79").Hidden = False
Else
Rows("79").Hidden = True
End If
End Sub
Sub wire()
If Application.WorksheetFunction.CountIf(Range("B84:B87"), "") < 4 Then
Rows("83").Hidden = False
Else
Rows("83").Hidden = True
End If
End Sub
Sub fraud()
If Application.WorksheetFunction.CountIf(Range("B89:B96"), "") < 8 Then
Rows("88").Hidden = False
Else
Rows("88").Hidden = True
End If
End Sub
Sub lbx()
If Application.WorksheetFunction.CountIf(Range("B98:B127"), "") < 30 Then
Rows("97").Hidden = False
Else
Rows("97").Hidden = True
End If
End Sub
 
Upvote 0
robertk93 said:
Okay- I figured it out. I had to put in on the code for the customer sheet- which is fine- it is working.

My last question- I have this code, but how do I make sure it runs automatically without having to press F5?
Click to expand...
The other rows hide when I take the value out on the proforma sheet, but the header row stays in the customer sheet. Do I need to do a sheet reference like we did in the first code?
robertk93 said:
Sub account()
If Application.WorksheetFunction.CountIf(Range("B25:B33"), "") < 9 Then
Rows("24").Hidden = False
Else
Rows("24").Hidden = True
End If
End Sub
Sub depository()
If Application.WorksheetFunction.CountIf(Range("B35:B46"), "") < 12 Then
Rows("34").Hidden = False
Else
Rows("34").Hidden = True
End If
End Sub
Sub information()
If Application.WorksheetFunction.CountIf(Range("B48:B53"), "") < 6 Then
Rows("47").Hidden = False
Else
Rows("47").Hidden = True
End If
End Sub
Sub rdc()
If Application.WorksheetFunction.CountIf(Range("B55:B59"), "") < 5 Then
Rows("54").Hidden = False
Else
Rows("54").Hidden = True
End If
End Sub
Sub ach()
If Application.WorksheetFunction.CountIf(Range("B61:B73"), "") < 13 Then
Rows("60").Hidden = False
Else
Rows("60").Hidden = True
End If
End Sub
Sub funds()
If Application.WorksheetFunction.CountIf(Range("B75:B78"), "") < 4 Then
Rows("74").Hidden = False
Else
Rows("74").Hidden = True
End If
End Sub
Sub payables()
If Application.WorksheetFunction.CountIf(Range("B80:B82"), "") < 3 Then
Rows("79").Hidden = False
Else
Rows("79").Hidden = True
End If
End Sub
Sub wire()
If Application.WorksheetFunction.CountIf(Range("B84:B87"), "") < 4 Then
Rows("83").Hidden = False
Else
Rows("83").Hidden = True
End If
End Sub
Sub fraud()
If Application.WorksheetFunction.CountIf(Range("B89:B96"), "") < 8 Then
Rows("88").Hidden = False
Else
Rows("88").Hidden = True
End If
End Sub
Sub lbx()
If Application.WorksheetFunction.CountIf(Range("B98:B127"), "") < 30 Then
Rows("97").Hidden = False
Else
Rows("97").Hidden = True
End If
End Sub
Click to expand...
 
Upvote 0
Sorry, I have been out of town all week.

robertk93 said:
My last question- I have this code, but how do I make sure it runs automatically without having to press F5?
Click to expand...
Go back and read post #10, in which I discuss Events (automated VBA code).
 
Upvote 0
Got it- I see this in that article, but am unsure as to how I can incorporate that into my code I referenced above.

Any help is appreciated.

Thanks!

1703906028493.png
 
Upvote 0
You would either to move the body of your current code out of its current procedure, and into a "Worksheet_Change" procedure.
Or you could simply call your procedure from a new "Worksheet_Change" procedure, i.e.
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Call account
End Sub
Note though that you may want to add conditions on when exactly to call/run this code (i.e. only when certain cells are updated, as shown in the article), i.e.
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If <some condition here> Then
        Call account
    End If
End Sub
 
Upvote 0
Joe4 said:
You would either to move the body of your current code out of its current procedure, and into a "Worksheet_Change" procedure.
Or you could simply call your procedure from a new "Worksheet_Change" procedure, i.e.
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Call account
End Sub
Note though that you may want to add conditions on when exactly to call/run this code (i.e. only when certain cells are updated, as shown in the article), i.e.
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If <some condition here> Then
        Call account
    End If
End Sub
Click to expand...
This is my current code, but it's not running automatically. I'm not sure what you mean by the above.

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.WorksheetFunction.CountIf(Range("B25:B33"), "") < 9 Then
Rows("24").Hidden = False
Else
Rows("24").Hidden = True
End If
If Application.WorksheetFunction.CountIf(Range("B35:B46"), "") < 9 Then
Rows("34").Hidden = False
Else
Rows("34").Hidden = True
End If
If Application.WorksheetFunction.CountIf(Range("B48:B53"), "") < 6 Then
Rows("47").Hidden = False
Else
Rows("47").Hidden = True
End If
If Application.WorksheetFunction.CountIf(Range("B55:B59"), "") < 5 Then
Rows("54").Hidden = False
Else
Rows("54").Hidden = True
End If
If Application.WorksheetFunction.CountIf(Range("B61:B73"), "") < 13 Then
Rows("60").Hidden = False
Else
Rows("60").Hidden = True
End If
If Application.WorksheetFunction.CountIf(Range("B75:B78"), "") < 4 Then
Rows("74").Hidden = False
Else
Rows("74").Hidden = True
End If
If Application.WorksheetFunction.CountIf(Range("B80:B82"), "") < 3 Then
Rows("79").Hidden = False
Else
Rows("79").Hidden = True
End If
If Application.WorksheetFunction.CountIf(Range("B84:B87"), "") < 4 Then
Rows("83").Hidden = False
Else
Rows("83").Hidden = True
End If
If Application.WorksheetFunction.CountIf(Range("B89:B96"), "") < 8 Then
Rows("88").Hidden = False
Else
Rows("88").Hidden = True
End If
If Application.WorksheetFunction.CountIf(Range("B98:B127"), "") < 30 Then
Rows("97").Hidden = False
Else
Rows("97").Hidden = True
End If
End Sub
 
Upvote 0
What is the name of the Module that this code exists in?

What exactly are you doing that should trigger the code to run?
 
Upvote 0
This is the code on my customer sheet, sheet 3. This code below should automatically run when a row on sheet 2, proforma has a value input. The row 24 is the header row for 25-33.
1704210618054.png

This is my code on sheet 2, proforma sheet.

1704210702937.png
 
Upvote 0
Okay, so I figured out the hide/unhide and here is the code. The issue I have now is screen flickering. When I enter something on the Proforma sheet, the screen flickers and kind of lags. Any help for that would be appreciated. Here is the code I have.

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Customer").Unprotect Password:="test"
' 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("B24:B33")) 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:="test"
Sheets("Customer").Rows(Target.Row).Hidden = False
Sheets("Customer").Rows(24).Hidden = False
Else
' Unhide similar row on Customer sheet
Sheets("Customer").Unprotect Password:="test"
Sheets("Customer").Rows(Target.Row).Hidden = True
Sheets("Customer").Rows(24).Hidden = True

End If
End If
Sheets("Customer").Protect Password:="test"
' 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("B34:B46")) 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:="test"
Sheets("Customer").Rows(Target.Row).Hidden = False
Sheets("Customer").Rows(34).Hidden = False
Else
' Unhide similar row on Customer sheet
Sheets("Customer").Unprotect Password:="test"
Sheets("Customer").Rows(Target.Row).Hidden = True
Sheets("Customer").Rows(34).Hidden = True
End If
End If
Sheets("Customer").Protect Password:="test"
' 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("B47:B53")) 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:="test"
Sheets("Customer").Rows(Target.Row).Hidden = False
Sheets("Customer").Rows(47).Hidden = False
Else
' Unhide similar row on Customer sheet
Sheets("Customer").Unprotect Password:="test"
Sheets("Customer").Rows(Target.Row).Hidden = True
Sheets("Customer").Rows(47).Hidden = True
End If
End If
Sheets("Customer").Protect Password:="test"
' 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("B54:B59")) 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:="test"
Sheets("Customer").Rows(Target.Row).Hidden = False
Sheets("Customer").Rows(54).Hidden = False
Else
' Unhide similar row on Customer sheet
Sheets("Customer").Unprotect Password:="test"
Sheets("Customer").Rows(Target.Row).Hidden = True
Sheets("Customer").Rows(54).Hidden = True
End If
End If
Sheets("Customer").Protect Password:="test"
' 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("B60:B73")) 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:="test"
Sheets("Customer").Rows(Target.Row).Hidden = False
Sheets("Customer").Rows(60).Hidden = False
Else
' Unhide similar row on Customer sheet
Sheets("Customer").Unprotect Password:="test"
Sheets("Customer").Rows(Target.Row).Hidden = True
Sheets("Customer").Rows(60).Hidden = True
End If
End If
Sheets("Customer").Protect Password:="test"
' 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("B74:B78")) 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:="test"
Sheets("Customer").Rows(Target.Row).Hidden = False
Sheets("Customer").Rows(74).Hidden = False
Else
' Unhide similar row on Customer sheet
Sheets("Customer").Unprotect Password:="test"
Sheets("Customer").Rows(Target.Row).Hidden = True
Sheets("Customer").Rows(74).Hidden = True
End If
End If
Sheets("Customer").Protect Password:="test"
' 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("B79:B82")) 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:="test"
Sheets("Customer").Rows(Target.Row).Hidden = False
Sheets("Customer").Rows(79).Hidden = False
Else
' Unhide similar row on Customer sheet
Sheets("Customer").Unprotect Password:="test"
Sheets("Customer").Rows(Target.Row).Hidden = True
Sheets("Customer").Rows(79).Hidden = True
End If
End If
Sheets("Customer").Protect Password:="test"
' 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("B83:B87")) 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:="test"
Sheets("Customer").Rows(Target.Row).Hidden = False
Sheets("Customer").Rows(83).Hidden = False
Else
' Unhide similar row on Customer sheet
Sheets("Customer").Unprotect Password:="test"
Sheets("Customer").Rows(Target.Row).Hidden = True
Sheets("Customer").Rows(83).Hidden = True
End If
End If
Sheets("Customer").Protect Password:="test"
' 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("B88:B96")) 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:="test"
Sheets("Customer").Rows(Target.Row).Hidden = False
Sheets("Customer").Rows(88).Hidden = False
Else
' Unhide similar row on Customer sheet
Sheets("Customer").Unprotect Password:="test"
Sheets("Customer").Rows(Target.Row).Hidden = True
Sheets("Customer").Rows(88).Hidden = True
End If
End If
Sheets("Customer").Protect Password:="test"
' 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("B97:B127")) 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:="test"
Sheets("Customer").Rows(Target.Row).Hidden = False
Sheets("Customer").Rows(97).Hidden = False
Else
' Unhide similar row on Customer sheet
Sheets("Customer").Unprotect Password:="test"
Sheets("Customer").Rows(Target.Row).Hidden = True
Sheets("Customer").Rows(97).Hidden = True
End If
End If
Sheets("Customer").Protect Password:="test"
End Sub
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
VBA for Cut, move, paste, delete rows
Replies
6
Views
437
RForster6
R
S
  • Solved
Drop down rows or tables possible?
Replies
3
Views
288
myall_blues
myall_blues
velohead
  • Solved
VBA - autofilter Table for non-zero items
Replies
3
Views
27
bebo021999
B
M
  • Question Question
Populate a table of items based on 1 cell selection
Replies
1
Views
253
Edgar_
Edgar_
K
  • Question Question
Rolled up color coding
Replies
3
Views
321
felixstraube
F

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