none
finding values in excel in hidden cells RRS feed

  • Question

  • Question about range.Find in excel, in cells which are in either a hidden row or column.

    Say I have a worksheet with a hidden column.  And in that column I have a cell with known static content, like "abc".

    I can call find and will find the cell by searching for the target string IFF I choose to search by formulas. If instead, I choose to search by values, then the cell is not found.   If the cell is visible, of course, it finds it both ways.

    Can anyone explain why formulas vs values would matter with hidden in this way?   It is the same way when using excel interactively, like with ctrl-F, so I guess its a general excel thing, not really specifically vsto or interop.  But maybe somebody here will know..

    This is with Excel 2007 - fwiw.

    Thanks.

     

     

     

     


    roger reynolds
    • Moved by Cindy Meister MVP Wednesday, July 14, 2010 9:08 AM Excel-specific question (From:Visual Studio Tools for Office)
    Tuesday, July 13, 2010 7:31 PM

Answers

  • I have seen other posts suggesting that hack of unhiding and then rehiding all the hidden rows/cols and while I can see that it can work, i really don't want to be doing that.

    In my situation, it just so happens that I can get away with a different sort of hack, which is to do the find against the formulas first (which finds the hidden cells) and then manually compare each cell's value against my target string. 

    Maybe not appropriate in all cases, but that's what I'm going to go with.

     

    Thanks.


    roger reynolds
    • Marked as answer by roger reynolds Wednesday, July 14, 2010 11:23 PM
    Wednesday, July 14, 2010 11:22 PM

All replies

  • Hello Roger,

    Thanks for posting. I read your post carefully. Now from what you said, I am able to reproduce this issue you are seeing in my side. After searching the database, this issue is a known issue for Excel application. When we do it in UI or using Find method of which third parameter LookIn is "xlValues", it always ignores hidden cells.

    If you are finding values programmatically, I think you could search hidden columns/rows, unhide them, use Find method and hide it again. Also for this point, here is a resource which could help you: Find Text in Hidden Cells.  If you have any concern/comments for this post, just feel free to let us know.

    We are looking forward to you reply. Have a nice day!

    Best regards,
    Bessie Zhao - MSFT
    MSDN Subscriber Support in Forum
    If you have any feedback of our support, please contact msdnmg@microsoft.com.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Wednesday, July 14, 2010 8:32 AM
  • I have seen other posts suggesting that hack of unhiding and then rehiding all the hidden rows/cols and while I can see that it can work, i really don't want to be doing that.

    In my situation, it just so happens that I can get away with a different sort of hack, which is to do the find against the formulas first (which finds the hidden cells) and then manually compare each cell's value against my target string. 

    Maybe not appropriate in all cases, but that's what I'm going to go with.

     

    Thanks.


    roger reynolds
    • Marked as answer by roger reynolds Wednesday, July 14, 2010 11:23 PM
    Wednesday, July 14, 2010 11:22 PM