Excel VBA - Perform Operations on visible cells only -


i have database has in excess on 200,000 rows. when writing vba script had database of 20,000 rows in mind didn't care whether database filtered or not because vba script ran quickly. given realization database huge , testing vba script surprised notice how ran. without further how code looks :

set wsdb = thisworkbook.sheets("db") dim nameindex long: nameindex = application.match(name, wsdb.rows(1), 0) dim formula string    formula = "=iferror(averageifs(" + gra(nameindex) + "," + gra(dateindex) + ",r2c," + gra(cellnameindex) + ",rc1" + "),"""")"

where gra function returns address of range of column.

private function gra(byref rngindex long)    gra = "db!" + cstr(range(cells(2, rngindex), cells(rownos, rngindex)).address(1, 1, xlr1c1, 0, 0)) end function

so given filter table beforehand how can adjust code ignores hidden rows , takes account visible. of course aware simple dirty solution copy filter database , paste in new sheet affect performance i'm trying improve.

you can use following function return range of visible cells.

function visiblecells(rng range) variant dim r range dim arr() integer dim rndx long dim cndx long  if rng.areas.count > 1     visiblecells = cverr(xlerrref)     exit function end if  redim arr(1 rng.rows.count, 1 rng.columns.count)  rndx = 1 rng.rows.count     cndx = 1 rng.columns.count         set r = rng(rndx, cndx)         if (r.entirerow.hidden = true) or _                  (r.entirecolumn.hidden = true)             arr(rndx, cndx) = 0          else             arr(rndx, cndx) = 1         end if     next cndx next rndx  visiblecells = arr  end function

the above code came http://www.cpearson.com/excel/visiblecells.aspx.

normally post code write thinking.


Comments

Post a Comment

Popular posts from this blog

apache - Remove .php and add trailing slash in url using htaccess not loading css -

i have add following code htaccess file removing .php extension , add trailing slash urls. worked fine reduced page loading speed , css code not loading @ all. pages ugly. please help rewriteengine on rewritebase / ## hide .php extension snippet # externally redirect /dir/foo.php /dir/foo rewritecond %{the_request} ^[a-z]{3,}\s([^.]+)\.php [nc] rewriterule ^ %1/ [r,l] # add trailing slash rewritecond %{request_filename} !-f rewritecond %{request_uri} !/$ rewriterule . %{request_uri}/ [l,r=301] # internally forward /dir/foo /dir/foo.php rewritecond %{request_filename} !-d rewritecond %{request_filename}.php -f rewriterule ^(.*?)/?$ $1.php [l] we can rewrite urls using .htaccess files. before writing in .htaccess files need verify kind of server using. can check server adding a <?php phpinfo(); ?> in php page. ensure mod_rewrite module enabled in apache server. can identified checking phpinfo included page. common methods used url rewriting follows ...
Read more

javascript - jQuery show full size image on click -

pretty self explanatory not workking . i want display full image when user click on image... yes know there tons of plugins but i want keep simple. any idea ? css : .full { display: none; width: 500px; height: 500px; margin-left: 100px; margin-top: -300px; position: fixed; background-color: #f00; z-index: 6; } .thumbnail { width: 50px; height: 50px; } html: <img class="thumbnail" src="http://4.bp.blogspot.com/-uhbdp2esxng/txneptvi92i/aaaaaaaaaky/upfqis7zto0/s1600/rihanna-wallpaper.jpg"> jquery: $(document).ready(function() { $('.thumbnail').on('click', function() { var img = $('<img />',{src:this.src,'class':'full'}); $('.full').html(img).show(); console.log('done!'); }); }); try looking how jquery works. $(document).ready(function() { $('.thumbnail').on('click', function(...
Read more