Sent on my BlackBerry® from Vodafone -----Original Message----- From: Domain Admin <domainqu...@gmail.com> Sender: excel-macros@googlegroups.com Date: Thu, 19 Apr 2012 00:11:35 To: <excel-macros@googlegroups.com> Reply-To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ What is left behind when you clear or delete cell contents that still affects usedrange?
A. That works though I still have to see if it solves the usedrange problem B. I should have known that. I have been told so before C. I still don't get why it can not be done without the set and then use the qualified values even if that is not the best way, but the function itself fails trying to remove them. Thanks. Asa, you have given me examples of this before. I throw myself upon my sword. On Thu, Apr 19, 2012 at 12:04 AM, David Grugeon <da...@grugeon.com.au> wrote: > make your second line > > For index = 2 To tmprange.Rows.count > > > On 19 April 2012 16:47, Domain Admin <domainqu...@gmail.com> wrote: >> >> I can get a little further >> >> my code has the error indicated on the For index line. tmprange does >> return with the correct range value. The UsedDataRange function is >> below my code >> >> Set tmprange = UsedDataRange(Sheets(RawData), True) >> For index = 2 To Sheets(RawData).tmprange.Rows.count >> this line gets error Object does not support this property or >> method >> If Sheets(RawData).Cells(index, BarDate).Value = >> Sheets(ContangoSource).Cells(contangoindex, ConDate).Value Then >> Sheets(RawData).Cells(index, EContango).Value = >> Sheets(ContangoSource).Cells(contangoindex, Contango).Value >> Else >> contangoindex = contangoindex + 1 >> Sheets(RawData).Cells(index, EContango).Value = >> Sheets(ContangoSource).Cells(contangoindex, Contango).Value >> End If >> Next index >> >> Function UsedDataRange(Optional WS As Worksheet, Optional >> IncludeEmptyFormulas As Boolean) As Range >> ' does what usedrange is supposed to do >> Dim LookInConstant As Long, FirstCell As Range, LastCell As Range >> If WS Is Nothing Then Set WS = ActiveWorkbook.ActiveSheet >> If IncludeEmptyFormulas Then >> LookInConstant = xlFormulas >> Else >> LookInConstant = xlValues >> End If >> Set LastCell = WS.Cells(WS.Cells.Find(What:="*", SearchOrder:=xlRows, _ >> SearchDirection:=xlPrevious, LookIn:=LookInConstant).Row, >> _ >> WS.Cells.Find(What:="*", SearchOrder:=xlByColumns, _ >> SearchDirection:=xlPrevious, >> LookIn:=LookInConstant).Column) >> Set FirstCell = WS.Cells(WS.Cells.Find(What:="*", After:=LastCell, >> SearchOrder:=xlRows, _ >> SearchDirection:=xlNext, LookIn:=LookInConstant).Row, _ >> WS.Cells.Find(What:="*", After:=LastCell, >> SearchOrder:=xlByColumns, _ >> SearchDirection:=xlNext, LookIn:=LookInConstant).Column) >> Set UsedDataRange = WS.Range(FirstCell, LastCell) >> End Function >> >> On Wed, Apr 18, 2012 at 6:16 PM, Domain Admin <domainqu...@gmail.com> >> wrote: >> > How do you call this? >> > >> > With Sheets(RawData) >> > tmprange = UsedDataRange(, True) >> > Set tmprange = .Range(.Cells(2, BarOpen), >> > .Cells(.tmprange.Rows.count, EContango)) >> > End With >> > >> > gave error Object variable or With block variable not set on the first >> > assignment to tmprange (which is defined as a range) >> > >> > On Wed, Apr 18, 2012 at 4:54 PM, Sam Mathai Chacko <samde...@gmail.com> >> > wrote: >> >> This might be of interest >> >> >> >> >> >> http://www.excelfox.com/forum/f22/replacement-flawed-usedrange-property-374/ >> >> >> >> Regards, >> >> Sam Mathai Chacko >> >> >> >> >> >> On Thu, Apr 19, 2012 at 5:22 AM, Domain Admin <domainqu...@gmail.com> >> >> wrote: >> >>> >> >>> Actually ever weirder. If I keep clearing the current region and >> >>> pasting in the exact same >> >>> data over and over the usedrange number of rows increases by 1 each >> >>> time. >> >>> >> >>> On Wed, Apr 18, 2012 at 4:41 PM, Domain Admin <domainqu...@gmail.com> >> >>> wrote: >> >>> > Sorry Don but I can not comply with your request in this case >> >>> > easily. >> >>> > But I will get there if needed. >> >>> > >> >>> > Asa what you said tells me that what I am doing should work. At the >> >>> > time I clear the contents >> >>> > of the currentrengion is has fewer columns than usedrange but >> >>> > exactly >> >>> > the same number of rows. >> >>> > >> >>> > After clearing or deleting the contents of currentregion and then >> >>> > loading in a new set of data >> >>> > with fewer rows than before, the row count of usedrange is still the >> >>> > same as before. >> >>> > >> >>> > On Wed, Apr 18, 2012 at 4:25 PM, Asa Rossoff <a...@lovetour.info> >> >>> > wrote: >> >>> >> UsedRange includes the intersection of all rows with used cells and >> >>> >> all >> >>> >> columns with used cells. In other words, UsedRange is always a >> >>> >> rectangular >> >>> >> range. >> >>> >> >> >>> >> >> >>> >> >> >>> >> CurrentRegion based on a cell with a value in it also returns a >> >>> >> rectangular >> >>> >> range. RANGE.CurrentRegion returns a RANGE that includes the >> >>> >> specified >> >>> >> RANGE and that is bounded by a completely empty row on top and >> >>> >> bottom, >> >>> >> and a >> >>> >> completely empty column on left and right. >> >>> >> >> >>> >> >> >>> >> >> >>> >> Asa >> >>> >> >> >>> >> >> >>> >> >> >>> >> -----Original Message----- >> >>> >> From: excel-macros@googlegroups.com >> >>> >> [mailto:excel-macros@googlegroups.com] >> >>> >> On Behalf Of Domain Admin >> >>> >> Sent: Wednesday, April 18, 2012 4:12 PM >> >>> >> To: excel-macros@googlegroups.com >> >>> >> Subject: Re: $$Excel-Macros$$ What is left behind when you clear or >> >>> >> delete >> >>> >> cell contents that still affects usedrange? >> >>> >> >> >>> >> >> >>> >> >> >>> >> Deleting the columns from row 2 (save the header row) to xldown and >> >>> >> >> >>> >> shifting cells up does not solve it. >> >>> >> >> >>> >> >> >>> >> >> >>> >> On Wed, Apr 18, 2012 at 4:04 PM, Domain Admin >> >>> >> <domainqu...@gmail.com> >> >>> >> wrote: >> >>> >> >> >>> >>> Never mind about the ROUND part. It returns 0 for a truly empty >> >>> >>> cell. >> >>> >> >> >>> >>> >> >>> >> >> >>> >>> On Wed, Apr 18, 2012 at 4:01 PM, tangledweb >> >>> >>> <domainqu...@gmail.com> >> >>> >>> wrote: >> >>> >> >> >>> >>>> I am doing many tests on columns of date pasted into a sheet. >> >>> >>>> Sometimes >> >>> >> >> >>> >>>> more rows, sometimes less. >> >>> >> >> >>> >>>> It is important that I be able to define the working range as >> >>> >>>> just >> >>> >>>> what >> >>> >>>> has >> >>> >> >> >>> >>>> data in it. However when I use >> >>> >> >> >>> >>>> clear or delete on the usedrange or currentregion or any other >> >>> >>>> way to >> >>> >>>> define >> >>> >> >> >>> >>>> it everything seems to be gone >> >>> >> >> >>> >>>> but there is something left behind that is part of usedrange. >> >>> >>>> The >> >>> >>>> only >> >>> >>>> way >> >>> >> >> >>> >>>> I have found to make this work >> >>> >> >> >>> >>>> is to delete the entire row which deletes other stuff I want to >> >>> >>>> keep. Is >> >>> >> >> >>> >>>> there really no way to truly empty >> >>> >> >> >>> >>>> the cells? Side not the ROUND function running on those cells >> >>> >>>> puts >> >>> >>>> in a >> >>> >>>> 0 >> >>> >> >> >>> >>>> >> >>> >> >> >>> >>>> -- >> >>> >> >> >>> >>>> FORUM RULES (986+ members already BANNED for violation) >> >>> >> >> >>> >>>> >> >>> >> >> >>> >>>> 1) Use concise, accurate thread titles. Poor thread titles, like >> >>> >>>> Please >> >>> >> >> >>> >>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need >> >>> >>>> Advice >> >>> >>>> will >> >>> >> >> >>> >>>> not get quick attention or may not be answered. >> >>> >> >> >>> >>>> >> >>> >> >> >>> >>>> 2) Don't post a question in the thread of another member. >> >>> >> >> >>> >>>> >> >>> >> >> >>> >>>> 3) Don't post questions regarding breaking or bypassing any >> >>> >>>> security >> >>> >> >> >>> >>>> measure. >> >>> >> >> >>> >>>> >> >>> >> >> >>> >>>> 4) Acknowledge the responses you receive, good or bad. >> >>> >> >> >>> >>>> >> >>> >> >> >>> >>>> 5) Cross-promotion of, or links to, forums competitive to this >> >>> >>>> forum >> >>> >>>> in >> >>> >> >> >>> >>>> signatures are prohibited. >> >>> >> >> >>> >>>> >> >>> >> >> >>> >>>> NOTE : Don't ever post personal or confidential data in a >> >>> >>>> workbook. >> >>> >>>> Forum >> >>> >> >> >>> >>>> owners and members are not responsible for any loss. >> >>> >> >> >>> >>>> >> >>> >> >> >>> >>>> >> >>> >>>> >> >>> >>>> >> >>> >>>> ------------------------------------------------------------------------------------------------------ >> >>> >> >> >>> >>>> To post to this group, send email to >> >>> >>>> excel-macros@googlegroups.com >> >>> >> >> >>> >> >> >>> >> >> >>> >> -- >> >>> >> >> >>> >> FORUM RULES (986+ members already BANNED for violation) >> >>> >> >> >>> >> >> >>> >> >> >>> >> 1) Use concise, accurate thread titles. Poor thread titles, like >> >>> >> Please >> >>> >> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need >> >>> >> Advice >> >>> >> will >> >>> >> not get quick attention or may not be answered. >> >>> >> >> >>> >> >> >>> >> >> >>> >> 2) Don't post a question in the thread of another member. >> >>> >> >> >>> >> >> >>> >> >> >>> >> 3) Don't post questions regarding breaking or bypassing any >> >>> >> security >> >>> >> measure. >> >>> >> >> >>> >> >> >>> >> >> >>> >> 4) Acknowledge the responses you receive, good or bad. >> >>> >> >> >>> >> >> >>> >> >> >>> >> 5) Cross-promotion of, or links to, forums competitive to this >> >>> >> forum >> >>> >> in >> >>> >> signatures are prohibited. >> >>> >> >> >>> >> >> >>> >> >> >>> >> NOTE : Don't ever post personal or confidential data in a >> >>> >> workbook. >> >>> >> Forum >> >>> >> owners and members are not responsible for any loss. >> >>> >> >> >>> >> >> >>> >> >> >>> >> >> >>> >> >> >>> >> ------------------------------------------------------------------------------------------------------ >> >>> >> >> >>> >> To post to this group, send email to excel-macros@googlegroups.com >> >>> >> >> >>> >> -- >> >>> >> FORUM RULES (986+ members already BANNED for violation) >> >>> >> >> >>> >> 1) Use concise, accurate thread titles. Poor thread titles, like >> >>> >> Please >> >>> >> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need >> >>> >> Advice >> >>> >> will >> >>> >> not get quick attention or may not be answered. >> >>> >> >> >>> >> 2) Don't post a question in the thread of another member. >> >>> >> >> >>> >> 3) Don't post questions regarding breaking or bypassing any >> >>> >> security >> >>> >> measure. >> >>> >> >> >>> >> 4) Acknowledge the responses you receive, good or bad. >> >>> >> >> >>> >> 5) Cross-promotion of, or links to, forums competitive to this >> >>> >> forum in >> >>> >> signatures are prohibited. >> >>> >> >> >>> >> NOTE : Don't ever post personal or confidential data in a workbook. >> >>> >> Forum >> >>> >> owners and members are not responsible for any loss. >> >>> >> >> >>> >> >> >>> >> >> >>> >> ------------------------------------------------------------------------------------------------------ >> >>> >> To post to this group, send email to excel-macros@googlegroups.com >> >>> >> >>> -- >> >>> FORUM RULES (986+ members already BANNED for violation) >> >>> >> >>> 1) Use concise, accurate thread titles. Poor thread titles, like >> >>> Please >> >>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need >> >>> Advice will >> >>> not get quick attention or may not be answered. >> >>> >> >>> 2) Don't post a question in the thread of another member. >> >>> >> >>> 3) Don't post questions regarding breaking or bypassing any security >> >>> measure. >> >>> >> >>> 4) Acknowledge the responses you receive, good or bad. >> >>> >> >>> 5) Cross-promotion of, or links to, forums competitive to this forum >> >>> in >> >>> signatures are prohibited. >> >>> >> >>> NOTE : Don't ever post personal or confidential data in a workbook. >> >>> Forum >> >>> owners and members are not responsible for any loss. >> >>> >> >>> >> >>> >> >>> ------------------------------------------------------------------------------------------------------ >> >>> To post to this group, send email to excel-macros@googlegroups.com >> >> >> >> >> >> >> >> >> >> -- >> >> Sam Mathai Chacko >> >> >> >> -- >> >> FORUM RULES (986+ members already BANNED for violation) >> >> >> >> 1) Use concise, accurate thread titles. Poor thread titles, like Please >> >> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice >> >> will >> >> not get quick attention or may not be answered. >> >> >> >> 2) Don't post a question in the thread of another member. >> >> >> >> 3) Don't post questions regarding breaking or bypassing any security >> >> measure. >> >> >> >> 4) Acknowledge the responses you receive, good or bad. >> >> >> >> 5) Cross-promotion of, or links to, forums competitive to this forum in >> >> signatures are prohibited. >> >> >> >> NOTE : Don't ever post personal or confidential data in a workbook. >> >> Forum >> >> owners and members are not responsible for any loss. >> >> >> >> >> >> ------------------------------------------------------------------------------------------------------ >> >> To post to this group, send email to excel-macros@googlegroups.com >> >> -- >> FORUM RULES (986+ members already BANNED for violation) >> >> 1) Use concise, accurate thread titles. Poor thread titles, like Please >> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will >> not get quick attention or may not be answered. >> >> 2) Don't post a question in the thread of another member. >> >> 3) Don't post questions regarding breaking or bypassing any security >> measure. >> >> 4) Acknowledge the responses you receive, good or bad. >> >> 5) Cross-promotion of, or links to, forums competitive to this forum in >> signatures are prohibited. >> >> NOTE : Don't ever post personal or confidential data in a workbook. Forum >> owners and members are not responsible for any loss. >> >> >> ------------------------------------------------------------------------------------------------------ >> To post to this group, send email to excel-macros@googlegroups.com > > > > > -- > David Grugeon > > -- > FORUM RULES (986+ members already BANNED for violation) > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will > not get quick attention or may not be answered. > > 2) Don't post a question in the thread of another member. > > 3) Don't post questions regarding breaking or bypassing any security > measure. > > 4) Acknowledge the responses you receive, good or bad. > > 5) Cross-promotion of, or links to, forums competitive to this forum in > signatures are prohibited. > > NOTE : Don't ever post personal or confidential data in a workbook. Forum > owners and members are not responsible for any loss. > > ------------------------------------------------------------------------------------------------------ > To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com
