excel vba - Unrelated Pivot Tables slowing loops to a crawl -

-

a change of scope project means 2 workbooks must become 1 workbook. workbook has 3 sheets: output (viewed end users,) data (used drop small sql queries onto sheet , run logic below,) , data2 has active connection db bigger queries on server via pivot tables.

this code (without data2 sheet in workbook) can loop through ~15k lines in less 5 seconds

for = sheets("data").range("a" & rows.count).end(xlup).row 1 step -1     if not (range("d" & i).value > qs) , (range("d" & i).value < qe)         range("d" & i).entirerow.delete      end if next

upon adding new sheet (which has 2 pivot tables querying sql db, several fliters, , slicer each) single loop (which 1 of 8) takes long run haven't let finish. dropping break on "end if" , iterating through holding f5 shows code working properly. nothing on data2 sheet references outside of sheet. feel reason pivot tables refreshing through each iteration of loop no reason.

things have not helped:

pivottable("table1").manualupdate = true

or

application.screenupdating = false

or

application.calculation = xlmanual

a slight modification doesn't address root problem might speed processing:

dim sht worksheet dim rngdel range  set sht = sheets("data") = 1 sht.range("a" & rows.count).end(xlup).row      if not (sht.range("d" & i).value > qs) , (sht.range("d" & i).value < qe)         if rngdel nothing             set rngdel = sht.range("d" & i).entirerow         else             set rngdel = application.union(rngdel, sht.range("d" & i).entirerow)         end if     end if next  if not rngdel nothing rngdel.delete

@acantud's comment worth addressing: unqualified range objects can cause sorts of problems sheet being read/modified not 1 want.


Comments

Post a Comment

Popular posts from this blog

c++ - OpenCV Error: Assertion failed <scn == 3 ::scn == 4> in unknown function, -

-
i read alot other solution still confused should mine... #include <opencv2/objdetect/objdetect.hpp> #include <opencv2/highgui/highgui.hpp> #include <opencv2/imgproc/imgproc.hpp> #include <iostream> #include <stdio.h> using namespace std; using namespace cv; int main(int argc, const char** argv) { //create cascade classifier object used face detection cascadeclassifier face_cascade; //use haarcascade_frontalface_alt.xml library face_cascade.load("haarcascade_frontalface_alt.xml"); //setup video capture device , link first capture device videocapture capturedevice; capturedevice.open(0); //setup image files used in capture process mat captureframe; mat grayscaleframe; //create window present results namedwindow("outputcapture", 1); //create loop capture , find faces while (true) { //capture new image frame capturedevice >> captureframe; ...
Read more

php - render data via PDO::FETCH_FUNC vs loop -

-
i've been using 2 methods render data. the first one: function name($id,$name){ return '<div id="'.$id.'">'.$name.'</div>'; } echo implode($pdo->query("select id,name user")->fetchall(pdo::fetch_func,'name')); the second one: $users = $pdo->query("select id,name user")->fetchall(pdo::fetch_obj); foreach($users $user){ echo name($user->id,$user->name); } i don't understand how pdo::fetch_func works. tried figure out. however, not well-documented. could please explain fetch mode? , also, 1 performs better? thank you. both methods wrong , have learn how use templates , how separate business logic presentation logic. $users = $pdo->query("select id,name user")->fetchall(pdo::fetch_obj); tpl::assign('users', $users); is code business logic part. then in template <?php foreach $users $row): ?> <div id="<?=$...
Read more

The canvas has been tainted by cross-origin data in chrome only -

-
uncaught securityerror: failed execute 'getimagedata' on 'canvasrenderingcontext2d': canvas has been tainted cross-origin data i getting above error in chrome. code(following) works fine in mozilla. have not corss domain issue. why above error in chrome? var wheel_canvas, wheel_context, can_w, can_h, wheel_grd; var large_radius = 160; var small_radius = 120; var wheel_canvas = document.getelementbyid('wheel_canvas'); var wheel_context = wheel_canvas.getcontext('2d'); var can_w = $(wheel_canvas).width(); var can_h = $(wheel_canvas).height(); var centerx = can_w / 2, centery = can_h / 2; var wheel_grd = null; test('#ff0000'); function test(hex) { $('#clrpicker').css({'left': 210, 'top': 210 }); inverted = hexbw(hex); $('#color_val_show').val(hex); current_color_hex_val_selected = hex; $('#color_val_show').css({'color':inverted,'background':hex}); fillgradient...
Read more