On Mar 6, 9:51*am, Maury Markowitz wrote:
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