Check for next blank cell in given range until range is completed

[richand1]

All, I'm not too sure how to upload an example of the spreadsheet but will try and explain:

I have a financial sheet that I would like to create a rolling 24 month tracker in. The periods will range from column C to column Z.

In terms of rows in the range, I have a few: Income (C20:Z20), Overheads (C21:Z21), Core Spend (C22:Z22) etc and all will be relevant in each period.

This is a new financial sheet so therefore the first figure to be entered will be in C20/21/22 etc and all subsequent cells will be blank for now.

Once I have gotten to the last column in the range - Z20/21/22 etc in May 2009 - I wish to clear the contents of C20/21/22 etc, move the whole range left in order to keep the historic data and free up Z20/21/22 again to enter the latest months' figure in.

If an example is needed then please let me know how to upload one.

Many, many thanks
D

 

[Bong]

The title of your post seems to imply that you want to do something that is not in what you describe. That is

Once I have gotten to the last column in the range - Z20/21/22 etc in May 2009 - I wish to clear the contents of C20/21/22 etc, move the whole range left in order to keep the historic data and free up Z20/21/22 again to enter the latest months' figure in.

says nothing about checking for a blank cell. Anyway, lets say you have some data in A20:Z22. As I understand it, you want now to leave columns A and B alone, move D20:Z22 to C20:Y22 and clear Z20:Z22. Is that right?

set r1=sheet1.[d20:z22]
set r2=sheet1.[c20:y22]
r1.copy(r2)
sheet1.[z20:z22].clear

_________________
Bob Rashkin

 

[Rofeu]

I've had to set up similar things. They way I do it is to use one sheet to store al my data then for Z set the header to =EOMONTH(TODAY(),-1)+1 (which is the current month) and calculate backwards to C deducting one month every column (=EOMONTH(Z1,-2)+1). I then use lookup or sum functions to get the required data from the other sheet (database).

This way you only need to append new data to your database and the rest is all done via the formulae.

Cheers,

Roel

 

[AnotherHiggins]

I'd propose a different approach.

Don't delete the old data. Instead, use Named Ranges to only report on the most recent data! No VBA needed, and you don't lose data after 24 months, which means you don't need to create a new file each month in order to archive data.

Go to Inert > Name > Define, type in a name (let's say rngRolling24Month). In the "refers to" box, you'd type in something like:
[tab]=Offset(Sheet1!$C$20, 0, count(Sheet1!$20:$20)-24, 1, 24)

If you need to account for having less than 24 figures entered (which it sounds like you do), then you could use this formula instead:
[tab]=if(count(Sheet1!$20:$20) < 24, Offset(Sheet1!$C$20,0,0,1,24), Offset(Sheet1!$C$20,0,count(Sheet1!$20:$20)-24,1,24))

Also, I'd suggest that you sort dates going DOWN, not across. There are 65.536 rows in excel, only 256 columns. Dates going down will give you plenty of room. (With one day/row, we'll all be dead before you run out of room, and that's assuming you stay with Excel 2003 or earlier.)

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

Help us help you. Please read FAQ 181-2886 before posting.

 

[richand1]

Chaps, thank you very much indeed for your replies!

Just for clarification:

Because I am starting with a blank slate at the left-most column in the range (C20/21/22), the next 23 columns in the range (D20/21/22:Z20/21/22) will be blank. Up to the point when - 24 months in the future - I finally fill in the last column in the range (Z20/21/22), I then, for the next month, want the code to recognise that the range is now complete (C20:Z22) and then, from the 2nd column in the range (D20/21/22) to the last (Z20/21/22) have that particular sub-range pasted one column to the left - over the oldest month resident in column C, leaving period 24 blank and ready for the most recent figures to be entered.
__________________________________________________________

I think it worth mentioning at this point that there will be 70+ financial sheets - split into business regions and sub-regions resulting in each spreadsheet having a number of these - and they will definitely be emailed all over the place, so size is a concern -

John, thank you for your thoughts/ideas; I had forgotten how useful named ranges could be, but alas I will need have the oldest month overwritten once the range size hits that 24 month limit. Would the offset formula you have given be useful in an if statement in VBA for evaluating the size of the range and then copying it over if true/false etc?

Would someone mind assisting me in writing the statement if this is indeed a good way to do it?

Thank you all very much.
Rich

 

[Rofeu]

Hi,


with 70+ sheets needing to be mailed 'all over the place', I'd strongly suggest using a different approach altogether.

Something like a SQLServer database that holds all your data and (excel) reports published on an intranet or a kind of template document that pulls the relevant data from the SQL DB. You could then even set up privileges so users can only see the regions/sub-regions that pertain to them.

Cheers,

Roel

 

[richand1]

Thanks Roel. That approach is definitely the preferable one, but I have limited time, not much experience with intranet work and no access to an SQL server.

The sheets would ultimately be printed out for use in monthly business finance reviews as a pack. The target audience is also renowed for their lack of IT experience plus they prefer to have a printed version handed to them on a plate.

Back to square one I'm afraid...

 

[Rofeu]

I just reread your post 27 Jun 08 3:41. My initial suggestion gives you what you want with the one small exception that it starts in Z instead of D, but why not putting your months hard in your header row until you reach Z and then change them for the formulae I proposed?

Should work fine and with minimal manual effort.
The only real issue will be with the filesize if we're talking 70+ sheets in the workbook.

Another option might be to add a helper column to the database-sheet that identifies current month to current month - 24, and build pivottables on that filtering out everything that's not within range. You can then change the table option 'Save table with underlying data' to false and that should prevent the file from blowing up in size.

Cheers,

Roel

 

[richand1]

Roel, thank you very much for your replies and help with this.

It seems I have a couple of viable options!

Because I didn't have enough time to automate all of the workbooks (there are 6 workbooks with 70+ sheets between them) I am having to do this manually for the first month. I'll give some feedback on which of these options I have chosen within the next couple of weeks.

Thanks Roel and all.

Rich

 

[richand1]

With this, I just decided to input the 24 month's worth of data so the range that will be moving every month is the same in every sheet: a simple copy and paste!

Thanks again all.