none
Strange behavior of Find command on "Find and Replace" dialog RRS feed

  • Question

  • I found a strange behavior of the Find command on Excel2010(32bit) and Win7Pro 64.
    For example,

    On a new Sheet,
    Merge D5:E5 and set string data "DE" there.
    Set string data "B" to B3.
    Then select all the sheet and set the find parameters as follows

    Find what: * 
    Within: Sheet
    Search: By Columns
    Look In: Values

    Press "Find Next", then then find command successfully find the "B" in B3.
    Then press "Find Next" again, but find command does not find the "DE in merged cell.

    But, if I select small area for example A1:F10 and do the same thing,
    The "Find Next" can find "B" first,
    and "DE" also can be found successfully by "Find Next".

    I noticed that this symptom happens only when the following conditions,
    Search: By Columns
    Full sheet select or full column select including the merged area.

    Is this Excel bug?
    Friday, November 21, 2014 1:04 PM

Answers

  • Press Alt+F11 to activate the Visual Basic Editor.

    Press Ctrl+G to activate the Immediate window.

    Enter

    ? ActiveSheet.UsedRange.Address

    and press Enter. This will give you the address of the used range of the active sheet.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, November 23, 2014 2:54 PM

All replies

  • One more good reason to avoid merging cells...

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, November 21, 2014 3:36 PM
  • Thank you for your reply.
    I tested this symptom on EXCEL2003 and 2013.
    The symptom was reproduced.
    Does this mean that Find command source code of Excel.exe is not changed at all from EXCEL2003 to 2013?
    Saturday, November 22, 2014 9:42 AM
  • I'm sure there have been some changes - Excel 2003 had 65536 rows in a worksheet, and Excel 2007 and later support 1048576 rows. But the way merged cells are handled hasn't changed, as far as I know.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, November 22, 2014 12:06 PM
  • Thank you for your reply.
    I am using this find command to detect the actual data range in a selected range.
    With the follow settings,
      Find what: * 
      Within: Sheet
      Search: By Columns
      Look In: Values

    "Find Next" command can detect the data1.
    Then "Find Prev" can detect the data10.
    So, left and right column of actual data range can be detected.
    But, as I reported, if the selected range is full sheet select or full column select and if the selected range has a merged cell, find fails.
    Is there good alternative method to detect left and right column of actual range?
    • Edited by kaki3 Sunday, November 23, 2014 12:55 PM
    Sunday, November 23, 2014 12:54 PM
  • Press Alt+F11 to activate the Visual Basic Editor.

    Press Ctrl+G to activate the Immediate window.

    Enter

    ? ActiveSheet.UsedRange.Address

    and press Enter. This will give you the address of the used range of the active sheet.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, November 23, 2014 2:54 PM