Get what row value. 1

[Turpis]

I want to have the row value returned to me when I find a certain value in it. Like if I was searching the document with VBA for certain cells containing 0's and if they do then I want to hide that row. All the code for that other stuff is super simple, but how do you return that row value to pull into the hide command?
Charles
Walden's Machine, Inc.
Quality Assurance/Office Developer

 

[Zathras]

Strictly speaking, you don't really need to know what row number you are on to hide it, assuming you are using the .Find method. But if you still want the row, it is a property of the range, i.e. MyRange.Row (If MyRange spans multiple rows, the first one is given as the .Row value.)

Perhaps the following code will illustrate:

Option Explicit

Sub TestFAH()
Dim bHidden As Boolean

  bHidden = FindAndHide(0)
  While bHidden
    bHidden = FindAndHide(0)
  Wend
  
End Sub
Function FindAndHide(ASearchWord) As Boolean
Dim c As Range
  Set c = ActiveSheet.UsedRange.Find(What:=ASearchWord, _
            LookIn:=xlValues, LookAt:=xlWhole)
  If Not c Is Nothing Then
    MsgBox "Hiding row number " & c.Row
    c.EntireRow.Hidden = True
    FindAndHide = True
  End If
  Set c = Nothing
End Function

 

[Turpis]

Thank you very much Zathras...this will help in a couple of different projects of mine. Charles
Walden's Machine, Inc.
Quality Assurance/Office Developer