"bardate is a column" do you mean it's a range object?
then instead of
Sheets(RawData).BarDate
just use:
BarDate
For more help pleas post whole procedure
-----Original Message-----
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Domain Admin
Sent: Saturday, April 07, 2012 3:14 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
I figured the extra row was cleared but that is not a problem.
It seems on testing that clearcontents is working fine leaving the
formats alone, It appears that delete is also leaving the formats
alone. But this still leaves me with 2 problems
1. why does usedrange seem to be returning the entire row count for
the spreadsheet?
2. Why does this statement return Object does not support this
property or method
startrawdata =
WorksheetFunction.Match(Sheets(ContangoSource).Cells(2,
ConDate).Value, Sheets(RawData).BarDate, 0)
where Condate is a column and BarDate is a column and hopefully
Sheets(RawData).BarDate returns that column as the range for the
Match.
On Sat, Apr 7, 2012 at 2:53 PM, Asa Rossoff <a...@lovetour.info> wrote:
> not well documented but entire row and column formats are only stored once
> in your file and do not effect usedrange.
> Yeah, your efficient statement will clear the used range of the
spreadsheet
> except for the first row. it will also clear the next row down from the
> used range (which was already clear). Not to mention, even if you had
> specified the correct range, ClearContents is like the delete key - it
> clears the text and formulas. Clearing is not the same as deleting.
>
> If you have a table of data that is the only significant data on the
sheet,
> this will delete other rows/columns, I beliewve - untested - save first
> (although, syou probably don't need to code this; this situation does not
> arise if you delete rows that are no longer needed as a matter of habit
> rather than clearing them):
> with activesheet
> set r=.range("a1").currentregion
> .Cells.Resize(-r.rows.count).offset(r.rows.count).delete
> .cells.resize(0,-r.columns.count).offset(r.columns.count).delete
> end with
>
> Asa
>
> -----Original Message-----
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Domain Admin
> Sent: Saturday, April 07, 2012 2:40 PM
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire
> spreadsheet?
>
> Ok then I can not use this method at all becaues the columns have
> formats set for the entire column (really not efficient for that to be
> part of userange).
> And I am guessing this statement that I thought was efficient is
> actually clearing the entire spreadsheet and not jus the part with
> data (except the header row)?
>
> Sheets(Results).UsedRange.Offset(1).ClearContents
>
> Best alternative for finding the last row of data in a spreadsheet?
>
> And this statement is returning Object does not support this property
> or method. I was pretty sure it would not work but worth a shot.
>
> startrawdata =
> WorksheetFunction.Match(Sheets(ContangoSource).Cells(2,
> ConDate).Value, Sheets(RawData).BarDate, 0)
>
> is the problem that worksheetfunction needs a particular sheet?
> ConDate and BarDate are column names and this concept seems to work
> elsewhere
> but not sure if Sheets(RawData).Bardate really returns that column as a
> range?
>
> On Sat, Apr 7, 2012 at 2:30 PM, Asa Rossoff <a...@lovetour.info> wrote:
>> you should highlight the entire rows (and same for extra columns),
>> right-click, delete
>>
>> the delete key won't cut it (clears the text and formulas but leaves
>> formats)
>>
>> or I select those rows/columns, then Alt-E,D
>>
>> if selecting some cells but not entire row/column, you can delete entire
> row
>> with Alt-E,D,R and entire column with Alt-E,D,C
>>
>>
>>
>> From: excel-macros@googlegroups.com
[mailto:excel-macros@googlegroups.com]
>> On Behalf Of tangledweb
>> Sent: Saturday, April 07, 2012 2:25 PM
>> To: excel-macros@googlegroups.com
>> Subject: $$Excel-Macros$$ Why is usedrange here returning the entire
>> spreadsheet?
>>
>>
>>
>> This statement is returning 999999 though I deleted everthing below the
>> actual data to make sure no cells below it were used
>>
>>
>>
>> stoprawdata = Sheets(RawData).UsedRange.Rows.Count - 1
>>
>>
>>
>>
>>
>> --
>> 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
