LinkBack URL
About LinkBacks
The column widths will adjust automatically every time I convert a range to a table. It will apply its table style and formatting too which I don't want.
Is it possible to convert to table without changing any formatting?
Thank you.
The column widths will adjust automatically every time I convert a range to a table. It will apply its table style and formatting too which I don't want.
Is it possible to convert to table without changing any formatting?
Thank you.
Last edited by mastertonn; 10-05-2018 at 04:09 AM.
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.)...
Please Login or Register to view this content.
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.
Ways to get help: Post clear questions, explain your overall goal, supply as much background as possible, respond to questions asked of you by those trying to help and post sample(s) files.
"I am here to help, not do it for people" -Me
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 ¯\_(ツ)_/¯
Thank you. It would be much better it convert to a table based on what cells I select.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.)...
Please Login or Register to view this content.
Convert the selection to a table (the first row is header row). Don't change any formatting. Re-apply the column widths.
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.
And what I am explaining is thats not how it works. You apply tables then format, not format then apply tables.
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.
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.
I dont think you follow. Formatting things the way you want is something you do AFTER you have done everything else, not before.
IE: add data -> create table -> format
not
add data -> format -> create table -> format
There is nothing you can do about how the table behaves, you are basically saying you want some of the built in parts of a table but not others...you dont get to make that choice, its hard coded by MS that way. What you can and should change is your workflow. The efficient way to work is to save manual formats for the end and do it a single time instead of many time fighting against other features of Excel.
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).
Please Login or Register to view this content.
This is the actual workflow:
add data -> create table -> Excel auto-applies its formats -> undo Excel formats -> format my own
You still have to undo its auto-applied formats.
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?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).
Please Login or Register to view this content.
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):
Please Login or Register to view this content.
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.
Thank you very much.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):
Please Login or Register to view this content.
To re-incorporate the column sizing, just a slight adjustment to reference the selection rather than a range:
Please Login or Register to view this content.
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!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks