Excel VBA Relative Table Help

[matmortel]

Hi,

I'm trying to create a macro that will work dynamically with the number of rows of data. To try and combat this I inserted a table to house the data, so that all of the references after would be more dynamic, but I've found myself stuck with the creation of the table, as it wants to use defined values (my code is below):

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$M$88"), , xlYes).Name = _
"Table1"

Can anyone help me to make the range dynamic so it will fit the table to the number of rows the data has (number of columns will always be consistent).

Hope you can help!

Thanks, Mat

 

[Norie]

Does this work?

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1").CurrentRegion, , xlYes).Name = _
"Table1"

 

[matmortel]

Hi Norie,

This almost works, but it ends up giving me an infinite number of empty rows below the table, so when I come to mailmerge from this list I have a large amount of blank data.

 

[Roger Govier]

Hi Mat

Not sure why you are wanting to Add the table in Code.
If you have already created the table (which is automatically dynamic), then you don't need to Add it again in your code, just refer to it by name
e.g.
With Sheets("Sheet1").ListObjects("table1")

substituting your sheet name and table name

will then allow you to do anything with that Table.