I can use a loop to find the end, but why is usedrange returning all rows? That seems to be a fundamental issue.
On Sat, Apr 7, 2012 at 3:13 PM, Asa Rossoff <a...@lovetour.info> wrote: > p.s. other methods of identifying the real data besides .currentregion are: > 1. Range.End(xlup) and range.end(xlleft) (lookup range.end in help) applied > to the last cell at the extent of the entire worksheet in a used column or > row. This method only considers that one column/row. > 2. worksheetfunction.match or .counta depending on your data. these search > from the top of the range, so they only work if there is a value on every > row. > > You can use a loop with method 1 above to get the best overall result > between the varied methods because you can check all columns and use the > maximum result if you columns might not all extend to the same row, to find > the true last row for all columns. That idea could also be applied to rows > to find the last column, but if you have a lot of rows the time cost is > probably too high, and if it is likely that all cells with data will at last > have some pattern of connection between them, without being entirely > separate by blanks, you're back to depending on .currentregion or the using > method #1 on 1 or a few rows (i.e. row 1 with the headers). > > 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
