excel vba - Delete Rows & Maintain Input Range -



excel vba - Delete Rows & Maintain Input Range -

i wonder whether may able help me please.

for few weeks i've been trying find solution whereby users can following:

delete rows , without data, shift rows containing info aso sit down 1 under another, whilst maintaining defined 'input range'

i've set next script clears cell contents , hence doesn't alter 'input range'.

sub delrow() dim msg sheets("input").protect "handsoff", userinterfaceonly:=true application.enablecancelkey = xldisabled application.enableevents = false msg = msgbox("are sure want delete row?", vbyesno) if msg = vbno exit sub selection application.intersect(.parent.range("a:s"), .entirerow).interior.colorindex = xlnone application.intersect(.parent.range("t:ae"), .entirerow).interior.colorindex = 42 selection.specialcells(xlcelltypeconstants).clearcontents application.intersect(.parent.range("c:ae"), .entirerow).locked = true application.intersect(.parent.range("ag:ag"), .entirerow).locked = true end application.enableevents = true end sub

updated code

sub delrow() dim rangetoclear range dim msg vbmsgboxresult 'sheets("input").protect "handsoff", userinterfaceonly:=true application.enablecancelkey = xldisabled application.enableevents = false msg = msgbox("are sure want delete row?", vbyesno) if msg = vbno exit sub selection application.intersect(.parent.range("a:s"), .entirerow).interior.colorindex = xlnone application.intersect(.parent.range("t:ae"), .entirerow).interior.colorindex = 42 on error resume next set rangetoclear = selection.specialcells(xlcelltypeconstants) on error goto 0 ' or defined error handler if not rangetoclear nil rangetoclear.clearcontents else selection.sort key1:=range("b7"), order1:=xlascending, header:=xlno, _ ordercustom:=1, matchcase:=false, orientation:=xltoptobottom, _ dataoption1:=xlsortnormal end if application.intersect(.parent.range("c:ae"), .entirerow).locked = true application.intersect(.parent.range("ag:ag"), .entirerow).locked = true end application.enableevents = true end sub

the problem though, if user selects blank row receive 'error 400' message , doesn't shift rows sit down underneath each other.

as said, i've spent much time on trying find solution without success.

i grateful if @ please , offer guidance on how may accomplish this.

many , kind regards

if selection blank, line selection.specialcells(xlcelltypeconstants).clearcontents fail because there no xlcelltypeconstants. need test , clear content if there any:

edit: seek reply sorting question

i think want sort no matter what, moved sort after clearcontents. sorted usedrange though, don't think want. need define range sorted, either named range using name manager in excel, or in code.

sub delrow() dim rangetoclear range dim msg vbmsgboxresult sheets("input").protect "handsoff", userinterfaceonly:=true application.enablecancelkey = xldisabled application.enableevents = false msg = msgbox("are sure want delete row?", vbyesno) if msg = vbno exit sub selection application.intersect(.parent.range("a:s"), .entirerow).interior.colorindex = xlnone application.intersect(.parent.range("t:ae"), .entirerow).interior.colorindex = 42 on error resume next set rangetoclear = selection.specialcells(xlcelltypeconstants) on error goto 0 ' or defined error handler if not rangetoclear nil rangetoclear.clearcontents end if 'you need define range want sorted 'here i've used usedrange activesheet.usedrange.sort key1:=range("b7"), order1:=xlascending, header:=xlno, _ ordercustom:=1, matchcase:=false, orientation:=xltoptobottom, _ dataoption1:=xlsortnormal application.intersect(.parent.range("c:ae"), .entirerow).locked = true application.intersect(.parent.range("ag:ag"), .entirerow).locked = true end application.enableevents = true end sub

excel-vba excel-2003

Comments

Post a Comment

Popular posts from this blog

web services - java.lang.NoClassDefFoundError: Could not initialize class net.sf.cglib.proxy.Enhancer -

web services - java.lang.NoClassDefFoundError: Could not initialize class net.sf.cglib.proxy.Enhancer - when trying request rest service @ time of response getting exception.. feb 21, 2013 2:34:49 pm com.sun.jersey.spi.container.containerresponse mapmappablecontainerexception severe: exception contained within mappablecontainerexception not mapped response, re-throwing http container java.lang.noclassdeffounderror: not initialize class net.sf.cglib.proxy.enhancer @ org.hibernate.proxy.pojo.cglib.cgliblazyinitializer.getproxyfactory(cgliblazyinitializer.java:117) @ org.hibernate.proxy.pojo.cglib.cglibproxyfactory.postinstantiate(cglibproxyfactory.java:43) @ org.hibernate.tuple.entity.pojoentitytuplizer.buildproxyfactory(pojoentitytuplizer.java:162) @ org.hibernate.tuple.entity.abstractentitytuplizer.<init>(abstractentitytuplizer.java:135) @ org.hibernate.tuple.entity.pojoentitytuplizer.<init>(pojoentitytuplizer.java:55) @ org.hibernate.tuple.e...
Read more

Accessing MATLAB's unicode strings from C -

Accessing MATLAB's unicode strings from C - how can access underlying unicode info of matlab strings through matlab engine or mex c interfaces? here's example. let's set unicode characters in utf-8 encoded file test.txt, read as fid=fopen('test.txt','r','l','utf-8'); s=fscanf(fid, '%s') in matlab. now if first feature('defaultcharacterset', 'utf-8') , c engevalstring(ep, "s") , output text file utf-8. proves matlab stores unicode internally. if mxarraytostring(enggetvariable(ep, "s")) , unicode2native(s, 'latin-1') give me in matlab: non-latin-1 characters replaced character code 26. need getting access underlying unicode info c string in unicode format (utf-8, utf-16, etc.), , preserving non-latin-1 characters. is possible? my platform os x, matlab r2012b. addendum: documentation explicitly states "[mxarraytostring()] supports multibyte encoded characters...
Read more

javascript - mongodb won't find my schema method in nested container -

javascript - mongodb won't find my schema method in nested container - i trying access method of schema stored within mixed container. here situation : have cases model can many different things, have schema each of these things stored in "casecontent" mixed property. var caseschema = mongoose.schema({ casecontent : {}, object : {type:string, default : "null"}, collision : {type : boolean, default : false} }); the casecontent property filled model of 1 of schemas, 1 exemple : var treeschema = new mongoose.schema({ applecount : {type : number, default : 3} }); treeschema.methods.dostuff = function (data) { console.log('hey, listen'); homecoming true; }; then, want utilize method of schema original container : caseschema.methods.dostuff = function (data) { if (this.casecontent.dostuff !== undefined) { this.casecontent.dostuff(); console.log('it worked'); } else { ...
Read more