excel - Erase empty cells in an array -


i paste array in column, problem leaves cells in column empty. how can erase cells in column?

this have:

private sub worksheet_change(byval target range)      worksheets("info").range("a1").select     dim integer     dim ilastrow long     ilastrow = activesheet.cells(activesheet.rows.count, "a").end(xlup).row     dim arrmatrix() string       redim arrmatrix(1 ilastrow, 1 1)     = 1 ilastrow     range("a2").cells(i, 1).select     if selection.offset(0, 11) = "pi emitida"     arrmatrix(i, 1) = range("a2").cells(i, 1).value     end if     next     worksheets("inicio").range("g4:g1000000").clearcontents      worksheets("inicio").range("g4").resize(ubound(arrmatrix, 1)).value =     arrmatrix()  end sub

there few problems sample code.

  1. hopefully, on info worksheet's code sheet. should not attempting .activate worksheet worksheet_change event macro.
  2. it isn't clear why need in worksheet_change event macro. as-is, run time value anywhere in worksheet added/modified/deleted. sounds overkill since 2 columns determine outcome columns , l.
  3. the redim statement can used preserve expand array can redimension last rank.

this modification not rely on selecting or activating worksheet(s) before processing them. arrmatrix array expanded needed not end blank values in array.

private sub worksheet_change(byval target range)     if not intersect(target, range("a:a, l:l")) nothing         on error goto fìn         application.enableevents = false         dim long, n long         dim arrmatrix variant         redim arrmatrix(1 1, 1 1)         = 2 cells(rows.count, 1).end(xlup).row             if cells(i, 12).value = "pi emitida"                 n = n + 1                 redim preserve arrmatrix(1 1, 1 n)                 arrmatrix(1, n) = cells(i, 1).value             end if         next         worksheets("inicio")             .range("g4:g" & rows.count).clearcontents             .range("g4").resize(ubound(arrmatrix, 2), 1) = application.transpose(arrmatrix)         end     end if  fìn:     application.enableevents = true end sub

this run when value in column or column l added/changed/deleted.

since i've been expanding , populating last rank, used application.transpose reorient data before stuffed inicio worksheet.


Comments

Post a Comment

Popular posts from this blog

java - Spring Data JPA: Why findOne(id) executing delete query internally? -

i have 1 application using @manytomany annotation link 2 different entities 1 parent entity. not sure if have implemented properly. when execute .findone() on parent entity's repository find it, @ first fetch data along executing delete queries internally delete record mapping table (a table jpa creates after adding @manytomany annotation.). following generated sql queries , code snippet: java class @entity @table(name = "inventory") public class inventory { @id long id; // many other properties @manytomany(fetch=fetchtype.lazy) private list<stream> streams; @manytomany(fetch=fetchtype.lazy) private list<subject> subjects; } there no other annotation i've put in stream , subject entity class link inventory class. when run app, create following tables: inventory stream subject inventory_stream inventory_subject (inventory_stream , inventory_subject, 2 tables created automatically, don't k...
Read more

python - Mongodb How to add addtional information when aggregating? -

i beginner , wrote line pipeline works, wanna add other information output, screen name , or number of tweets.i tried add under $group gave me syntax error everytime here pipeline: def make_pipeline(): # complete aggregation pipeline pipeline = [ { '$match': { "user.statuses_count": {"$gt":99 }, "user.time_zone": "brasilia" } }, { "$group": { "_id": "$user.id", "followers": { "$max": "$user.followers_count" } } }, { "$sort": { "followers": -1 } }, { "$limit" : 1 } ]; i using on example : { "_id" : objectid("5304e2e3cc9e684aa98bef97"), "text" : "first week of school on :p", "in_reply_to_status_id" : null, "retweet_count" : null, ...
Read more

java - Incorrect order of records in M-M relationship in hibernate -

Image
i have 4 records in bridge table in mysql database. using @manytomany in hibernate, accurate order of employees incorrect incorrect of projects. following required material figure out doing mistake. this source. @entity @table(name="project") public class projectbean { @id @generatedvalue @column(name="project_id") private int projectid; @column(name="title") private string projecttitle; @manytomany(mappedby="projects") private collection<employeebean> employees; public projectbean() { employees = new arraylist<employeebean>(); } //getters & setters } @entity @table(name="employee") public class employeebean { @id @generatedvalue @column(name="employee_id") private int employee_id; @column(name="first_name") private string ...
Read more