Hide and unhide rows in a range if cell is blank (has formulas, but no value)

LabLady11

New Member
Joined
Oct 22, 2021
Messages
26
Office Version
  1. 2016
Platform
  1. Windows
Hello,

I found a post for hiding rows that are blank (but contain a formula) but I cannot find a code that automatically unhides rows when the formula results in text populating.

This is the 'hiding row' code I am using:

Sub hide()
Dim lr As Long, e As Range
lr = Range("A" & Rows.Count).End(xlUp).Row
On Error Resume Next
For Each e In Range("A1:A" & lr).SpecialCells(xlFormulas)
If Len(e) = 0 Then Rows(e.Row).Hidden = True
Next
On Error GoTo 0
End Sub

Is there a way to automatically have the workbook recalculate rows on all worksheets to hide/ unhide when the response to the corresponding formula populates data in a row?

A little more info... I have a tab labeled 'coversheet' in which there are yes/no pulldowns. When someone chooses 'yes' on that worksheet, data automatically pulls into rows in another worksheet labeled "department 1" from a separate worksheet containing data labeled 'data input'. So what I'm looking to do is have rows hide/unhide on each 'department' sheet when answers are changed from yes/no in the 'coversheet. Please help and thanks!!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
What about using your code in Worksheet_Change event of 'coversheet' directing it to run on SheetX, where "SheetX" is the sheet where the rows are to hidden or shown.
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,313
Members
452,634
Latest member
cpostell

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