Search string on reference table and hide rows NOT containing string

MathiasHH

New Member
Joined
Apr 26, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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? :)

VBA Code:
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!
 
Last edited by a moderator:

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,238
Office Version
  1. 2010
Platform
  1. Windows
Hi,​
obviously to hide rows that just needs a filter or an advanced filter …​
 
Last edited:

Forum statistics

Threads
1,141,129
Messages
5,704,450
Members
421,350
Latest member
jake9951

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