How to convert all tables to ranges in active Worksheet?
In Excel, you may use some tables to record data, but, in some times, you want to convert the table to range for doing some edition. Generally, you only can convert one table into range once time, it will be troublesome if there are dozens of tables in a sheet needed to be converted to ranges. Here I have a VBA code can convert tables of active sheet into range at once.
Convert all tables to ranges of active sheet with VBA
Convert all tables to ranges of active sheet with VBA
1. Activate the sheet you want to convert its tables, then press Alt + F11 keys to enable Microsoft Visual Basic for Applications window.
2. Click Insert > Module to create a new module script, copy and paste below code to the blank module.
VBA: Convert all tables of active sheet to ranges
Sub ConvertTablesToRange()
'UpdatebyExtendoffice20180530
Dim xSheet As Worksheet
Dim xList As ListObject
Set xSheet = ActiveWorkbook.ActiveSheet
For Each xList In xSheet.ListObjects
xList.Unlist
Next
End Sub
3. Press F5 key to run the code, now all tables of the current sheet have been converted to ranges.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!