I am just using cut and paste to get the data. No other option as
different sources require different data munging.
Your delete method did not work either.
usedrange is incrementing each time by 1 as mentioned. I can not get
currentregion due to one of my usual misunderstandings.
I have tried what you see, using .application, etc but nothing works.
With Sheets(RawData)
Debug.Print "UsedRange: " & .UsedRange.Address
Debug.Print "CurrentRegion: " & Range.CurrentRegion.Address
Set tmprange = .Range(.Cells(2, BarOpen), .Cells(stoprawdata, StopCol))
tmprange.Value2 = .Evaluate("IF(ROW(),round(" &
tmprange.Address & ",2))") 'use evaluate instead of code below.
End With
On Wed, Apr 18, 2012 at 4:59 PM, Asa Rossoff <a...@lovetour.info> wrote:
> If:
> - CurrentRegion is defining your range correctly,
> - you have data in other columns you want to preserve
> - you have no other data below your data of interest in the same columns
> Then:
> The following should work:
>
> Debug.Print "UsedRange: " & WORKSHEET.UsedRange.Address
> Debug.Print "CurrentRegion: " & RANGE.CurrentRegion.Address
> RANGE.CurrentRegion.Delete(xlshiftup)
> Debug.Print "UsedRange: " & WORKSHEET.UsedRange.Address
> 'Import Data here
> Debug.Print "UsedRange: " & WORKSHEET.UsedRange.Address
> Debug.Print "CurrentRegion: " & RANGE.CurrentRegion.Address
>
> If that doesn't work, please share how you are importing your data, and the
> output of the debug.print statements.
>
> P.S. You might consider using unique sheets for each table of data so there
> is no data in other columns parrallel to your table of data. Then, delete
> entire rows.
>
> Asa
>
> -----Original Message-----
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
> Sent: Wednesday, April 18, 2012 4:42 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?
>
> 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
>
> --
> 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
