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 Formula to text or vice versa in Excel
|With Kutools for Excel's Convert Formula to Text, you can quickly convert the formula to text string which means only display the formula string in the cell and not get the result. And the Convert Text to Formula can convert the text string back to formula and display the calculated result. Click for 60 days free trial!|
|Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days.|
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.
Recommended Productivity Tools
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
300 New Features for Excel, Make Excel Much Easy and Powerful:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...