Rocky contacted me after reading the article How to use Excel’s find feature to highlight or delete matching values. Like many Excel users, Rocky doesn’t use this feature’s default settings, so he must change them, often. The defaults don’t seem user-friendly, and I also find myself changing them often.

If you’re in the same boat–you’re a frequent user of Excel’s Find feature–you’re not completely stuck. In this article, I’ll show you a simple macro that sets this feature’s default settings when you open the workbook. It’s not a magic bullet, but it’s helpful.

I’m using Office 365’s (desktop) Excel on a Windows 10 64-bit system. This macro will work in earlier versions, but the online version doesn’t support macros. You can download the demonstration .xlsm file and the .bas module or enter the macro code manually. Don’t try to copy and paste the code from this web page; the Visual Basic Editor (VBE) will complain about phantom characters that you can’t see.

SEE: System update policy template download (Tech Pro Research)

The defaults

Because this article is about defaults, let’s review them quickly:

What specifies the search string.

Within lets you determine where you search–the active sheet or the entire workbook. Sheet is the default. You must change this option to search the entire workbook.

Search determines the search direction (sort of)–by columns or by rows. You won’t miss data by not changing this option, but you might speed things up a bit. By Rows is the default setting.

Look in lets you limit a search–to formulas, values, or comments. Having the wrong option set can really mess things up, as I mentioned earlier. Formulas (oddly enough) is the default. Use Formulas to quickly update a reference.

Match case will find only those values that match the case used in the Find what field. Disabled is the default.

Match entire cell contents will find only those values that match only the characters entered in the Find what field. It’s a great way to find exact matches or similar matches, depending on your setting. Disabled is the default.

Format lets you search for specific formats. This is extremely handy if you don’t depend on styles; you can use Replace to update formats with a simple search task. Disabled is the default. To access options, shown in Figure A, click the Options button. These options will help you fine-tune the task:

When you use Excel’s Find feature, you’ll need to change the settings according to your task. Excel will remember your settings, which can be useful, or not. The only way to use this feature efficiently is to remember to review the defaults every time you use it.

The macro

The macro in Listing A sets the Find feature’s Look in option when you open the workbook. It happens behind the scenes. The macro relies on VBA’s Find() function, which automates the Find feature (Find & Select in the Editing group on the Home tab). When using the user interface, you reset defaults by choosing settings from a dropdown–sometimes changing them every time you use the feature. Using the FIND() function, you pass those same settings using parameters, automatically.

Listing A

Sub Auto_Open()
'Set Find feature's default LookIn option when the file opens.
Dim c As Range
Set c = Cells.Find(What:="", LookIn:=xlValues)
End Sub

To enter the macro into a workbook, press Alt+F11 to launch the VBE. From the Insert menu, choose Module. Enter the macro and return to Excel. If you’re using a ribbon version, be sure to save the workbook as a macro-enabled file (.xlsm). After saving the workbook, close and reopen it.

Opening the workbook triggers this simple macro, which sets only the What and LookIn parameters. There are many more; this function uses the following form:

range.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

where range is an expression that returns a Range object. The parameters are similar to the user interface feature’s options, but not the same:

  • First, there’s an extra one, After. This parameter specifies the (single) cell where the search begins–it corresponds to the active cell when searching manually using the dialog.
  • Second, there’s no WithIn parameter because this function searches range. In the case of our macro, range is the Cells collection.

Only the What parameter is required; all others are optional and support the Variant data type (everything). Table A lists the different constants for each parameter. The constants in bold are the defaults.

Table A

Parameter Constants
LookIn xlFormulas, xlValues, xlNotes
LookAt xlPart, xlWhole
SearchOrder xlByRows, xlByColumns
SearchDirection xlNext, xlPrevious
MatchCase True for case-sensitive; False
MatchByte True for double-byte character match; False (use only for double-byte language support)

Notice that not all of the VBA arguments are the same as the user interface. For instance, instead of Search, VBA uses SearchOrder; if you use Search, VBA will return an error.

To add more control, simply add the appropriate parameters. For example, the procedure in Listing B sets LookIn, LookAt, and SearchOrder. Add it to a workbook, save the workbook as a macro-enabled file if necessary, close it, and then reopen it.

Listing B

Sub Auto_Open()
'Set Find feature's default LookIn option when the file opens.
Dim c As Range
Set c = Cells.Find(What:="", LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByColumns)
End Sub

Then, review the Find options via the user interface, as shown in Figure A, after opening the workbook.

Figure A

The macro changes three default settings.

Not a magic bullet

For better or worse, sometimes Excel seems to ignore the macro’s settings. For instance, if you use the Replace keyboard shortcut, Ctrl+H, the settings won’t be the same as those you set using the macro. The truth is, Replace isn’t the same as Find. If you use Ctrl+F to display the Find dialog, you’ll find the options are the same as those the macro set, until you use Replace or Ctrl+H, then those settings usurp the macro’s. It’s the way the feature works–Excel saves the current options and uses them until you change them.

Rocky put the macro in a blank macro-enabled file and stored it in XLStart, so the macro is available in each new workbook. If you don’t know how to find XLStart on your system, read Quick tip: Find Excel’s XLStart folder fast. The article is old, but it still works.

Send me your question about Office

I answer readers’ questions when I can, but there’s no guarantee. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.

See also