Convert through Macro an Excel Table to a Range

Hi, I suugest to do the following.
It's simple and will show you what to do.

Start recording macro #1:
select the table and convert to range
Stop the recording

Start recording macro #2:
Select the range required
Convert this to Table
Stop the recording

All you need to do is enter a check if the table exists and then run the macro #1 to set to range

The second one can only be run if the Table dose not exists.

I hope this helps to give you an idea.

BTW if peroble add data to an existing table it will automatically adapt and resieze.

You mentioned that but what I mentioned was that you need to add a test to see if the table is already present to avoid the errors.

Create a small table in a new blank workbook

Range B4 - F4 will be the header
B5 - F 6 is the date, two rows

Public Sub rng2Table()
Dim table   As ListObject
If Tbl2rng = True Then Exit Sub
Range("B4:F6").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$B$4:$F$6"), , xlYes).Name = _
    "TestTable"
Range("B1").Select
End Sub

Public Function Tbl2rng() As Boolean
Dim table   As ListObject
On Error GoTo noTable
Set table = ActiveSheet.ListObjects.Item("TestTable")
Tbl2rng = True
noTable:
Err.Clear
On Error GoTo 0
Range("B1").Select
End Function

If you place the above two macros in a blank workbook and run them:


rng2Table will create a table if does not already exists for the selected range

tbl2rng will convert the table to range if the table is present

The table name will be 'TestTable"

This is what I meant