0

I tried to find relevant topics on the forum but none of them seems to fit exactly my issue. Quick background: I am a pretty strong excel user but quite limited when it comes to macros, which I use mostly for time saving activities like handling large data and copy/pasting formulas into values to save space.

I also use this opportunity to thank all of you anonymous users who have helped me a lot without knowing it ;)

In this case my macro has the following purpose:

File set-up:

  • One tab with the raw data extracted from my system (call it "SheetRAW")
  • One tab to copy/paste/clean the raw data and add additional info from master file (call it "SheetDB")

Macro steps:

  1. Copy formula in SheetDB row 1 (linked to SheetRAW) and drag down to as many rows as needed
  2. Calculate current sheet only
  3. Copy paste everything into values starting row 2 downwards (do not erase row 1 with formulas)

I have a macro that works perfectly fine except when the number of rows in SheetRAW is too large (say more than 300k or 400k rows). I would like to know if there is any way to modify the code to avoid that issue?

The workaround I might need to use otherwise is to "fragment" my current macro to only calculate the first 100k or 200k rows, then repeat for the rest.

Note that I do deactivate the ScreenUpdating at the beginning and that my excel is always set to manual calculation.

Application.ScreenUpdating = False

ActiveSheet.Outline.ShowLevels RowLevels:=8, ColumnLevels:=8

    ActiveSheet.Calculate

    Dim iRow As Long
    iRow = Application.Max(Range("T1")) - 1

    'Delete previous data
    Range("A9").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).ClearContents

    'Update DB (all rows) for columns A-G
    Range("A7:G7").Select
    Selection.Copy

    Range("A9:G9").Select
    Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(iRow, 0)).Select
    ActiveSheet.Paste
    ActiveSheet.Calculate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'Update DB (all rows) for columns H-T
    Range("H7:T7").Select
    Selection.Copy

    Range("H9:T9").Select
    Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(iRow, 0)).Select
    ActiveSheet.Paste
    ActiveSheet.Calculate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'Update DB (all rows) for columns U-AH
    Range("U7:AH7").Select
    Selection.Copy

    Range("U9:AH9").Select
    Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(iRow, 0)).Select
    ActiveSheet.Paste
    ActiveSheet.Calculate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    ActiveWindow.ScrollRow = 1
    Range("A1").Select

ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1

Application.ScreenUpdating = True
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
nico0503
  • 1
  • 2
    First of all please read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and apply this technique to your code to make it much faster and less complicated. Also use this technique to avoid `ActiveSheet`. • If this doesn't solve your issue please [edit] and update the code to what you have changed it after using the link above. – Pᴇʜ Oct 21 '19 at 08:19
  • Ok thanks I will have a look at this thread and try to make any changes that might help. Unsure if I have time to do it today, otherwise I will feedback on the result tomorrow latest. Thanks a lot again for taking the time to help on such requests! – nico0503 Oct 21 '19 at 09:26
  • Ok so I tried different methods using this advice and other forums I was able to find... I still have the issue with my macro freezing at some point. As a general advice, if I need to update a database (with mainly lookup and sumif) of around 400,000 rows, is it better to do it row by row using a loop? or to do it all at once? Or will it freeze no matter what because too many rows? – nico0503 Oct 29 '19 at 04:48
  • If you applied the technique I suggested in my first comment, please [edit] and update the code in your question above to the version you have now (that is not using `.Select`) – Pᴇʜ Oct 29 '19 at 06:32
  • Yes I removed all .select to replace with ranges but still the volume of cells to calculate (34 col * 450k rows = around 15m) is too large. I think I found a workaround: I followed your tips but I split my macro into 4 parts (columns wise) so that only the same type of formula is calculated at one time. It also helps that the matching criteria now becomes a value and not another dynamic reference. Now my macro works fine and pretty fast for as many rows as needed. – nico0503 Oct 29 '19 at 07:50
  • Thanks again for the help and for taking the time to reply my query! Still trying to build my VBA skills and this type of forum is really worth a lot... – nico0503 Oct 29 '19 at 07:51

0 Answers0