vba convert dynamic range to tabe

[already]

Hello

I want to convert with vba a growing (dynamic) range into a table.

But I can't figure it out.

thanks in advance for your help

Kind regards

Al

 

[Misca]

Try

Dim rng As Range
    
    Set rng = Range("A1").CurrentRegion 'Change to match your range
    
    ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes).Name = "MyTable"

 

[already]

thanks !! It did the job

Kind regards

Al

 

[already]

Re: vba convert dynamic range to table

Hello

When updated my table with new data my lookup function changed and the table name in my formula is changed by an error #ref!.

How can I resolve this ?

1/ I remove the old table
2/ I replace it with the new one import range convert it to table with same name and same sheet name than the old one

lookup formula change from =IFERROR(VLOOKUP(A292;mytable;3;0);"") into =IFERROR(VLOOKUP(A292;#REF!;3;0);"")

Kind regards

Al

 

[Misca]

Re: vba convert dynamic range to table

Looks like your formula doesn't find the mytable. Check the name of the table. Looks like VBA doesn't always need a reason to change the table name into something else: I changed my original code to name the tables using the sheet name and still the names were sometimes slightly different even when there were no other tables on the sheet (and that macro creates a single Table on the sheet anyway).

If you're using macros you might as skip the table and use an event macro to name your new range with the old name every time your data range expands. - Or use the good old OFFSET-function to define the named range without having to use any VBA. Both these ways make sure the name doesn't change.

 

[arkusM]

Try

Dim rng As Range
 
    Set rng = Range("A1").CurrentRegion 'Change to match your range
 
    ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes).Name = "MyTable"

I would think the code should work like:

Dim rng As Range
 
    Set rng = Range("A1").CurrentRegion 'Change to match your range
    rng.Name = "MyTable"

I use code like this without any trouble

 

[already]

thanks again misca for your help
thanks arkusm this little code did the trick waah !!!

Kind regards
Al