I must confess to having lost my way halfway through your discussion. But
just looking at the problem line of code:

..Resize .HeaderRowRange.CurrentRegion

I would think you'd need to do something like this because the Resize method
is looking for a number:

..Resize .HeaderRowRange.CurrentRegion.Rows.Count

"TheGlimmerMan" wrote in
message ...


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?