Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or
0
0
0
s2smodern

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!
doc covnvert formula to text
 
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days.

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

doc convert tables to ranges 1

3. Press F5 key to run the code, now all tables of the current sheet have been converted to ranges.


Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 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...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.