Determine range for listobject

[Asher]

How do I create a table in vba where the amount of data is unknown. The code I have creates 10000 rows just in case, but that messes with the rest of my code and well, there's got to be a better way.

Code: Select all

    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$3:$FZ$10000"), , xlYes).Name = _
        "MK6LETable"
    ActiveSheet.ListObjects("MK6LETable").TableStyle = "TableStyleMedium15"

Asher

[HansV]

Welcome to Eileen's Lounge!

If the table is contiguous, with at least one empty row and column to separate it from the rest of the data in the worksheet, you can use

Code: Select all

ActiveSheet.ListObjects.Add(xlSrcRange, Range("A3").CurrentRegion, , xlYes).Name = _
        "MK6LETable"

CurrentRegion lets Excel determine what should be included in the listobject.