FinalRow = Range("B65536").End(xlup).Row 1

[WaterSprite]

FinalRow = Range("B65536").End(xlup).Row
If WorksheetFunction.countA(cells) > 0 then
LastColumn = Cells.find(what:="*", after:=[B12]. _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).column


The above code works great. Using it along with CurrentRegion, I can pick up a block of Excel that starts at B15 and currently ends at P69, but at the end of the year will be P380 and I can park the entire block of code at IG15 or I can park it at IG65136.

My question is this. Once I have parked the block of Excel at either of those two places, I cannot find it using a modified version of the above code. IE changing FinalRow to FinalRow3 and the range to Q65536 and the colums LastColum3 and the "After" to Q12. I thought if I just changed the range to be looked at, the block of Excel that was in the new location would be found.

The reason I want to be able to find the parking place via code is so the computer will do all of the work each January 1st of closing out the year and starting a new year's worth of data.

 

[AnotherHiggins]

I can park the entire block of code at IG15 or I can park it at IG65136.

Sorry, but what does that mean?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

John,

I think that "park" is to COPY.

But that raises a question in my mind; "Why chop up a, what appears that might be, perfectly good and utilitarian data source into dirrerent year chunks? It makes absolutely no sense, when considering that your data is a database.

Excel's plethora of data analysis and data reporting tools rely on CONSOLIDATED data, not SEGMENTED data. You could easily, in many cases, produce a pivot table or query for a specific, Day, Week, Month, Quarter, Year or any other conceivable span, in a matter of SECONDS.

Segmented data results in so many woes and sorrows, blood sweat and tears. You deserve better!

Skip,

_[red][/red]

 

[WaterSprite]

I knew I was going to get busted for this question. So, this is the deal.
I have a spreadsheet that is supposed to run just after midnight every day. It calculates a disinfection profile for the water treatment plant that I am in charge of. This data has to be kept for 10 years. Lawyers.

So, I figured if I just moved each years worth of data to the far side of the spreadsheet. then one worksheet would hold the data until the end of time, since one years worth of data would be 365 rows, 17 columns. 65536 divided by 365 is about 163 years of data in just one part of the worksheet.
But, I need to know where to find where the block of Excel has been moved to each year so I do not have to do this by manual calculation each time. And, sometimes don't you just want to do stuff that no one else around you can do.
Thus,my question, why does range("a65536").end(xlup).row only work once, how come I cannot modify the code to a different macro and work on a different part of the spreadsheet.

 

[SkipVought]

Again, what is the pressing need to move the data, especially with respect to the relatively small number of rows. So you have a table that, with 10 years of data, would have 3600 rows. BIG DEAL! Why make you job more complex than is necessary and optimal?

If I were going to archive data, it would go on some OTHER sheet. ALL tables are best suited to a single sheet, starting in A1.

lNextRow = objTheSheet.[A1].CurrentRegion.Rows.Count + 1

Unless they are small lookup tables, I avoid multiple tables on a sheet. I think its a holdover from the old Lotus 1-2-3 single worksheet days.

Skip,

_[red][/red]

 

[WaterSprite]

Thanks for the comments.