Setting up Excel to search by columns instead of by rows, by defau

  • Thread starter Thread starter Keith Howard
  • Start date Start date
K

Keith Howard

Each time I open Excel, I have to configure the Ctrl+F search dialog box to
search by columns instead of by rows. Is there a way to do this permanently,
so that I do not have to set this up each time I load Excel? (By the way, I
would also like to search by Values instead of by Formulas.)

Thanks.

Keith Howard
 
You can't change it permanently.

Saved from a previous post:

Excel tries to help by remembering the last settings you used--except for the
first search in that session.

You can use that to your advantage.

You could make a dummy workbook and put it in your xlStart folder. Have a
macro in that workbook that does a find (and sets all the stuff the way you
like). Then closes and gets out of the way.


Option Explicit
Sub auto_open()

'change the settings the way you like
Worksheets("sheet1").Cells.Find What:="", After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False

ThisWorkbook.Close savechanges:=False

End Sub

The workbook opens, does a find (to fix your settings) and then closes to get
out of the way.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

But if you do an Edit|Find and change anything, then those changed settings will
be remembered.
 
Thanks Dave.

It's not ideal that MS does not give the user such customisability, but your
workaround solved my problem.

Regards,

Keith Howard
 
Back
Top