Convert all Tables to Range

[mfrost]

Hi all,

I've currently got some data linked in from a database in several different sheets. They seem to be held is some special Excel Tables which, if i right click anywhere in them it gives me the option of Table -> Convert to Range.

I also currently have a macro that attempts to remove all formulas and data sources, which works. But it also doesn't allow what were the data tables to be filtered unless i convert them to a range. (macros and VBA are very new to me so it's probably really badly coded), below is what my macro is doing so far.

Please Login or Register  to view this content.

I have done some searching but can't find a way to hunt down any excel "tables" and convert them to range in the above macro. Is this possibe?

Thanks a lot for any help!!

Mathew

[romperstomper]

Something like:

Please Login or Register  to view this content.

[mfrost]

Something like:

Please Login or Register  to view this content.

Well that worked a treat, thanks ever so much. Super fast, and exactly what I was after! Kudos to you . Strangely i'm sure i tried almost exactly the same code myself earlier but failed miserably. Cheers!

[romperstomper]

Glad to help.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[lesoies]

Hey I am having the same problem as this user. I know the thread is old, but I can't find any info online to help me!

How do I use this piece of code you provided?

[arlu1201]

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button