Using a Find with a Table

[brucemc777]

In setting up to find a string within a column of a table using the Find function, I would like the search to start right after the header row, which cell would be therefore referenced after the expression After:= (or in proper order). How do I properly reference a specific cell in the header row without using A1 notation? I want to do this for if I move the table or delete a row above it or column before it, I'd rather not have another nightmare on my hands...

Thank-you!!!

[Leith Ross]

Hello brucemc777,

A Table in Excel is a ListObject in VBA. You can reference the Table by it's name in the ListObjects collection. It is not necessary to know where it is on the worksheet because there a several properties that return Range objects from the Table.

ListObject Ranges

1. DataBodyRange = Cells below the header down to the row above Insertion row.
2. HeaderRow = Cells in the top row of the table.
3. InsertRowRange = The last row of the Table. This is a special row used to insert new rows into the table.
4. TotalsRowRange = The cells at the bottom of the Table used for the totals. This is only visible when the .ShowTotals property is set to True.

[MarvinP]

Hi bruce,

It sounds like you need a "Named Range" somewhere in your formulas. Instead of doing a cell ref like A1:D27 you can name that range and the name will stick with the range if/when you move it around. If the range grows or shrinks then you need Dynamic Named Ranges. Read:

http://www.bettersolutions.com/excel...G820716330.htm

[brucemc777]

Thank-you folks - I learned from both of the posts! I found the solution that seems to work is simply to set my start of search after:

Please Login or Register  to view this content.

Which I will probably either assign or set to a variable.

Where hopefully obvious the worksheet is named dbImport and the table is named tblImport...

Once again, I appreciate the information!!!