Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Find Method To Search On Hidden Rows

  1. #1
    Join Date
    4th November 2005
    Location
    Hertfordshire, UK
    Posts
    224

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,314

    Re: Find Function To Search On Hidden Rows

    What code are you using?

    Cheers
    Andy


  3. #3
    Join Date
    4th November 2005
    Location
    Hertfordshire, UK
    Posts
    224

    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.

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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,314

    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


  5. #5
    Join Date
    4th November 2005
    Location
    Hertfordshire, UK
    Posts
    224

    Re: Find Function To Search On Hidden Rows

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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    15th September 2010
    Posts
    1

    Re: Find Function To Search On Hidden Rows

    Quote 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.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 3
    Last Post: October 9th, 2006, 00:04
  2. Find Method to Search Time/Date
    By kimler in forum EXCEL HELP
    Replies: 10
    Last Post: August 28th, 2006, 11:51
  3. Replies: 3
    Last Post: December 3rd, 2005, 16:00
  4. Find method. Partial search in date variables
    By Alex Feature in forum EXCEL HELP
    Replies: 2
    Last Post: July 29th, 2005, 17:18
  5. Find method cannot search
    By emwanri in forum EXCEL HELP
    Replies: 2
    Last Post: September 15th, 2004, 21:12

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno