Resize a Dynamic Named Range

[securityman]

I have a sheet that I import records from an Access table in to. After the records are imported, one of the things I want to do is copy all the VOID records into another sheet. In this first sheet the data is set up as a table.

The first time I did this, I created a named range (VoidedTickets) to use with a vlookup function in the second sheet. Everything works great.

I imported more records from Access into the first sheet. I filtered the records so I can copy the VOID records again and add them to the second sheet. All this is done thru VBA. I can do this.

I can increase (or Resize) the table in the first sheet with VBA.
How can I increase the size of the named range (thru VBA) to include the added records to the range so that the vlookup function will work with the added records and not with just the old records only?
I can increase the named range manually, but I need to do it with VBA.

[TMS]

If it's a dynamic named range it shouldn't need resizing ... that's the whole point of a *dynamic* named range. How have you defined the range in Name Manager?

Regards, TMS

[securityman]

If it's a dynamic named range it shouldn't need resizing ... that's the whole point of a *dynamic* named range. How have you defined the range in Name Manager?

Regards, TMS

It is in the Name Manager.....but it's not a dynamic range, that I know of (guess I misquoted myself in the title).

[TMS]

Well, simple answer is ... make it a named range

Something like:

=$A2:INDEX($A:$A, COUNTA($A:$A)) ... it will add the sheet references.


Regards, TMS

[securityman]

Doesn't that just make it 1 column wide?

The first time I did this, I created a named range (VoidedTickets) to use with a vlookup function in the second sheet. VoidedTickets = Range("$B$2:$DK$1200")
If I add 400 rows to the sheet I want the named range (VoidedTickets) to Resize to =Range("$B$2:$DK$1600")
It doesn't do this automatically.

[TMS]

=$B2:INDEX($DK:$DK, COUNTA($B:$B)) ... it will add the sheet references.


Regards, TMS

[securityman]

Thank you....will try this.