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
