Complie Error Help
 

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

Complie Error Help
 

I think you want the INSTR function.

You could probably also use

R.Row to get the row number.

Robin Hammond
www.enhanceddatasystems.com

"JMay" wrote in message
news:xyZId.34843$jn.14460@lakeread06...
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

Complie Error Help
 

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.


On Sun, 23 Jan 2005 22:02:40 -0500, "JMay" wrote:

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

Complie Error Help
 

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.

--
Regards,
Tom Ogilvy

"JMay" wrote in message
news:xyZId.34843$jn.14460@lakeread06...
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

Complie Error Help
 

Thanks Tom, as usual


"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"JMay" wrote in message
news:xyZId.34843$jn.14460@lakeread06...
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