Announcement

Collapse
No announcement yet.

Find Method To Search On Hidden Rows

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Find Method To Search On Hidden Rows

    Hi Guys, hope this should be a quick one,

    I'm using the Find function in VBA on a column of data, but the range is being set to Nothing if the data item I'm looking for happens to be in a row that is hidden at the time.

    How do I set the find to look in all rows, hidden or not?
    Can this be done without unhiding all the rows first?

    Thanks,
    Ian

  • #2
    Re: Find Function To Search On Hidden Rows

    What code are you using?

    Cheers
    Andy

    Comment


    • #3
      Re: Find Function To Search On Hidden Rows

      Hi Andy,

      it's pretty simple really...

      AcRng in dim'd as a range and AcNum is dim'd as as integer with some checks done to make sure it's a number.

      Column B on the sheet "Action Log" has numbers in it but some of the rows are hidden.
      I am finding that if the row with the number I'm looking for is hidden, AcRng is retunred as Nothing.

      Code:
      Set AcRng = Sheets("Action Log").Columns(2).Find(AcNum, lookat:=xlWhole, LookIn:=xlValues, _
      after:=Sheets("Action Log").Cells(8, 2))
      Thanks,
      Ian

      Comment


      • #4
        Re: Find Function To Search On Hidden Rows

        If the LookIn argument is xlFormula the find will work on hidden cells.

        If not you will need to unhide first. Or could you use the match function to search for a row?

        Cheers
        Andy

        Comment


        • #5
          Re: Find Function To Search On Hidden Rows

          Thanks Andy. Strange solution, but it works in this instance.

          Comment


          • #6
            Re: Find Function To Search On Hidden Rows

            Originally posted by Andy Pope View Post
            If the LookIn argument is xlFormula the find will work on hidden cells.
            Thanks for the tip, this solved my problem too - if only the VBA help thought to mention this!

            I think you mean xlFormulas though.

            Comment

            Working...
            X