+ Reply to Thread
Results 1 to 6 of 6

Speed Up Macro by Removing Select Commands

  1. #1
    Forum Contributor
    Join Date
    02-20-2007
    MS-Off Ver
    2003 & 2007
    Posts
    299

    Speed Up Macro by Removing Select Commands

    I have created a macro via the recorder, help forums, and other research. I understand that using the select commands are not needed and that they slow down the process. However, Everytime I try to get rid of these commands I get errors. The code below works perfectly as is but is kinda slow. Could someone show me how to consolidate my macro for speed and so I can better create and edit macros in the future.

    Thank you very much for your time and help.

    Please Login or Register  to view this content.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202
    This should get you started, but after this I'm not sure what you are doing

    Please Login or Register  to view this content.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Forum Contributor
    Join Date
    02-20-2007
    MS-Off Ver
    2003 & 2007
    Posts
    299
    Thank you very much for your help.

  4. #4
    Forum Contributor boylejob's Avatar
    Join Date
    02-22-2007
    Location
    Forest City, NC
    MS-Off Ver
    2003
    Posts
    562
    erock24,

    I am going to jump in on this because getting rid of the Select statements was the first thing this Forum helped me do. I had been programming for years and had always heard you need to get rid of the Select statements, but I simply could not grasp how to do it. Mudraker, from this list, solved that problem for me and I have been flying through code ever since.

    Here is a small example using come coping and pasting.

    The Macro Recorder gives you this
    Please Login or Register  to view this content.
    To get rid of the Select statement you simply assign your worksheet to a variable and use that to refer to your sheet (I'm not good with all the technical terms). The above becomes this
    Please Login or Register  to view this content.
    You could also do it like this
    Please Login or Register  to view this content.
    which ends up being less code, but what if you were copying this to Sheet2 and Sheet3 of different workbooks. It then becomes
    Please Login or Register  to view this content.
    This becomes very bulky especially if you are doing more complicated coding. If you go back to using the Set statements, you end up with
    Please Login or Register  to view this content.
    and this is definitely a lot easier to read. I realize this is a very simple explanation and things can be a little more complicated that I have just describe, but maybe this will give you a little understanding and help you get started.
    Sincerely,
    Jeff

  5. #5
    Forum Contributor
    Join Date
    02-20-2007
    MS-Off Ver
    2003 & 2007
    Posts
    299
    Thank you very much for your reply. Your examples are very helpful.
    I'm going to plug away at updating my macro with this format. Thanks again

  6. #6
    Forum Contributor boylejob's Avatar
    Join Date
    02-22-2007
    Location
    Forest City, NC
    MS-Off Ver
    2003
    Posts
    562
    Glad I could be of some help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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