Complie Error Help

[JMay]

Below Code BOMBS at MsgBox line with: Compile Error Sub or Function not
defined;
With the word Search highlighted --- Why?????

Sub Tester()
For Each r In Rows
If r.Hidden = True Then
MsgBox "Row " & Mid(r.Address, 2, Search(":", r.Address) - 2) & " is
hidden."
End If
Next r
End Sub

TIA

 

[Robin Hammond]

I think you want the INSTR function.

You could probably also use

R.Row to get the row number.

Robin Hammond
www.enhanceddatasystems.com

 

[Myrna Larson]

If you want to use the worksheet function SEARCH, the syntax is

Application.Search

or

Application.WorksheetFunction.Search

VBA has the Instr function that may be useful to you.

 

[Tom Ogilvy]

I don't think you want to loop over 65536 rows.

Sub Tester()
For Each r In ActiveSheet.UsedRange.rows
If r.Entirerow.Hidden = True Then
MsgBox "Row " & r.row & " is hidden."
End If
Next r
End Sub

As stated by others , Search is a worksheetfunction, not a VBA function.

 

[Jim May]

Thanks Tom, as usual

I don't think you want to loop over 65536 rows.

Sub Tester()
For Each r In ActiveSheet.UsedRange.rows
If r.Entirerow.Hidden = True Then
MsgBox "Row " & r.row & " is hidden."
End If
Next r
End Sub

As stated by others , Search is a worksheetfunction, not a VBA function.