excel - .End(xlDown) selecting last nonblank cell incorrectly -

-

writing vba code copy dynamic range new worksheet. code supposed first define range range copied. starting in upper left hand corner of range begin, using range.end(xldown) find last entry. offset finds bottom right hand corner of range, , range set span upper left hand corner lower right hand corner. that's how supposed work, , how work verbatim sub, changes in variable names clarity.

here's goes south. range.end(xldown) indicating last non-blank cell in column very, bottom cell on worksheet (like row 40,000 something). not true, last non-blank cell 3 rows down range looking at. thus, rather getting 4x5 size range, 1 spans entire height of sheet. have tried clearing formatting of column in case lingering, no avail. code below.

    sub copy_starters_tomaster()      dim masterio worksheet, iows worksheet     set masterio = worksheets("master io worksheet")     set iows = worksheets("io worksheet")      'sets range cover vfds entered enclosure.     dim enclosurestarters range, bottomline range     set bottomline = iows.range("$z$6").end(xldown).offset(0, 3)     set enclosurestarters = iows.range("$z$6", bottomline)      'finds first blank line in master vfd table     dim myblankline range     set myblankline = masterio.range("$ab$6")      while myblankline <> vbnullstring        set myblankline = myblankline.offset(1, 0)     loop      'copies on enclosure list of vfds, pastes master @ bottom of list.     enclosurestarters.copy     myblankline.pastespecial     dim bottominenclosure range, currentstarterrange range, enclosurenumber range      'indicates enclosure each vfd copied in belongs to, formats appropriately.     set bottominenclosure = myblankline.end(xldown)     set currentstarterrange = range(myblankline, bottominenclosure).offset(0, -1)         each enclosurenumber in currentstarterrange             enclosurenumber                 .value = worksheets("math sheet").range("$a$11").value                 .borderaround _                 colorindex:=1, weight:=xlthin                 .horizontalalignment = xlcenter             end         next enclosurenumber      end sub

any advice on appreciated, endlessly frustrating. please let me know if need post photos of errors, or further code, etc.

i think answer here use xlup , generic lastrow formula such as:

set bottomline = iows.cells(rows.count, "z").end(xlup).offset(0, 3)

that gives last used cell in column z, offset 3


Comments

Post a Comment

Popular posts from this blog

yii2 - Yii 2 Running a Cron in the basic template -

-
i have seen instructions on running cron in advanced template, cant figure out how on basic template. have following controller in basic controllers folder. <?php namespace app\controllers; use yii\console\controller; /** * cron controller */ class croncontroller extends controller { public function actionindex() { echo "cron service runnning"; } public function actionmail($to) { echo "sending mail " . $to; } } i have navigated root of application , tried these comands yii cron php yii cron im getting unknown comand "cron" i know old question failed find actual answer this. if @ code yii exec file you'll notice requires /common/config/aliases.php file console/config/main.php. can naturally change these or copy on these advanced template. hope solves problem wanting same thing.
Read more

asp.net - 'System.Web.HttpContext' does not contain a definition for 'GetOwinContext' Mystery -

-
i realize question might seem trivial some, it's these types of things find myself fighting quite bit , want make sense of despite seeming losing battle in .net (for me anyway). so, if following: using system.web; ... applicationuser user = system.web.httpcontext.getowincontext().getusermanager<applicationusermanager>().findbyid(system.web.httpcontext.user.identity.getuserid()); that produces error in title , red getowincontext() , error cannot resolve symbol 'getowincontext()' however, if following (remove system.web in front of httpcontext ), works expected (or @ least no errors): using system.web; ... applicationuser user = httpcontext.getowincontext().getusermanager<applicationusermanager>().findbyid(system.web.httpcontext.user.identity.getuserid()); however, if (same line that's working using system.web commented out): //using system.web; ... applicationuser user = httpcontext.getowincontext().getusermana...
Read more

mercurial graft feature, can it copy? -

-
there new feature in mercurial 3, called 'graft' (graft local). job of moving change-set different branch. is there way "copy" change-set branch? i interested in in moving stuff qa branch production branch, still need code reside in qa. want copy. found , have tried few things, maybe it'll become clear more tinkering , reading... does have better way of 'cherry picking' change-sets move cross branch (or trunk)??? graft copies changesets, doesn't move them. hg graft (emphasis mine): this command uses mercurial's merge logic copy individual changes other branches without merging branches in history graph. known 'backporting' or 'cherry-picking' . default, graft copy user, date, , description source changesets.
Read more