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
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
