VBA Run-time error when opening document

[etgreer]

I have a document that is basically a big Table of Contents, with hyperlinks to helpful sites. I have 2 macros- the first is basically a keyword search, that hides all rows that don't include the keyword that the user enters into cell H2. The second is a simple command to unhide all of the rows. Every time I open the document, I get the Run-time error 9: Subscript out of range. Can someone tell this VBA rookie how to keep this error from coming up?

Here's my code:

Sub Search()
Dim myans As Range
Dim location As Range
Dim Text As String
Application.ScreenUpdating = False
Text = Range("H2")
For Count = 3 To 250
    With ActiveSheet.Range(Cells(Count, 3), Cells(Count, 12))
        Set myans = .Find(What:=Text, LookAt:=xlPart, SearchOrder:=xlByRows)
        If myans Is Nothing Then
            Cells(Count, 1).EntireRow.Hidden = True
        Else
            Cells(Count, 1).EntireRow.Hidden = False
        End If
    End With
Next Count

End Sub

Sub UnhideAll()
ActiveSheet.Range("A1:A300").EntireRow.Hidden = False
End Sub

Thanks!

 

[Ruca13]

Hi,

I'm also a rookie in VBA, but I would suggest that when the error message appears, you press debug so that you can see the row of the code where the error originates.

Maybe it's something you misspelled.

 

[GTO]

At first blush, I do not see anything to error in the code you posted. You mention the error happens upon opening the workbook, so presumably, you have code in Workbook_Open. You may wish to post that and show us how your code at post #1 is being called.

 

[JackDanIce]

Welcome to the board. Untested, however, try:

Sub Search_v1()

Dim x       As Long
Dim rng     As Range
Dim rngHide As Range

Application.ScreenUpdating = False

    With ActiveSheet
    
        .Cells.EntireRow.Hidden = False
        
        'Last used cell in column 3
        For x = 3 To .Cells(.Rows.count, 3).End(xlUp).Row
            On Error Resume Next
                Set rng = Cells(x, 3).Resize(, 10).Find(.Range("H2").Value)
            On Error GoTo 0
            
            'Create a range object to ride rows in a single pass
            If rng Is Nothing Then
                If rngHide Is Nothing Then
                    Set rngHide = rng
                Else
                    Set rngHide = Union(rng, rngHide)
                End If
            End If
        Next x

        If Not rngHide Is Nothing Then
            rngHide.EntireRow.Hidden = True
            Set rngHide = Nothing
        End If
    
    End With
        
End Sub

Sub UnhideAll_v1()
    
    Dim x   As Long
    
    With ActiveSheet
        x = .Cells(.Rows.count, 1).End(xlUp).Row
        .Range("A1:A" & x).EntireRow.Hidden = False
    End With
    
End Sub

 

[etgreer]

Got it! I used the Debug command and it took me to Workbook_Open. I have been revising this document that I got from someone else so they must have put it there because I'm not even sure where that exists. But I cleared out the code and it works now. Thank you!

 

[GTO]

Glad you got it working