excel - Vba. 300mb+ optimizing macro -
(there more code after/before code below, part want optimize loops)
sheets("leanreport").activate dim lrow long on error resume next lrow = application.worksheetfunction.match("05 2016", range("ab:ab"), 0) on error goto 0 if lrow > 0 'code end if = 2 lastrowleanreport r1 = cstr(cells(i, 5)) rg1 = cstr(cells(i, 24)) matrizrg1(i - 2) = rg1 matrizr1(i - 2) = r1 next sheets("carrier").activate = 2 lastrowcarrier rg2 = cstr(cells(i, 1)) matrizrg2(i - 2) = rg2 next j = 2 lastrowcarrier p = lrow lastrowleanreport if matrizrg2(j) = matrizrg1(p) matrizrg3(j) = cells(j, 1) matrizc1(j) = matrizr1(p) end if next p if matrizrg3(j) = "" x = 0 lrow if matrizrg2(j) = matrizrg1(x) matrizrg3(j) = cells(j, 1) matrizc1(j) = matrizr1(p) end if next x end if next j is there way optimize macro? lastrowleanreport has more 700000 rows how can change loops else?
it gives me time error 6 & 7 out of memory.
in stead of loading data matrices , operate on matrices, operate directly on cells. don't consume memory large matrices.
to achieve this, first changed code can find equivalent expressions. example, assign matrix element , later use element. usage equivalent expression got data sheet , placed matrix element.
once done, can replace matrix references in last loop cell references. here see funny: source worksheet has apparently 2 header rows skip. later in loop skip them again, skip first 2 matrix elements! don't think mean:
for j = 0 lastrowcarrier - 2 p = lrow lastrowleanreport if matrizrg2(j + 2) = matrizrg1(p) matrizrg3(j + 2) = cells(j + 2, 1) matrizc1(j + 2) = matrizr1(p) end if next p if matrizrg3(j + 2) = "" x = 0 lrow if matrizrg2(j + 2) = matrizrg1(x) matrizrg3(j + 2) = cells(j + 2, 1) matrizc1(j + 2) = matrizr1(p) end if next x end if next j in above, think expression j + 2 should j (except in cells expression). continue on premises. note p in matrizr1(p) ill-defined points beyond matrix (i leave error fix).
next introduced variables worksheets, easier address them. changed loops start 0 row count - 2. gives following equivalent subroutine:
dim sheetcarrier worksheet dim sheetreport worksheet dim lrow long set sheetreport = sheets("leanreport") sheetreport.activate lrow = application.worksheetfunction.match("05 2016", range("ab:ab"), 0) = 0 lastrowleanreport - 2 matrizrg1(i) = cstr(sheetreport.cells(i + 2, 24)) matrizr1(i) = cstr(sheetreport.cells(i + 2, 5)) next set sheetcarrier = sheets("carrier") = 0 lastrowcarrier - 2 matrizrg2(i) = cstr(sheetcarrier.cells(i + 2, 1)) next = 0 lastrowcarrier - 2 p = lrow lastrowleanreport if matrizrg2(i) = matrizrg1(p) matrizrg3(i) = sheetcarrier.cells(i + 2, 1) matrizc1(i) = matrizr1(p) end if next p if matrizrg3(i) = "" x = 0 lrow if matrizrg2(i) = matrizrg1(x) matrizrg3(i) = sheetcarrier.cells(i + 2, 1) matrizc1(i) = matrizr1(p) end if next x end if next in next step, have replace matrix references in last loop cell references earlier loops. these equivalences are:
matrizrg1(i) = cstr(sheetreport.cells(i + 2, 24)) matrizr1(i) = cstr(sheetreport.cells(i + 2, 5)) matrizrg2(i) = cstr(sheetcarrier.cells(i + 2, 1)) (i don't know output in matrizrg3 , matrizc1 going, leave in code - nice exercise you.)
the equivalent subroutine without matrices becomes:
dim sheetcarrier worksheet dim sheetreport worksheet dim lrow long set sheetcarrier = sheets("carrier") set sheetreport = sheets("leanreport") sheetreport.activate lrow = application.worksheetfunction.match("05 2016", range("ab:ab"), 0) = 0 lastrowcarrier - 2 p = lrow lastrowleanreport if cstr(sheetcarrier.cells(i + 2, 1)) = cstr(sheetreport.cells(p + 2, 5)) matrizrg3(i) = sheetcarrier.cells(i + 2, 1) matrizc1(i) = cstr(sheetreport.cells(p + 2, 5)) end if next p if matrizrg3(i) = "" x = 0 lrow if cstr(sheetcarrier.cells(i + 2, 1)) = cstr(sheetreport.cells(x + 2, 24)) matrizrg3(i) = sheetcarrier.cells(i + 2, 1) matrizc1(i) = cstr(sheetreport.cells(p + 2, 5)) ' note: 'p' undefined!! end if next x end if next if works (and check it; errors made), can go on see if can optimize bit more.
Comments
Post a Comment