Search string on reference table and hide rows NOT containing string

[MathiasHH]

Hi All

Im trying to make a Multi-user calender with Sheet1 called Worksheet("Kalender") for user inputs.
PS. The table is named as well.

And then i'm creating view-only sheets with referencing the table from Sheet1 on each following sheets, so each project managers can search and lookup the assigned colleagues on the given project without interfering each other when using the document at the same time. It's placed on our Sharepoint for shared use.

I'm using the code below to be able to search through the calender for tasks & projects from a cell value.
It returns all rows containing the string and hides the rest.

The code below works on Sheet 1 ("Kalender"), but I have trouble searching the referenced table on Sheet 2.

It got me thinking if it's possible to search Sheet 1 and apply the "hide rows" on Sheet2, since its the same table & range?

I'm still new to VBA.

Do you have any suggestions?

Sub SøgProjekt()
   
    Application.ScreenUpdating = False
   
    With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
   
    Dim r As Long, What As String, Where As Range, Found As Range, Vizible As Range
    Rows("6:300").EntireRow.Hidden = True
    What = "*" & ActiveSheet.Range("C1") & "*"
   
    Set Where = Range("D6:BBB300")
    'Set Where = Worksheets("Kalender").Range("D6:BBB300")
      
    For r = 0 To 299
        On Error Resume Next
        Set Found = Nothing
        Set Found = Where.Offset(r).Find(What)
        On Error GoTo 0
        If Not Found Is Nothing Then
            If Vizible Is Nothing Then Set Vizible = Found Else Set Vizible = Union(Vizible, Found)
        End If
    Next r
   

    If Not Vizible Is Nothing Then Vizible.EntireRow.Hidden = False
   
    Application.ScreenUpdating = True
   
   
Application.Calculation = xlAutomatic
   
End Sub

Thanks!

 

[Marc L]

Hi,​

​

obviously to hide rows that just needs a filter or an advanced filter …​