Hide / unhide rows based on value

Dubz149

New Member
Joined
Sep 18, 2014
Messages
1
Hi guys,

This is my first time actually posting here so here goes:

The question is simple enough (I hope); I have a master spreadsheet that controls two slave spreadsheets. Layout-wise they are more or less identical, the columns are slightly different but the rows are exactly the same. The user fills in the master document, which updates the other two, which are the ones we ultimately issue out. The nature of the document, however, means that not all rows will be filled in on the master document, leaving corresponding blank rows in the slave documents. I’m not fussed about there being blank rows on the master, as it never leaves the computer, but the other two are more critical.

My question, therefore, is, how can I write a macro to automatically hide rows that contain a blank value in a particular cell, and have them unhide if they become filled?

In my particular case, the column which is being updated is column B. The first 7 rows are title etc. so don’t need to be included within the process. The code I have been trying to use so far is:

Private Sub Worksheet_Calculate()
Dim LastRow As Long, c As Range
Application.EnableEvents = False
LastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
On Error Resume Next
For Each c In Range("B8:B" & LastRow)
If c.Value = "" Then
c.EntireRow.Hidden = True
ElseIf c.Value <> "" Then
c.EntireRow.Hidden = False
End If
Next
On Error GoTo 0
Application.EnableEvents = True
End Sub

My intention was to input this code into both slave spreadsheets, but so far am only getting row 8 to hide and it seems to not matter if B8 has a value in or not!

I have seen this question posted several times online and normally I manage to fudge the responses to suit my particular case, but this time I’m having no luck. Please let me know what I need to do.

Cheers,
Dubz
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

OTOTO

Board Regular
Joined
Dec 23, 2013
Messages
209
any luck yet with this? I could use this functionality as well!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,302
Messages
5,836,504
Members
430,436
Latest member
fefenouil

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
Top