Making Find ignore parts of a sheet?

[Maury Markowitz]

I have a spreadsheet that is initially filled with a big chunk of data
from our SQL db, pasted into the far right and then hidden. I have
formulas in columns on the left end of the sheet to refer to data in
those cells and convert them into user-readable values. Some of the
input columns contain text.

The problem is that when you use Find to look up some of these text
values, Excel gets hits in these hidden columns. When this happens,
the sheet is scrolled way over to the right, and the cursor placed
inside an invisible field. Clearly this is sub-optimal.

Is there any way to tell Excel to ignore hidden fields, or
alternately, ignore a particular range? I'd need to do this globally
in the workbook, I cannot pass this burden onto the user. Solutions
using the Format box in Find, for instance, are a non-starter.

Maury

 

[meh2030]

I have a spreadsheet that is initially filled with a big chunk of data
from our SQL db, pasted into the far right and then hidden. I have
formulas in columns on the left end of the sheet to refer to data in
those cells and convert them into user-readable values. Some of the
input columns contain text.

The problem is that when you use Find to look up some of these text
values, Excel gets hits in these hidden columns. When this happens,
the sheet is scrolled way over to the right, and the cursor placed
inside an invisible field. Clearly this is sub-optimal.

Is there any way to tell Excel to ignore hidden fields, or
alternately, ignore a particular range? I'd need to do this globally
in the workbook, I cannot pass this burden onto the user. Solutions
using the Format box in Find, for instance, are a non-starter.

Maury

Maury,

You can alter this code to fit your needs, but essentially what you
want to use is .SpecialCells(xlCellTypeVisible). Below you will
notice that I'm using a generic .UsedRange to get any cell that could
potentially have data in it and then the .SpecialCells
(xlCellTypeVisible) to restrict the UsedRange to only visible cells.
These are nested in Intersect to return only the visible cells in the
UsedRange.

I hope this helps.

Matt

Sub FindInVisibleCells()
Dim rngFind As Range
Dim rngUsed As Range
Dim strFind As String

Set rngUsed = ActiveSheet.UsedRange
Set rngFind = Intersect(rngUsed, rngUsed.SpecialCells
(xlCellTypeVisible))

strFind = rngFind.Find(What:="matt").Address

End Sub

 

[Jim Cone]

Put the db data on a separate sheet?
--
Jim Cone
Portland, Oregon USA



"Maury Markowitz"
<[email protected]>
wrote in message
I have a spreadsheet that is initially filled with a big chunk of data
from our SQL db, pasted into the far right and then hidden. I have
formulas in columns on the left end of the sheet to refer to data in
those cells and convert them into user-readable values. Some of the
input columns contain text.

The problem is that when you use Find to look up some of these text
values, Excel gets hits in these hidden columns. When this happens,
the sheet is scrolled way over to the right, and the cursor placed
inside an invisible field. Clearly this is sub-optimal.

Is there any way to tell Excel to ignore hidden fields, or
alternately, ignore a particular range? I'd need to do this globally
in the workbook, I cannot pass this burden onto the user. Solutions
using the Format box in Find, for instance, are a non-starter.
Maury

 

[Maury Markowitz]

Ok, it turns out that if you simply select "Values" in the "search in"
popup instead of "Formulas" it ignores hidden fields. I'm sure someone
at MS thought this made sense.

So now the question is whether or not there is a switch somewhere that
I can use to select this as the default operation when the sheet is
open.

Maury

 

[meh2030]

Ok, it turns out that if you simply select "Values" in the "search in"
popup instead of "Formulas" it ignores hidden fields. I'm sure someone
at MS thought this made sense.

So now the question is whether or not there is a switch somewhere that
I can use to select this as the default operation when the sheet is
open.

Maury

You may want to investigate the Find Method in the VBE help. Here is
a cut and paste from the help. "The settings for LookIn, LookAt,
SearchOrder, and MatchByte are saved each time you use this method. If
you don’t specify values for these arguments the next time you call
the method, the saved values are used. Setting these arguments changes
the settings in the Find dialog box, and changing the settings in the
Find dialog box changes the saved values that are used if you omit the
arguments. To avoid problems, set these arguments explicitly each time
you use this method."

I hope this helps in some way.

Best,

Matt Herbert