+ Reply to Thread
Results 1 to 11 of 11

Macro optimization needed

  1. 05-03-2017, 01:58 AM #1
    Antiparras
    Antiparras is offline
    Registered User
    Join Date
    05-03-2017
    Location
    Norway
    MS-Off Ver
    365
    Posts
    3

    Macro optimization needed

    Hello!

    Despite I follow all of you for a while now, this is my first post here. I'm asking for advice regarding a macro I'm running against a single column (B) in 'raw' sheet with a lot of data (most of it unimportant) to extract from a server log the lines I want to look at and move them (without row gaps) to 'process' sheet.

    The macro is effective but it's dramatically slow because we are talking about 400K rows. If any of you have any suggestion to speed it up it will be highly appreciated. Here's the code I'm using:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  2. 05-03-2017, 02:11 AM #2
    Winon
    Winon is offline
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Macro optimization needed

    Welcome to the Forum Antiparras,

    You could try the below, to see if that helps.

    Please Login or Register  to view this content.
    Regards.
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .
    Reply With QuoteRegister To Reply
  3. 05-03-2017, 02:30 AM #3
    Keebellah
    Keebellah is offline
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,905

    Re: Macro optimization needed

    I would consider a find and findnext for each item in the array instead of looping though every row
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you
    Reply With QuoteRegister To Reply
  4. 05-03-2017, 02:52 AM #4
    Antiparras
    Antiparras is offline
    Registered User
    Join Date
    05-03-2017
    Location
    Norway
    MS-Off Ver
    365
    Posts
    3

    Re: Macro optimization needed

    Hi Keebellah,

    I did try something similar with some nested if's instead of an array and the result wasn't good at all, a lot of missed rows. Could you detail how you'll use FIND or FINDNEXT in the array? I'm a bit lost there.

    Thanks!
    Reply With QuoteRegister To Reply
  5. 05-03-2017, 02:54 AM #5
    Antiparras
    Antiparras is offline
    Registered User
    Join Date
    05-03-2017
    Location
    Norway
    MS-Off Ver
    365
    Posts
    3

    Re: Macro optimization needed

    Thanks Winon,

    I'm trying your approach right now. So far is taking some time too and Excel becomes unresponsive in the meanwhile. Let's see how it goes!
    Reply With QuoteRegister To Reply
  6. 05-03-2017, 03:04 AM #6
    Keebellah
    Keebellah is offline
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,905

    Re: Macro optimization needed

    I'll work it out based upon the code you pasted in the post.
    That Excel becomes 'unresponsive' is an Excel issue but it is still working in the background.
    I was running into issues with 'Not responding' in the title bar after I started using Excel 2010 until I found out that it's a 'feature' and Excel is still just busy processing, all you need is patience
    Reply With QuoteRegister To Reply
  7. 05-03-2017, 03:18 AM #7
    bakerman2
    bakerman2 is offline
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,908

    Re: Macro optimization needed

    Best to post example file if possible with a before and after sheet so we have something to work with.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
    Reply With QuoteRegister To Reply
  8. 05-03-2017, 03:18 AM #8
    jindon
    jindon is offline
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,525

    Re: Macro optimization needed

    Add a header and use AdvancedFilter, it will the fastest.
    Reply With QuoteRegister To Reply
  9. 05-03-2017, 03:27 AM #9
    Keebellah
    Keebellah is offline
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,905

    Re: Macro optimization needed

    Question(s) and remarks based on first impression:
    Remarks:
    The code 
    Please Login or Register  to view this content.
    is a very slow process

    this is faster 
    Please Login or Register  to view this content.
    If you know how many columns the you could specify the range selected

    Questions:
    If I look at your code I am assuming that you check Column B if it contains the value in the array, Correct?
    Is that the only value in column B? IN: OUT: TIMESTAMP (without the : ) and DENIED: ?
    Case sentisitive?
    Last edited by Keebellah; 05-03-2017 at 03:28 AM. Reason: Type-o
    Reply With QuoteRegister To Reply
  10. 05-03-2017, 03:31 AM #10
    humdingaling
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Macro optimization needed

    maybe filter/copy would make things faster
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    Reply With QuoteRegister To Reply
  11. 05-03-2017, 04:15 AM #11
    Keebellah
    Keebellah is offline
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,905

    Re: Macro optimization needed

    I have not tested, no data but try this one

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Macro needed - Sum untill reach 1 (Optimization)
    By tatiguc in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-23-2016, 09:45 AM
  2. Simple Optimization Model - Help Needed
    By afgallday in forum Excel General
    Replies: 3
    Last Post: 04-09-2015, 02:32 AM
  3. Optimization of Replace macro
    By DKAbi in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 04-19-2013, 05:30 PM
  4. Help with Optimization Macro..
    By zealot in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-11-2013, 06:35 PM
  5. VBA Script Optimization needed
    By mattan in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-14-2011, 01:45 PM
  6. Macro Optimization
    By intelligents in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-02-2008, 05:56 AM
  7. Macro Optimization
    By RH+ in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-14-2007, 06:37 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1