Trying to create a macro to remove active Queries

TedX

Board Regular
Joined
Apr 18, 2021
Messages
122
Office Version
  1. 365
Platform
  1. Windows
Hello (y)

I have this one line of code that removes one query at a time from the Queries & Connections area, I spent the afternoon trying to place it in a loop so that it would run multiple times because I normally have about 10 queries in a daily session and I don't want to sit there clicking a macro 10 times. Who knows how to do this?

VBA Code:
Sub DeleteActiveQueries()

    ActiveWorkbook.Queries(1).Delete
    
End Sub

1671192244514.png
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Hey,

Can you try this one?
VBA Code:
For Each qry In ActiveWorkbook.Queries

    qry.Delete

Next qry
 
Upvote 0
Solution
Hey,

Can you try this one?
VBA Code:
For Each qry In ActiveWorkbook.Queries

    qry.Delete

Next qry

Thanks *bferraz, I did try it and got this Variable not define error, pictured below. Any idea how to Dim it?

1671195337674.png
 
Upvote 0
Sorry about that!

You can dim it as Variant

VBA Code:
Dim qry As Variant

Got it, you little ripper, I was actually dim'ing it as your reply came through, I was trying string, then Queries< would have gotten around to variant in about 3 days 😂 :ROFLMAO: 😂 You certainly have solved this for me. I also noticed you are fairly new, so if this is your first 'Solved' for someone else, then congratulations. Thank you heaps.

The code that worked perfectly is:

VBA Code:
Sub DeleteActiveQueries()
 
Dim qry As Variant

For Each qry In ActiveWorkbook.Queries

    qry.Delete

Next qry

End Sub
 
Upvote 0
Nice! You got it right, that was actually my first 'Solved'. I just got into the forum but I hope I can help more members as well. :biggrin:

By the way, you can dim them as variant or object. It is up to you!
 
Upvote 0
Nice! You got it right, that was actually my first 'Solved'. I just got into the forum but I hope I can help more members as well. :biggrin:

By the way, you can dim them as variant or object. It is up to you!

I have an hour's work, but after that, I'm going to read up on them. Thanks again, LOL, I guess you lost your virginity to me, not sure how to take that hahahaha :ROFLMAO: 😂 :ROFLMAO:
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top