Create new table without any formatting

1

I have an issue with creating table from file that I download from server/database. It's an older .xls file. In that file there is a list with 67 columns and arbitrary number of rows (it depends on how much data there is). When I select list and create table from it, formating is applied which changes the default formating of the list. I go to Disign > Table Styles and click clear but that does not clear all formatting that is applied; new column width stays.

I need column width not to change when I create table, or any other formating that might be applied. This really mess up my files and renders them useless for later usage. I need to create table without any formatting applied. Im using Excel 2016/1808/32bit on Windows 7/64bit.

Thanks xD

IGRACH

Posted 2019-03-22T15:39:06.353

Reputation: 223

When you apply a table style, local formatting is preserved by default. Can you give an example of what you are doing? – harrymc – 2019-03-22T16:20:42.817

Just create 2x2 list with text in first 2 rows and numbers in second 2 rows. Text needs to be longer than column length(lets say 20 characters.). When you create table from that list column width will change to the length of the text. That column length stays even when you click clear in Design tab. – IGRACH – 2019-03-22T16:53:28.743

I think when you create a table from data the column width auto-resizes to the greater of the default width (64 pixels on my current computer) or the header length (plus some padding so the selection filter arrow doesn't block the header). To resize you'll probably need VBA that reads the column widths, store them in an array, then apply the table, then resize the columns to the widths in your array. – gns100 – 2019-03-22T17:11:28.700

@gns100 Any of stopping it in the first place xD. – IGRACH – 2019-03-22T17:14:38.043

suppressing the default behavior is beyond me (as evidenced by my response to workaround the default behavior). Maybe if you poked around how a table is created, you could find the part that does the column resize and override it with VBA. Then you would run that program instead of clicking on the table button... – gns100 – 2019-03-22T17:33:21.420

@IGRACH, better you share some sample data along with any Format if applied, before convert into TABLE. Also, While reject Table Format would you like to keep Filter Buttons or not ? – Rajesh S – 2019-03-23T06:50:56.677

Answers

1

This VBA (Macro) will help you to create TABLE without any Format and Filter buttons.

Sub TableWithoutStryle()
Dim Trange As Range
Dim Ws As Worksheet

Set Ws = ThisWorkbook.Sheets("Sheet1")

Set Trange = Application.InputBox(Prompt:= _
                "Please select a range,,", _
                    Title:="Create Table", Type:=8)


Ws.ListObjects.Add(xlSrcRange, Trange, , xlYes).Name = "NewTable1"
   Ws.ListObjects("NewTable1").TableStyle = ""
    Ws.ListObjects("NewTable1").ShowAutoFilterDropDown = False
End Sub

How it works:

  • Press ALT+F11 to open VB editor.
  • Copy & Paste this Code as Standard Module with the Sheet.
  • Run the Macro & Select required Data Range you want to convert into TABLE, as soon INPUT Box appears.
  • Finish with Ok
  • You Find Selected Data Range now converted into TABLE without any Format, (Check using Table Design Command).

N.B.

In above Code Sheet & Table name are editable.

Rajesh S

Posted 2019-03-22T15:39:06.353

Reputation: 6 800

When I run this; it changes column width. – IGRACH – 2019-04-12T13:42:47.437

@IGRACH,, not possible this code doesn't have any command to change the width!! Get ready with data,, RUN the Macro,, while INPUT Box appears, select the data,, finish with Ok,,You get the Table!! I've tested this code before I've posted here. – Rajesh S – 2019-04-13T05:23:24.950

Asked: 2019-03-22T15:39:06.353

Viewed: 426 times

Active: 2019-03-26T10:23:04.300