+ Reply to Thread
Results 1 to 5 of 5

Speeding up a searching vba code

  1. #1
    Registered User
    Join Date
    11-30-2012
    Location
    alberta, canada
    MS-Off Ver
    Excel 2010
    Posts
    39

    Speeding up a searching vba code

    Good Morning Everyone,

    I was asked to create a code that pulls information from a table and adds everything that meets the qualification (or atleast shares a string with the search).The 2 Text Boxes that they can put information into are labeled "tb1" and "tb2" and it goes through the collection of information and copies the rows that match and values in the textboxes and pastes them and only them for review.

    I was wonding if there is a way that is more efficient then I have done it so that when the thousands of items are added it will not take FOREVER to load.
    Here is the Code:

    Please Login or Register  to view this content.
    Have a great day everyone.

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Speeding up a searching vba code

    Hi, Sleepyshy,

    either the use of the Autofilter or the Advanced Filter should speed the macro up as only the visible cells must be copied or with Advanced Filter an output could be made directly to the output sheet.

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  3. #3
    Registered User
    Join Date
    11-30-2012
    Location
    alberta, canada
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Speeding up a searching vba code

    Hello again,

    So the problem has become:

    The people that I am making the workbook for want it to have sharing enabled which means that I can not use a table (Which I ended up changing over to). I have tried using my new code which is infinitly fast with the added feature of disabling sharing then re-enabling it however this didn't work since when they saved it would turn the sharing off. Maybe I could have sharing turned on when the worksheet is turned on via a macro... Any ideas?

    Oh Right, I also took the table out and had the macro create a table with after it disabled sharing then it deleted the table and re enables sharing Iwhich doesn't save as if it had sharing.

    Any info would be super helpful,
    Sleepyshy

  4. #4
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Speeding up a searching vba code

    Hi, Sleepyshy,

    had you mentioned a shared workbook I would just have quit on reading further on the topic (the word shared is like the Stop command in VBA to me). I usually recommend relying on a database for the purpose.

    I hope youŽll find a solution for your problem but am sorry not to be able to deliver any more information.

    Ciao,
    Holger

  5. #5
    Registered User
    Join Date
    11-30-2012
    Location
    alberta, canada
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Speeding up a searching vba code

    Hey, I know it has been a LONG while since this topic was discussed anywhere but I figured I would pass on what my solution was:

    Attached Document Password: Fluttershy This password will open anything that is locked.

    I ended up turning the document itself into a SQL database. From there it would automatically save whenever a new item was entered. Anytime a change is made it saves. The odds that 2 people save at exactly the same moment is extremely unlikely and if this happens staff have been trained to take the other persons change and re-enter theirs.

    This was the only possible way I was able to find to solve this issue. If you have questions about it I will be happy to answer them to the best of my ability!
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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