PDA

View Full Version : Solved: How can I delete all Queries in workbook?



Elmura
06-04-2008, 02:37 AM
I have an 8 sheet workbook that has had queries added over a period of time. I wish to delete them all. I have deleted the ranges where the queries have been previously used but if I attempt to create a new query using a name that has already been used, Excel won't allow it.

So these old queries are stored somewhere??? Can't find them however.

Another indication I get that they are still floating in the workbook somewhere, is that the Range name box in the toolbar lists all the queries ever created.

How do I do this?

Jan Karel Pieterse
06-04-2008, 03:28 AM
1. Download my Name Manager from
www.jkp-ads.com/officemarketplacenm-en.asp (http://www.jkp-ads.com/officemarketplacenm-en.asp)
Use it to get rid of the query range names
2. Run this code:

Sub RemoveQT()
Dim oQT As QueryTable
Dim oSh As Worksheet
For Each oSh In Worksheets
For Each oQT In oSh.QueryTables
oQT.Delete
Next
Next
End Sub

Elmura
06-04-2008, 07:56 AM
Thankyou for that great tool! I tried the code but that didn't do anything. However, after reading the instructions with your Name Manager tool, I found all the named queries and was able to quickly delete them.

Kudos to JKP!:bow: