locked
Excel memory management RRS feed

  • Question

  • Hi all,

    I wanted to know that how excel works when large amount of excel file is opened.
    I have a workbook with 30000 rows and 20 columns filled data.
    Using macro I read each line & then each column for some validation.

    so when this workbook is opened is all 30000*20 cells are loaded into working memory or when cell request comes then only it gets loaded into memory?

    is it good to use array to read some 200 rows first and then checking each row and each column in this array or read directly?


    thanks
    born to code
    Wednesday, August 17, 2011 9:45 AM

Answers

  • There is plenty of information about Excel and memory here, but I don't think you will find exactly what you are looking for, or even if you can I don't think it would be of much help to you -

    http://www.decisionmodels.com/index.htm

    is it good to use array to read some 200 rows first and then checking each row and each column in this array or read directly?

    Yes definitely. Reading and particularly writing directly to cells individually is slow

    Dim arr() As Variant

    With Worksheets(1)
    i = 1
    ' read
    arr = .Range(.Cells(i, 1), Cells(i + 199, 20)).Value
    ' write
    .Range(.Cells(i, 1), Cells(i + 199, 20)).Value = arr
    End With

    Experiment with different sized arrays.30k x 20 might be too much in one go.

    Peter Thornton

    • Proposed as answer by danishani Monday, November 28, 2011 9:43 PM
    • Marked as answer by danishani Wednesday, January 11, 2012 8:14 PM
    Wednesday, August 17, 2011 11:36 AM
  • Iwould try both approaches and use the Timer command to time each one. I would also press Ctrl+End in the worksheet and make sure that the last cell is row 30,000 and not lower. If for example the last row is 40,000 then the file is needlessly large and may further slow progress.

    Go to row 30,000 then Select row 40,000 with the shift key down to select all cells. Shift+Space to make the selection for entire rows. Now press Delete then Ctrl+- to remove the surplus rows. Next time you save the file size should reduce. Do same for any unused columns to the right of your data (Ctrl+Space to select entire columns).

    This might make the macro faster. Maybe Excel 2010 64 bit for even faster performance?


    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management
    • Proposed as answer by danishani Monday, November 28, 2011 9:44 PM
    • Marked as answer by danishani Wednesday, January 11, 2012 8:14 PM
    Thursday, August 18, 2011 7:22 AM

All replies

  • There is plenty of information about Excel and memory here, but I don't think you will find exactly what you are looking for, or even if you can I don't think it would be of much help to you -

    http://www.decisionmodels.com/index.htm

    is it good to use array to read some 200 rows first and then checking each row and each column in this array or read directly?

    Yes definitely. Reading and particularly writing directly to cells individually is slow

    Dim arr() As Variant

    With Worksheets(1)
    i = 1
    ' read
    arr = .Range(.Cells(i, 1), Cells(i + 199, 20)).Value
    ' write
    .Range(.Cells(i, 1), Cells(i + 199, 20)).Value = arr
    End With

    Experiment with different sized arrays.30k x 20 might be too much in one go.

    Peter Thornton

    • Proposed as answer by danishani Monday, November 28, 2011 9:43 PM
    • Marked as answer by danishani Wednesday, January 11, 2012 8:14 PM
    Wednesday, August 17, 2011 11:36 AM
  • Iwould try both approaches and use the Timer command to time each one. I would also press Ctrl+End in the worksheet and make sure that the last cell is row 30,000 and not lower. If for example the last row is 40,000 then the file is needlessly large and may further slow progress.

    Go to row 30,000 then Select row 40,000 with the shift key down to select all cells. Shift+Space to make the selection for entire rows. Now press Delete then Ctrl+- to remove the surplus rows. Next time you save the file size should reduce. Do same for any unused columns to the right of your data (Ctrl+Space to select entire columns).

    This might make the macro faster. Maybe Excel 2010 64 bit for even faster performance?


    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management
    • Proposed as answer by danishani Monday, November 28, 2011 9:44 PM
    • Marked as answer by danishani Wednesday, January 11, 2012 8:14 PM
    Thursday, August 18, 2011 7:22 AM
  • Thanks Peter and Rod for valuable information and your time, efforts.

    born to code
    Friday, October 21, 2011 5:35 PM