How to automatically hide row if Column B is blank OR contains a duplicate value as a previous cell

[steve80s]

Hi All,

I've managed to create a list that will autofill from other sources using formulas such as =[Book2]Sheet1!$O$18

I need a Macro/Code/Whatever which will automatically hide rows where Column B is empty OR where there are duplicate values in Column B (such as if 'Mr A' appeared twice).

It needs to be working constantly in the background, as it's possible the original source (Sheet1) will be updated and Column B suddenly contains data, so that line would need to un-hide on my spreadsheet.

Please see screenshot to illustrate my layout.

Thanks in advance!

 

[DanteAmor]

Let's try the following.
In the sheet where you want the rows to be hidden, put the following code in their events.
Inside the code, change the name of the sheet "Sheet2" to the name of the sheet where you want the rows to be hidden.

Private Sub Worksheet_Calculate()
  Dim c As Range, hideRow As Range
  Dim dic As Object
  Dim lr As Long
  Dim sh2 As Worksheet
  
  Set sh2 = Sheets("Sheet2")   'Fit the name of the sheet where you want rows to be hidden
  Set dic = CreateObject("Scripting.Dictionary")
  
  Application.ScreenUpdating = False
  sh2.Range("B:B").EntireRow.Hidden = False
  lr = sh2.Range("A" & Rows.Count).End(3).Row
  Set hideRow = sh2.Range("A" & lr + 1)
  For Each c In sh2.Range("B2:B" & lr)
    If dic.exists(c.Value) Or c.Value = "" Then
      Set hideRow = Union(hideRow, c)
    Else
      dic(c.Value) = Empty
    End If
  Next
  hideRow.EntireRow.Hidden = True
  Application.ScreenUpdating = True
End Sub

Note Sheet Event:
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

 

[steve80s]

Thanks for your reply @DanteAmor

However, I have followed your steps and nothing seems to happen. I wonder if this is because the contents of my cells contain formulas (e.g. ='https://nel.sharepoint.com/sites/WFROOMBOOKINGSYSTEM/SharedDocuments/General/ZZZ-Guidance/[Book2.xlsx]Sheet1'!$AJ$5). The formulas obviously display the VALUES from the appropriate source cell, but I don't know if your code is reading the formulas or the returned values?

Could this be the problem or am I doing something else wrong?

 

[steve80s]

Ignore my previous message! I got it working now.

Thanks for your reply @DanteAmor

This seems to be working quite well. However, is there now a way of adjusting the code so that it will hide any row where B contains the words "as" and "above" in any CASE or COMBINATION? eg. If the cell contains "as Above", or "Same as above"? It also still shows the first instance of a row where B is blank.

 

[DanteAmor]

However, is there now a way of adjusting the code so that it will hide any row where B contains the words "as" and "above" in any CASE or COMBINATION? eg. If the cell contains "as Above", or "Same as above"? It also still shows the first instance of a row where B is blank.

Try this:

Private Sub Worksheet_Calculate()
  Dim c As Range, hideRow As Range
  Dim dic As Object
  Dim lr As Long
  Dim sh2 As Worksheet
  
  Set sh2 = Sheets("Sheet2")
  Set dic = CreateObject("Scripting.Dictionary")
  
  Application.ScreenUpdating = False
  sh2.Range("B:B").EntireRow.Hidden = False
  lr = sh2.Range("A" & Rows.Count).End(3).Row
  Set hideRow = sh2.Range("A" & lr + 1)
  For Each c In sh2.Range("B1:B" & lr)
    If dic.exists(c.Value) Or c.Value = "" Or InStr(1, c.Value, "as above", vbTextCompare) > 0 Then
      Set hideRow = Union(hideRow, c)
    Else
      dic(c.Value) = Empty
    End If
  Next
  hideRow.EntireRow.Hidden = True
  Application.ScreenUpdating = True
End Sub