Convert range to table without changing column width / formatting

For some reason when I convert a range to a table, the column widths don't change. In any case, a macro to create a table could also gather the column widths ahead of time, then re-apply after the table is created. For example, assuming data range is A1:?? (e.g. A1:F20, A1:Z450, etc.)...

Sub makeTable()
Dim i As Long, lastcol As Long, lastrow As Long, arr

lastrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
lastcol = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
ReDim arr(1 To lastcol)

'Get column widths for all used columns (assuming data is in A:??) and put in array
For i = 1 To lastcol
    arr(i) = Sheets("Sheet1").Cells(1, i).ColumnWidth
Next i

'Create table using data in A1:??
Sheets("Sheet1").ListObjects.Add(xlSrcRange, Range(Cells(1, 1), Cells(lastrow, lastcol)), , xlYes).Name = "Table1"

'Reset column widths for each column from array values gathered earlier
For i = 1 To lastcol
    Cells(1, i).ColumnWidth = arr(i)
Next i

End Sub

Originally Posted by ¯\_(ツ)_/¯

For some reason when I convert a range to a table, the column widths don't change.

It depends on the column headings. To easily see the effect, type a heading which is longer than the column width. Convert it to a table.

Originally Posted by ¯\_(ツ)_/¯

In any case, a macro to create a table could also gather the column widths ahead of time, then re-apply after the table is created. For example, assuming data range is A1:?? (e.g. A1:F20, A1:Z450, etc.)...

Sub makeTable()
Dim i As Long, lastcol As Long, lastrow As Long, arr

lastrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
lastcol = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
ReDim arr(1 To lastcol)

'Get column widths for all used columns (assuming data is in A:??) and put in array
For i = 1 To lastcol
    arr(i) = Sheets("Sheet1").Cells(1, i).ColumnWidth
Next i

'Create table using data in A1:??
Sheets("Sheet1").ListObjects.Add(xlSrcRange, Range(Cells(1, 1), Cells(lastrow, lastcol)), , xlYes).Name = "Table1"

'Reset column widths for each column from array values gathered earlier
For i = 1 To lastcol
    Cells(1, i).ColumnWidth = arr(i)
Next i

End Sub

Thank you. It would be much better it convert to a table based on what cells I select.

Convert the selection to a table (the first row is header row). Don't change any formatting. Re-apply the column widths.

Originally Posted by Zer0Cool

This is expected behavior as far as I am aware. Tables size the columns to fit the headers, since setting a range as a table adds a drop down to each table, you can imagine it would be annoying if each column didn't resize to show the header text without being blocked by the drop down box.

A table is really just a type of formatting, which overrides much of the formatting that took place before, similar to a style. Tables resize columns to ensure the header title is visible (as in a tabular structure knowing what each column represents is important).

For this and many other reasons, manual formats are best done once the data and all other entries, calculations, etc are complete. Otherwise a person would need to reformat multiple times while creating a document wasting time and effort.

In short, setup your data, make it a table if need be, calculate what you need to, etc. and then make it look nice at the end. Function > form.

Yes it resizes to fit the headers, but I want to keep them fixed.
It will automatically apply its table style and formatting too which is undesired.
I simply want to convert to a table without changing any formatting.

Originally Posted by Zer0Cool

And what I am explaining is thats not how it works. You apply tables then format, not format then apply tables.

This is what I have to do now.

Convert to table
It changes the widths and apply its formatting
I have to undo all the changes

You say table then format. It does not help. It apply its formatting automatically. You still have to undo them first even if you format your table later.

It shouldn't be difficult to adjust my code to accommodate a 'Selection' rather than determining the used range from A1:??.

But... I can't figure out WHY you'd want to convert a data range to a table, yet not utilize Table functionality? Why not just keep it a data range and add filters, etc.?

Originally Posted by ¯\_(ツ)_/¯

It shouldn't be difficult to adjust my code to accommodate a 'Selection' rather than determining the used range from A1:??.

But... I can't figure out WHY you'd want to convert a data range to a table, yet not utilize Table functionality? Why not just keep it a data range and add filters, etc.?

I want the table functionality. I just don't want it to change my formatting (column width, cell colors etc.).

The table functionality is not just about adding filters. There are much more, like total rows, automatically fill in formulas etc.

The following code replaces my original code. It makes a simple table with no row/header shading. Obviously you can adjust the table properties in the code itself. This code adds a Total row at the bottom; if you don't want that just delete or comment out that row. Also, it creates a table called 'Table1' every time it is run. If you want to be able to change that, you may want to either add an InputBox to get a name from the user, or loop through any current tables to see if a name is already used (code will error if it tries to create another table with the same name).

Sub makeTbl()
ActiveSheet.ListObjects.Add(xlSrcRange, Range(Selection.Address), , xlYes).Name = "Table1"
With ActiveSheet.ListObjects("Table1")
    .ShowTableStyleRowStripes = False
    .ShowHeaders = True
    .TableStyle = "TableStyleLight21"
    .ShowTotals = True
End With
End Sub

Originally Posted by ¯\_(ツ)_/¯

The following code replaces my original code. It makes a simple table with no row/header shading. Obviously you can adjust the table properties in the code itself. This code adds a Total row at the bottom; if you don't want that just delete or comment out that row. Also, it creates a table called 'Table1' every time it is run. If you want to be able to change that, you may want to either add an InputBox to get a name from the user, or loop through any current tables to see if a name is already used (code will error if it tries to create another table with the same name).

Sub makeTbl()
ActiveSheet.ListObjects.Add(xlSrcRange, Range(Selection.Address), , xlYes).Name = "Table1"
With ActiveSheet.ListObjects("Table1")
    .ShowTableStyleRowStripes = False
    .ShowHeaders = True
    .TableStyle = "TableStyleLight21"
    .ShowTotals = True
End With
End Sub

Thank you. However this will still apply a table style. I don't want any table style. Excel has a table style called none. What is the code name of this table style?
I create quite many tables in the workbook.
I know very little about scripting. Is it possible not to give any name or randomly generate a name for the table, for example, table123459 where the digit number is randomly generated.

It will still auto-resize the column widths by running the macro.

P.S. For some reason, I'm unable to edit the above post.

You realize you can record a macro of yourself creating a table from data, then looking at the code it created, right?

To set the table style to None, replace: .TableStyle = "TableStyleLight21"

With: .TableStyle = ""

As for random table names, this should create a table name with a random number between 100,000 and 1,000,000 (and set the table style to None):

Sub Macro1()
Dim newRand As Long
Randomize
newRand = CLng((1000000 - 100000 + 1) * Rnd + 100000)

ActiveSheet.ListObjects.Add(xlSrcRange, Range(Selection.Address), , xlYes).Name = "Table" & newRand
With ActiveSheet.ListObjects("Table" & newRand)
    .ShowTableStyleRowStripes = False
    .ShowHeaders = True
    .TableStyle = ""
    .ShowTotals = True
End With
End Sub

Originally Posted by ¯\_(ツ)_/¯

You realize you can record a macro of yourself creating a table from data, then looking at the code it created, right?

Yes I know I can record a macro, but I don't realise I could use it as a way to find out the code of a table style.
It's brilliant. Thanks for the tip.

Originally Posted by ¯\_(ツ)_/¯

To set the table style to None, replace: .TableStyle = "TableStyleLight21"

With: .TableStyle = ""

As for random table names, this should create a table name with a random number between 100,000 and 1,000,000 (and set the table style to None):

Sub Macro1()
Dim newRand As Long
Randomize
newRand = CLng((1000000 - 100000 + 1) * Rnd + 100000)

ActiveSheet.ListObjects.Add(xlSrcRange, Range(Selection.Address), , xlYes).Name = "Table" & newRand
With ActiveSheet.ListObjects("Table" & newRand)
    .ShowTableStyleRowStripes = False
    .ShowHeaders = True
    .TableStyle = ""
    .ShowTotals = True
End With
End Sub

Thank you very much.

To re-incorporate the column sizing, just a slight adjustment to reference the selection rather than a range:

Sub Macro1()
Dim newRand As Long, i As Long, arr
Randomize
newRand = CLng((1000000 - 100000 + 1) * Rnd + 100000)

ReDim arr(1 To Selection.Columns.Count)
For i = 1 To Selection.Columns.Count
    arr(i) = Selection.Cells(1, i).ColumnWidth
Next i

ActiveSheet.ListObjects.Add(xlSrcRange, Range(Selection.Address), , xlYes).Name = "Table" & newRand
With ActiveSheet.ListObjects("Table" & newRand)
    .ShowTableStyleRowStripes = False
    .ShowHeaders = True
    .TableStyle = ""
    .ShowTotals = True
End With

For i = 1 To Selection.Columns.Count
    Selection.Cells(1, i).ColumnWidth = arr(i)
Next i
    
End Sub

Hello, thanks a lot for this solution. I had a non-table XLS that needed porting from Excel 2003 to the current Excel version. The final macro here made conversion a breeze. Excellent!