Hi Matthew,
This is interesting in that it just reaffirms my dislike for Excel's
Find() in general. Part of the reason for that is that in most
spreadsheet apps, Headers are placed in rows *below* the sheet's
headers, and which the Find() function has no way to handle what's data
and what's not. As a result I opted for using an array containing the
entire UsedRange wherein I can specify where data starts so I can
exclude the header rows from the search. The logic of this approach is
as follows...

I use Farpoint's Spread ActiveX spreadsheet control in my VB6 apps. It
has builtin sheet 'properties' for this...

DataColCnt, DataRowCnt

...which store a Long Integer value for the last col/row containing
data. The search includes all cells of the sheet regardless of how many
cols/rows the headers have because the header cols/rows are not
considered in the search. The beauty of this is that I can set how many
cols/rows the headers have and so don't have to compensate for extra
'non-data' areas. I just ask the sheet to tell me where the last data
item is located!

It's pretty common to use 'extra' non-data areas on a sheet and so I
made the following functions to compensate for Excel's lack of being
able to define neither of the number or text of col/row headers...


Public Function GetLastDataRow&(Wks As Worksheet, Optional StartRow&)
' Finds the last row of UsedRange that contains data.
' Allows for excluding any number of header rows.
'
' Args:
' Wks Object ref to the sheet being searched.
' StartRow Optional:
' A type Long that specifies where the search starts.
' If omitted then the entire range is searched.
'

Dim vData, n&, k&, lRow&

vData = Wks.UsedRange '//load the data
lRow = IIf(StartRow 0, StartRow, 1) '//get 1st row

For n = UBound(vData) To lRow Step -1
For k = LBound(vData, 2) To UBound(vData, 2)
If Len(vData(n, k)) 0 Then GetLastDataRow = n: Exit For
Next 'k
Next 'n
End Function

Public Function GetLastDataCol&(Wks As Worksheet, Optional StartCol&,
Optional StartRow&)
' Finds the last col of UsedRange that contains data.
' Allows for excluding any number of header rows/cols.
'
' Args:
' Wks Object ref to the sheet being searched.
' StartCol Optional:
' A type Long that specifies the start col of the search.
' If omitted the search starts at Col1.
' StartRow Optional:
' A type Long that specifies the start row of the search.
' If omitted the search starts at Row1.
'

Dim vData, n&, k&, lRow&, lCol&

vData = Wks.UsedRange '//load the data
lCol = IIf(StartCol 0, StartCol, 1) '//get 1st col
lRow = IIf(StartRow 0, StartRow, 1) '//get 1st row

For n = UBound(vData, 2) To lCol Step -1
For k = lRow To UBound(vData, 2)
If Len(vData(n, k)) 0 Then GetLastDataCol = n: Exit For
Next 'k
Next 'n
End Function

...which I have not used very much in Excel since most of my users are
stepping away from M$ Office. I bought the Spread OCX back when M$
introduced the Office Ribbon because of so many users resisting to move
forward in the new (and strange at the time) UI environment. I provided
the old menubar and standard/formatting toolbars to several users to
help them through the transition to the new UI, allowing them to pretty
much do everything as before v2007 from the Addins tab of the Ribbon.

The point of this drivel is that working with both OCXs (an Excel
workbook is just a glorified ActiveX grid control on steroids!) enabled
me to offset some shortcomings of both! The Spread equivalent of
Excel's Find() is these 2 methods...

SearchCol(ByVal lCol As Long, ByVal lRowStart As Long, _
ByVal lRowEnd As Long, ByVal Text As String, _
ByVal SearchFlags As SearchFlagsConstants) As Long

SearchRow(ByVal lRow As Long, ByVal lColStart As Long, _
ByVal lColEnd As Long, ByVal Text As String, _
ByVal SearchFlags As SearchFlagsConstants) As Long

...where 'Text' is the format for all values in cells regardless of cell
'type'. That means the control defines data 'type' by the 'cell type'
as follows...

Button Cells
Check Box Cells
Combo Box Cells
Currency Cells
Custom Cells
Date Cells
Edit Cells (default type)
Number Cells
Owner Drawn Cells
Percent Cells
Mask (PIC) Cells
Picture Cells
Scientific Notation Cells
Static Text (Label) Cells
Time Cells

...so I can have a choice when exporting/importing data as to whether
it's formatted (typed).

Sorry about the 'long-in-tooth'! Let me know how (or if!) either of
these functions works with your data...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion