• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Remove Formatting from a Table

You are here: Home / Excel VBA / Remove Formatting from a Table
remove formatting from a table
September 19, 2018 by Philip Treacy

Let's say you have a range that you want to convert to a table

Something like this that has fills, font colors, font weight, borders and number formats.



Range with formatting

If you convert this to a table this formatting remains.

Table with formatting

To remove this formatting and preserve the table formatting, we can use some VBA code.

Download the Example Workbook

All of the VBA code I use in this post is in the example workbook. Download it and use it yourself.

Enter your email address below to download the workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the workbook. Note: This is a .xlsm file. Please ensure your browser doesn't change the file extension on download.

VBA to remove formatting from table

The way this works is to apply a modified Normal style to the table to clear other formats, and then reapply the table style.

I say a 'modified' Normal style because if we just apply the Normal style it will reset any number formatting.

So I'm creating a new style called MyNormal which will by default be the same as the Normal style. But I am clearing the .IncludeNumber property for the style.

This is the same as creating the new style by hand and unchecking the Number box.

No number format in new style

With the new MyNormal style created, the code applies that to the table, then reapplies the table style that it saved earlier in the TabStyle variable.

Table with formatting removed

How To Use the Code

Select any cell in the table then run the VBA.

The code checks that the active cell is in a table, if it isn't, the code will do nothing and end.

If you want to, you can have the code display a message instead, to say the active cell isn't part of a table. I'll leave that up to you.

remove formatting from a table

More Excel VBA Posts

Display All Matches from Search in Userform ListBox

Display All Matches from Search in Userform ListBox

Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts

Animating Excel Charts

Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms

Dynamic Data Validation Lists in Userforms

Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables

Show Report Filter Pages for Power Pivot PivotTables

PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel

Charting Real Time Data in Excel

Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list

Select Multiple Items from Drop Down (Data Validation) List

Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms

Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms

Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform

Searching for Data With a User Form

Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba

Checking Values in Range Objects With VBA

Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.


Category: Excel VBA
Previous Post:listing and deleting custom stylesListing and Deleting Custom Styles
Next Post:Excel Threaded CommentsExcel Threaded Comment

Reader Interactions

Comments

  1. Cari

    May 15, 2020 at 12:30 am

    How would you put this code if it’s looping through multiple worksheets with Tables (same format)? On the second table, I’m receiving an error: on the .Range.Style = “MyNormal” -> Run Time Error: ‘450’ – Wrong Number of Arguments or Invalid Property Assignment.

    Reply
    • Philip Treacy

      May 15, 2020 at 10:51 am

      Hi Carl,

      You should just need to put in a loop to work through every workbook and table. As I didn’t write this in myself I’m not sure how you have implemented it.

      Can you please start a topic on the forum and attach your workbook so I can check it.

      Regards

      Phil

      Reply
  2. Peter Day

    September 20, 2018 at 8:33 am

    How does your macro differ from the “Apply and Clear Formatting” option available on the ribbon under Table Tools / Design / Table Styles?

    Reply
    • Philip Treacy

      September 20, 2018 at 11:16 am

      Hi Peter,

      I don’t see an ‘Apply and Clear Formatting’ option. In O365 I can see ‘Clear’ under Design->Table Styles but that just clears the table style.

      You can go to Home->Clear-Clear Formats and that clears all formatting including number formatting.

      My macro clears formatting but preserves the number formating.

      Regards

      Phil

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

launch excel macros course excel vba course

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.