+ Reply to Thread
Results 1 to 3 of 3

SQL or Loop?

  1. #1
    Forum Contributor
    Join Date
    08-14-2006
    Location
    USA
    MS-Off Ver
    2019
    Posts
    686

    SQL or Loop?

    I have an Excel table with more data than is needed.

    It is not currently possible to pre-populate the table with only the data I want.
    I need to delete the unwanted rows. which are all but about 50.

    I could loop through a few thousand rows and delete the unwanted rows.

    But I can also use a SQL query to obtain the rows I want, then CopyFromRecordset into the first row of the table and then delete (via resize) the remaining rows below the query results.
    (Unfortunately, it won't retain the query results in memory and allow me to clear the table before CopyFromRecordset)

    Is there any downside to using SQL instead of looping?

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: SQL or Loop?

    Sounds like Advanced Filter would work here
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Forum Contributor
    Join Date
    08-14-2006
    Location
    USA
    MS-Off Ver
    2019
    Posts
    686

    Re: SQL or Loop?

    No. I've never used SQL in this manner and wondered it there was a drawback to it/

    FYI, I'm creating a tool that must be fully automatic.
    It's querying an XL export from Database1 (that I don't have access to to just query myself) and dumping it into a table that contains formulas and information derived from a Database2, (which I do have access to). Splitting information, allocating values, etc.
    I really need to delete the extra rows as they wreak havoc on the calculations. It would mean adding an exorbitant amount of error handling in the formulas - I'd loop and delete before I'd do that.
    The end result is creating an exported csv file for import into back into Database2.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Loop through emails to find Email today, if not found loop Date -1
    By lougs7 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-29-2020, 12:48 PM
  2. [SOLVED] VBA to loop through cell-defined range and paste table output after each loop.
    By mscott123 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-18-2019, 03:41 AM
  3. Replies: 13
    Last Post: 07-08-2018, 05:22 AM
  4. [SOLVED] Auto email loop with formatted table data breaking on second loop VBA
    By Who_else in forum Excel General
    Replies: 6
    Last Post: 03-24-2016, 06:22 AM
  5. [SOLVED] VBA to loop within a loop of multiple blocks of data (currentregion) ... Expert needed
    By Jim885 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-20-2016, 08:28 PM
  6. [SOLVED] Copy dynamically changing column and Paste using VBA Loop (Loop within Loop)
    By nixon72 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-12-2013, 12:46 PM
  7. Why did an inner loop variable start overwriting the outer loop range suddenly?
    By 111StepsAhead in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-16-2012, 03:24 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1