On Nov 19, 7:13*am, TheGlimmerMan
wrote:
*Hey guys.

* I have a macro which performs lookups to list the director and actor
names in a film.

* I have named ranges for the film to director name, and I have named
ranges for the film to actor(s) list. *I want to use the named ranges
instead of tables, if possible. *I think some of the tables are required
though.

* I currently get errors related to overlapping unequal sized tables of
data. *I currently use the macro to declare or create the data tables (I
think).

* So, the director index grabs the director number, and the director
names list grabs the name from that number. *This is easy.

* The actors listing, however, is a bit more difficult because it has to
compile a list first then fill a table with it, the copy that into my
main worksheet.

* I have named ranges which encompass entire columns, because the table
gets new entries placed into it all the time. I assumed that this would
solve that.

*So, I have named ranges for:

* The director index as: * * * DIndex

* The director names are as: * Directors

* The actor's listing are as: *Actors

* The table for the actors and film reference has been defined as a
table.
Next to that table, a table gets filled with the actors numbers that
match the film number.

* That table is what I use to perform lookups into to fill my "actors
list in my main worksheet.

* Here is my macro. *I get the error on the line that resizes a table

.Resize .HeaderRowRange.CurrentRegion

*Macro:

Sub FilterOnVar()
* * Dim rngData As Range
* * Dim rngCriteria As Range
* * Dim rngExtract As Range
* * Dim s As String

Application.ScreenUpdating = False
With Sheets("Acted_In")
* * Set rngData = .Range("Table1[#All]")
* * Set rngCriteria = .Range("Table3[#All]")
End With
With Sheets("Master_Pane")
* * Set rngExtract = .Range("Table4[#Headers]")
* * * * rngData.AdvancedFilter Action:=xlFilterCopy, _
* * * * CriteriaRange:=rngCriteria, _
* * * * CopyToRange:=rngExtract

With .ListObjects("Table4")
* * If .ListColumns(1).DataBodyRange.Cells(1) < "" Then
* * * * .Resize .HeaderRowRange.CurrentRegion
* * End If
End With
End With
End Sub

* I do not know why I am getting errors. *It used to work.
* I get "Runtime error # 1004" * *(invalid range).

* Is there a better way to perform these lookups, and fill a table with
the queried actor listing?

You don't want to be messing around with named ranges. A range is an
array, and an array isa much simpler thing to process. HTH