Ok but if I use Set BarDate = ... where BarDate is a range object and I do this inside a procedure, but BarDate is defined at the module level, then is BarDate the range available to other procedures?
On Mon, Apr 9, 2012 at 2:35 AM, Asa Rossoff <a...@lovetour.info> wrote: >> This works. Is there a reason why it is not better? > Yes. It won't always work. RANGE.Columns is relative to that range (as is > RANGE.Rows, RANGE.Cells, and RANGE.Range), not the worksheet. UsedRange > doesn't always start in A1. > >> This is puzzling. In the case of .rows(1) it can handle the range as >> the array but in this case you have to explicitly convert to an array. > Perhaps because in Excel 2007+ there are 16,384 columns, much less than the > million+ rows, > Your syntax might well work in prior versions of Excel, where there are just > 65,536 rows. > >> And then further down to this though not as easy to read and understand >> >> contangoindex = _ >> WorksheetFunction.Match(Sheets(RawData).Cells(2, BarDate).Value, _ >> Sheets(ContangoSource).UsedRange.Columns(ConDate).Value, 0) >> >> So what is happening here? Is this still just by adding the .value >> converting the range into an array as some secret VBA thing where you >> are extracting the values out of the range and they have to go >> somewhere to it creates and array for them? > Once you specify the .Value property of a range larger than one cell, you > are specifying an array. It doesn't matter if you don't create a variable > to refer to it. If you read the Help entry for WorksheetFunction.Match > you'll see that the data type for all the arguments is Variant. Your array > will be stored in memory as an array of Variant (the .Value property is > Variant) and stored inside another Variant (full name "Variant Array of > Variant"), and a pointer to it will be passed to WorksheetFunction.Match. > When Match is done with it's work, the array will be destroyed from memory > since there are no other pointers to it. If you had a need to refer to it > again, it would be a very good idea to create the variable first and not > have VBA waste energy creating and destroying the whole array multiple > times. > >> You are right though. Once you find a working bone you can keep >> gnawing on it until down to minimal shard that still functions. > It can help in understanding what's what. > >> When you use SET to create pointers they can only be done inside a >> Sub. If you want to use them in another SUB do you have to do the SET >> again or if in the same module will the SET be know by other Subs? > It's not the Set statement that matters for your question. All variables, > as well as procedures (be they Sub or Function procedures), and modules, > have a certain scope within which they can be referred to. What determines > the scope, in the case of a variable, is where it is declared, what > statement was used to declare it, and whether it is passed ByRef to any > other Sub or Function (although in the last case the other procedure would > have it's own local name for the variable, even though it points to the same > memory location). > > Variables can be declared at the module level as: > Public varname As vartype ' uses global or optionally workbook scope > Private varname As vartype ' uses module scope > Dim varname As vartype ' uses module scope > > In a procedure they can be declared: > Dim varname As vartype ' uses local (procedure) scope > Static varname as vartype ' uses local (procedure) scope and retains value > between calls > > Then use Set if you are assigning an object to a variable, or use Let or the > usual shorthand of varname=value if you are assigning any other data type. > > Asa > > -----Original Message----- > From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] > On Behalf Of Domain Admin > Sent: Sunday, April 08, 2012 1:36 AM > To: excel-macros@googlegroups.com > Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire > spreadsheet? > > This works. Is there a reason why it is not better? > > I did your array replacement but I changed this > > Set InputRange = > Application.Intersect(Sheets(ContangoSource).UsedRange, > Sheets(ContangoSource).Columns(ConDate)) > > with this > > Set InputRange = Sheets(ContangoSource).UsedRange.Columns(ConDate) > > but it does not work to get rid of the array replacement and go all > the way to this > > contangoindex = WorksheetFunction.Match(Sheets(RawData).Cells(2, > BarDate).Value, Sheets(ContangoSource).UsedRange.Columns(ConDate), 0) > > This is puzzling. In the case of .rows(1) it can handle the range as > the array but in this case you have to explicitly convert to an array. > > But it can be further whittled down to this > > CellsOfInterest = Sheets(ContangoSource).UsedRange.Columns(ConDate).Value > without these lines > > ' Set InputRange = Sheets(ContangoSource).UsedRange.Columns(ConDate) > ' CellsOfInterest = InputRange.Value > > And then further down to this though not as easy to read and understand > > contangoindex = WorksheetFunction.Match(Sheets(RawData).Cells(2, > BarDate).Value, > Sheets(ContangoSource).UsedRange.Columns(ConDate).Value, 0) > > So what is happening here? Is this still just by adding the .value > converting the range into an array as some secret VBA thing where you > are extracting the values out > of the range and they have to go somewhere to it creates and array for > them? You are right though. Once you find a working bone you can > keep > gnawing on it until down to minimal shard that still functions. > > > When you use SET to create pointers they can only be done inside a > Sub. If you want to use them in another SUB do you have to do the SET > again or if in the same module will the SET be know by other Subs? > > > On Sat, Apr 7, 2012 at 10:55 PM, Domain Admin <domainqu...@gmail.com> wrote: >> Not sure I follow that. Unless it creates an indexed database that it >> uses internally or creates hash tables or some other fast indexing >> then the match function still has to >> test all the entries until it finds a match. If I write it directly >> in machine language that is still the case. That indexing is overhead >> but if only done once and used repeatedly then it will be more >> efficient. Speed is not an issue for what I am doing. No run is ever >> likely to take more than a couple of minutes but just by background I >> still like to create the best code I can. I am just afraid I can not >> afford the time to learn to do so as getting this running quickly is >> the highest priority. I will still document it well and write it as >> well as I can but I am sure that will mean my style is most likely to >> be more procedural than object oriented to its best use. Unfortunate >> but reality. >> >> I am sure I will call on you again though as you have been quite >> helpful and no good deed ever goes unpunished :) >> >> On Sat, Apr 7, 2012 at 10:22 PM, Asa Rossoff <a...@lovetour.info> wrote: >>> It was type mismatch >>> >>> Oh, well. VBA is interpreted (when compiled it compiles to a bytecode, > not >>> executable code) so as a matter of practice it's good to avoid loops when >>> there are good alternatives, but if the speed is not an issue in your > case >>> whatever works. >>> >>> >>> -----Original Message----- >>> From: excel-macros@googlegroups.com > [mailto:excel-macros@googlegroups.com] >>> On Behalf Of Domain Admin >>> Sent: Saturday, April 07, 2012 10:14 PM >>> To: excel-macros@googlegroups.com >>> Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire >>> spreadsheet? >>> >>> When you did not use the intersection was the error unable to find the >>> match function? >>> >>> In any case >>> >>> contangoindex = 1 >>> Do While Sheets(RawData).Cells(2, BarDate).Value <> >>> Sheets(ContangoSource).Cells(contangoindex, ConDate).Value >>> contangoindex = contangoindex + 1 >>> Loop >>> >>> works, is a lot easier to read code, and probably as efficient as you >>> can get so I think I will stop chasing this greased pig. >>> Thanks for all the help. I will still go back and look through the >>> debug links and look for the other references you mentioned. >>> >>> On Sat, Apr 7, 2012 at 10:04 PM, Asa Rossoff <a...@lovetour.info> wrote: >>>> I tried this little variation: >>>> Function matchtest() As Long >>>> Dim contangoindex As Long >>>> >>>> Dim CellsOfInterest As Variant >>>> Dim InputRange As Range >>>> Set InputRange = Application.Intersect(Sheet2.UsedRange, >>>> Sheet2.Columns(1)) >>>> CellsOfInterest = InputRange.Value >>>> ' Determine where the index in ContangSource is of the first date that >>>> matches the first date in RawData >>>> contangoindex = WorksheetFunction.Match(Sheet1.Cells(2, 1).Value, >>>> CellsOfInterest, 0) >>>> >>>> matchtest = contangoindex >>>> End Function >>>> >>>> then in immediate: >>>> ? matchtest >>>> >>>> It worked fine. >>>> In the first sheet I put the formula =TODAY() in A2 >>>> In the second sheet I put the formula =TODAY()-10+ROW() in A1 and copied >>>> down to A24 >>>> >>>> The result in Immediate: >>>> 10 >>>> >>>> >>>> The function did not work without Application.Intersect though --- the >>> array >>>> was just too large and Match balked. >>>> >>>> In some instances with a full column range argument it did work in > earlier >>>> tests, but that's likely an example of some of the inconsistencies you > may >>>> run into with VBA if you use different types of variables than expected. >>>> Excel does a lot of trying to be clever in order to make the language >>> syntax >>>> hopefully simpler for beginners, but the side effects when you allow it > to >>>> coerce variables that way to another type, or various other ways of not >>>> being explicit make it so it is difficult to predict Excel's behavior. >>>> >>>> Also, the change in Excel 2007 to massive sheet sizes creates new memory >>> and >>>> compatibility issues.. not everything can handle 1000000000 data points >>> (or >>>> more when working with multiple columns). >>>> >>>> Asa >>>> >>>> -----Original Message----- >>>> From: excel-macros@googlegroups.com > [mailto:excel-macros@googlegroups.com] >>>> On Behalf Of Domain Admin >>>> Sent: Saturday, April 07, 2012 9:31 PM >>>> To: excel-macros@googlegroups.com >>>> Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire >>>> spreadsheet? >>>> >>>> That gave me an idea but it did not work. I thought maybe it could >>>> not find the match function because that meant it wanted one where the >>>> second variable was a range instead of an array so I tried your >>>> conversion. I may not have done it right of course. In the earlier >>>> match functions is .rows(1) an array or a range? >>>> >>>> Anyway I did this but got the error Type Mismatch >>>> >>>> Dim CellsOfInterest As Variant >>>> Dim InputRange As Range >>>> Set InputRange = Sheets(ContangoSource).Columns(ConDate) >>>> CellsOfInterest = InputRange.Value >>>> ' Determine where the index in ContangSource is of the first date >>>> that matches the first date in RawData >>>> contangoindex = WorksheetFunction.Match(Sheets(RawData).Cells(2, >>>> BarDate).Value, CellsOfInterest, 0) >>>> >>>> On Sat, Apr 7, 2012 at 9:17 PM, Asa Rossoff <a...@lovetour.info> wrote: >>>>> You don't need to know in advance the size of your output array, you > can >>>> use >>>>> ReDim Preserve to enlarge it as you go. >>>>> >>>>> -----Original Message----- >>>>> From: excel-macros@googlegroups.com >>> [mailto:excel-macros@googlegroups.com] >>>>> On Behalf Of Domain Admin >>>>> Sent: Saturday, April 07, 2012 9:10 PM >>>>> To: excel-macros@googlegroups.com >>>>> Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the > entire >>>>> spreadsheet? >>>>> >>>>> This is interesting. It would seem to allow for the possibility of >>>>> filling in my entire results sheet as an array then dumping it to the >>>>> sheet all at once. >>>>> But probably not since there is no apriori way to know how many rows >>>>> there will be. >>>>> >>>>> On Sat, Apr 7, 2012 at 8:56 PM, Asa Rossoff <a...@lovetour.info> wrote: >>>>>> Another one I started typing didn't finish and send. >>>>>> >>>>>>> (5) In your rawdata loop, there is no need for parenthesis around >>>>>>> the boolean expression. VBA will always evaluate the entire > expression >>>>>>> between If/Then separately from the rest of the If/Then statement. I >>>>>>> haven't analyzed your loop too closely, but you can consider if you >>>> could >>>>>>> use Range.Find or WorksheetFunction.Match instead of looping through >>> the >>>>>>> whole range. If that would result in fewer iterations it could be >>>>>>> significantly faster. >>>>>> >>>>>> Another speed-up trick for when you do need to loop through a range, > is >>>> to >>>>>> copy the range to a variant array first, then loop through the array >>>>>> instead: >>>>>> Dim CellsOfInterest As Variant >>>>>> Dim InputRange As Range >>>>>> Set InputRange = Range(whatever) >>>>>> ' Next line creates an array of Variant that could include String, >>>> Double, >>>>>> Currency, Date/Time, Boolean, or Variant/Empty. Since values are >>>>> converted >>>>>> to VBA datatypes, minor rounding and extra time are involved: >>>>>> CellsOfInterest = InputRange.Value ' Creates an array >>>>>> ' This ALTERNATIVE line is similar, but faster, and returns values as >>>>>> internally stored on a worksheet. Type will only be String, Double, or >>>>>> Variant/Empty: >>>>>> CellsOfInterest = InputRange.Value2 ' Creates an array >>>>>> >>>>>> This array you've created has two dimension. The first is for the > row, >>>>> the >>>>>> second, the column. >>>>>> >>>>>> 'If you don't know how many rows and columns you've retrieved, you can >>>>> check >>>>>> the size of the array: >>>>>> Dim RowCount As Long, ColumnCount As Long >>>>>> RowCount=UBound(CellsOfInterest, 1) >>>>>> ColumnCount=UBound(CellsOfInterest, 2) >>>>>> >>>>>> 'Then for example, loop Through rows, check certain columns for >>>>> conditions, >>>>>> and if met, copy entire row to another array, manipulate in place, > etc.: >>>>>> Dim Row As Long, Column As Long >>>>>> Dim >>>>>> For Row = 1 To RowCount >>>>>> For Column = 1 To ColumnCount >>>>>> '''''''''''''''''''''' no time to give you full examples now.. you > may >>>>> not >>>>>> even be interested :) >>>>>> >>>>>> Quickly, though, I'll point out that if you have copied desired values >>> to >>>>> a >>>>>> new two-dimensional variant array, it could be copied to the >>> destination. >>>>>> Dim OutputArray As Variant >>>>>> Dim OutputRowCount As Long, OutputColumnCount As Long >>>>>> Dim OutputRange As Range >>>>>> ' When OutputArray is ready, and OutputRowCoutn/OutputColumnCount set >>>>> during >>>>>> population of that array... >>>>>> Set OutputRange=Sheets("sheetname").range("a1").resize(OutputRowCount, >>>>>> OutputColumnCount) >>>>>> OutputRange.Value = OutputArray >>>>>> >>>>>> >>>>>> Off for the evening, >>>>>> Take it easy, >>>>>> Asa >>>>>> >>>>>> >>>>>> -----Original Message----- >>>>>> From: excel-macros@googlegroups.com >>>> [mailto:excel-macros@googlegroups.com] >>>>>> On Behalf Of Asa Rossoff >>>>>> Sent: Saturday, April 07, 2012 4:19 PM >>>>>> To: excel-macros@googlegroups.com >>>>>> Subject: RE: $$Excel-Macros$$ Why is usedrange here returning the > entire >>>>>> spreadsheet? >>>>>> >>>>>> The term reference in Excel, when referring to a cell or range, means >>> the >>>>>> textual description of that cell or range (the Address property of a >>>> range >>>>>> object returns the range reference). In VBA and other object oriented >>>>>> languages, reference also refers to variables that refer to an object, >>>> but >>>>>> that's a whole nother topic. In any case, the word has a special >>>> meaning, >>>>>> so I'm glad you posted more of your code to clarify what you meant :) >>>>>> >>>>>> (1) Make sure OPTION EXPLICIT is the first line in your module, it > will >>>>>> alert you if VBA thinks you didn't declare a variable (i.e. you > misspell >>>> a >>>>>> variable name or other keyword somewhere) >>>>>> (2) In InitRefs() I would use (to ensure UsedRange will be updated): >>>>>> Sheets(Results).UsedRange.ENTIREROW.Offset(1).DELETE ' delete > results >>>>>> from previous run >>>>>> OR, likely a hair faster: >>>>>> Sheets(Results).Cells.Resize(-1).Offset(1).Delete ' delete results >>>> from >>>>>> previous run >>>>>> (3) All variables storing row or column indexes should be Long. In >>>>>> LoadContangoSource() try using these declares: >>>>>> dim contangoindex As Long >>>>>> Dim startrawdata As Long >>>>>> Dim stoprawdata As Long >>>>>> Dim index As Long >>>>>> In your global declares: >>>>>> Public BarDate As Long >>>>>> Public ConDate As Long >>>>>> Public Contango As Long >>>>>> Public EContango As Long '(I assume this is a row/column index) >>>>>> (4) In Your problem line, you need to specifiy a range object to match >>>> in. >>>>>> You are trying to use a variable holding an integer value as a > property >>>> of >>>>> a >>>>>> worksheet, and also expecting it to return a range object when you do >>> so. >>>>>> This should work, though it will search the header row and potentially >>>> the >>>>>> unused range (a reason to use range variables and define the exact > range >>>>>> needed in advance since it is time consuming to create range objects >>>>>> repeatedly): >>>>>> startrawdata = >>>>>> WorksheetFunction.Match(Sheets(ContangoSource).Cells(2, >>>>>> ConDate).Value, Sheets(RawData).Columns(BarDate), 0) >>>>>> (5) In your rawdata loop, there is no need for parenthesis around the >>>>>> boolean expression. VBA will always evaluate the entire expression >>>>> between >>>>>> If/Then separately from the rest of the If/Then statement. I haven't >>>>>> analyzed your loop too closely, but you can consider if you could use >>>>>> Range.Find or WorksheetFunction.Match instead of looping through the >>>> whole >>>>>> range. If that would result in fewer iterations it could be >>>> significantly >>>>>> faster. >>>>>> >>>>>> Asa >>>>>> >>>>>> >>>>>> -----Original Message----- >>>>>> From: excel-macros@googlegroups.com >>>> [mailto:excel-macros@googlegroups.com] >>>>>> On Behalf Of Domain Admin >>>>>> Sent: Saturday, April 07, 2012 3:32 PM >>>>>> To: excel-macros@googlegroups.com >>>>>> Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the > entire >>>>>> spreadsheet? >>>>>> >>>>>> No not a range object, just a reference. But here is the entire >>>>>> program. I removed the currently unused portions and ignore Public >>>>>> just have not bothered to change that yet. >>>>>> >>>>>> ' Column reference objects >>>>>> Public BarDate As Double >>>>>> >>>>>> Public ConDate As Double >>>>>> Public Contango As Long >>>>>> Public EContango As Double 'the expanded set of contango values >>>>>> to cover all time periods >>>>>> >>>>>> ' Sheet names and pointers >>>>>> Const RawData As String = "RawData" >>>>>> Const Results As String = "Results" >>>>>> Const ContangoSource As String = "ContangoSource" >>>>>> >>>>>> >>>>>> >>>>>> Sub InitRefs() >>>>>> ' >>>>>> ' Sub which sets the data column names and misc values for the RawData >>>>> sheet >>>>>> ' >>>>>> With Sheets(RawData) >>>>>> BarDate = WorksheetFunction.Match("BarDate", .Rows(1), 0) >>>>>> End With >>>>>> With Sheets(ContangoSource) >>>>>> ConDate = WorksheetFunction.Match("ConDate", .Rows(1), 0) >>>>>> Contango = WorksheetFunction.Match("Contango", .Rows(1), 0) >>>>>> End With >>>>>> >>>>>> Sheets(Results).UsedRange.Offset(1).ClearContents ' clear out >>>>>> results from previous run >>>>>> >>>>>> End Sub >>>>>> Sub LoadContangoSource() >>>>>> >>>>>> ' Sub which takes the data from the ContangoSource sheet which is > per >>>>> day >>>>>> and >>>>>> ' copies it to the RawData sheet with entries for each of the bars. >>>>>> >>>>>> Dim contangoindex As Integer >>>>>> Dim startrawdata As Double >>>>>> Dim stoprawdata As Double >>>>>> Dim index As Integer >>>>>> >>>>>> contangoindex = 2 >>>>>> stoprawdata = Sheets(RawData).UsedRange.Rows.Count 'end of the >>>>>> RawData columns THE LINE THAT RETURNS 999999 >>>>>> ' Determine where the index in ContangSource is of the first date >>>>>> that matches the first date in RawData >>>>>> startrawdata = >>>>>> WorksheetFunction.Match(Sheets(ContangoSource).Cells(2, >>>>>> ConDate).Value, Sheets(RawData).BarDate, 0) THE FAILING MATCH >>>>>> >>>>>> ' Now loop through the RawData entries copying the contango value as >>>>>> many times as needed for the matching dates in RawData. >>>>>> ' Since RawData can be from charts with any interval there may be >>>>>> many entries for each matching ContangoSource date. >>>>>> For index = startrawdata To stoprawdata >>>>>> 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 >>>>>> End If >>>>>> Next index >>>>>> >>>>>> End Sub >>>>>> >>>>>> On Sat, Apr 7, 2012 at 3:22 PM, Asa Rossoff <a...@lovetour.info> wrote: >>>>>>> "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 >>>>>> >>>>>> -- >>>>>> 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 >>>> >>>> -- >>>> 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
