+ Reply to Thread
Results 1 to 3 of 3

VBA Code causes error: “There isn't enough memory to complete this action.”

  1. #1
    Registered User
    Join Date
    07-17-2019
    Location
    United States
    MS-Off Ver
    Office 2016
    Posts
    5

    VBA Code causes error: “There isn't enough memory to complete this action.”

    have a consolidator tool that consolidates data from different worksheets. It can handle up to 1 million rows. However, when I click the button to check duplicates, there's an error that says "There isn't enough memory to do this action." I noticed that this error only happens when this macro runs. Please excuse the bad practice code as I am new to programming and this is what currently works right now. This works for less than 100 rows but when it starts to reach 100+, it ends up freezing and racks up memory. Is there anyway I can clean this code properly while still maintaining the functionality? T

    This is how it works:

    | Employee ID | Status |

    E100 Deactivated

    E100 Activated


    Turns into:

    | Employee ID | Status | Status |

    E100 Deactivated Activated


    Please Login or Register  to view this content.
    Last edited by dubumochi; 07-18-2019 at 02:56 AM.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2411
    Posts
    26,675

    Re: VBA Code causes error: “There isn't enough memory to complete this action.”

    I am not sure why it would be sucking up memory, but I see a couple of things that could cause long execution times.

    Are you using 32-bit or 64-bit Office?

    Could you provide an Excel file with a couple of dozen lines of sample data (not private data) so we can analyze the code under execution?

    Your code seems reasonably straightforward although there are a couple of things I would do differently.
    Please Login or Register  to view this content.
    There is no reason to put an arbitrary number in there. I would use
    Please Login or Register  to view this content.
    It's not clear what you are trying to do here. Your comment doesn't match the code. The code iterates from columns A through ALL. Iterating
    Please Login or Register  to view this content.
    Might be better to iterate through the columns that are actually used.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    07-17-2019
    Location
    United States
    MS-Off Ver
    Office 2016
    Posts
    5

    Re: VBA Code causes error: “There isn't enough memory to complete this action.”

    Quote Originally Posted by 6StringJazzer View Post
    Are you using 32-bit or 64-bit Office?
    I'm using 32-bit Office (sadly).

    Quote Originally Posted by 6StringJazzer View Post
    Could you provide an Excel file with a couple of dozen lines of sample data (not private data) so we can analyze the code under execution?
    Hi, I have attached an excel file here ufile.io/suo2gaxe. Sorry if it's not in url form, the attachment button doesn't work for some reason. Please do change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    for testing.


    Quote Originally Posted by 6StringJazzer View Post
    There is no reason to put an arbitrary number in there. I would use
    Please Login or Register  to view this content.
    I decided to put .Rows in replacement for it but it gives out a "mismatch" error.

    Quote Originally Posted by 6StringJazzer View Post
    It's not clear what you are trying to do here. Your comment doesn't match the code. The code iterates from columns A through ALL. Iterating
    Please Login or Register  to view this content.
    Might be better to iterate through the columns that are actually used.
    The original code was "For i = 1 To 16" (which I found on another side), he has 16 columns so I assume I just have to change that part to how many columns I can predict. Can I use UsedRange here? I'm not quite well versed in VBA so I tried to avoid Ranges.
    Last edited by dubumochi; 07-18-2019 at 07:38 AM.

+ 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. [SOLVED] Add password in a button code to complete the action
    By Immortal2014 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 01-26-2016, 03:06 AM
  2. Excel 2013 "not enough memory to complete action"
    By ccruse in forum Excel General
    Replies: 4
    Last Post: 12-16-2015, 11:29 AM
  3. how to solve error as "excel is waiting for another app to complete an ole action"
    By bhuvana86 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-29-2015, 12:38 PM
  4. There isn't enough memory to complete this action.
    By matrix2280 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-18-2015, 12:51 PM
  5. There isn"t enough memory to complete this action
    By Tyso in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-25-2015, 01:31 AM
  6. Error - 'Microsoft Excel is waiting for another application to complete an OLE action'
    By chubboffshore in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-25-2013, 05:54 AM
  7. Error: Excel waiting for another application to complete an OLE action.
    By dasonras in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-10-2013, 04:24 PM

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