CTRKL-END of a Statement
 

I have an excel file that can have multiple pages of Financials. I like to
find a way to go to the end of the file and delete all records after that.
Reason is that I have a formula that I have used for the whole document and
may exceed the end of the document. The formula result will be "blank",
however, when printing, it goes to the end of formulated cells.
Is there a way to find the last "net income" and delete all rows after that?

Any help is appreciated.
Rich

CTRKL-END of a Statement
 

Hi there Rich,

Yes, I'm sure there is a way. Personally, I would think that getting at the
root of the problem would be easiest in the long run. It seems to me (in
what fleeting moments of sanity I have..) if we can control your formulas
and when they are entered and when they are not, you won't even have this
problem you've laid out for us. Would it be possible for you to manually
insert it everytime? Is the same formula copied down, row after row? If
so, a simple Ctrl + D will FillDown from the cell above it. Personally, I
use that all the time, daily in fact. Just saves me the trouble of doing
anything fancy. If this isn't an option, we can always use VBA.

This does bring one thing to mind, and it is very, very situation specific.
It all depends on how you structure/enter your data and how Excel see's your
data setup. In the Tools | Options | Edit (tab) menu, there is an option
for 'Edtend data range formats and formulas'. Look it up in the Help files.
Basically, Excel will enter formulas for you where the structure and formats
are the same for greater than 5 rows with the last 3 consecutive rows
unchanging. With this option checked/enabled, it can bring wonderful
results to a spreadsheet and save you time and frustration. (You can also
wow others in your office by the 'Magic Formula' that keeps appearing.
LOL!)

HTH

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)


"Rich" wrote in message
...
I have an excel file that can have multiple pages of Financials. I like to
find a way to go to the end of the file and delete all records after that.
Reason is that I have a formula that I have used for the whole document
and
may exceed the end of the document. The formula result will be "blank",
however, when printing, it goes to the end of formulated cells.
Is there a way to find the last "net income" and delete all rows after
that?

Any help is appreciated.
Rich

CTRKL-END of a Statement
 

How about an alternative?

I'm gonna use column A as a column that always has something in it if that row
should print. You can change that if your formula is in a different column.

Insert|Name|Define
Names in workbook: Sheet1!LastRow
Use this formula
Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$2000<""),ROW(Sheet1!$A$1:$A$2000) )

(Make that 2000 big enough to extend past the last possible row--but don't use
the whole column.)

Then once mo
Insert|Name|Define
Names in workbook: Sheet1!Print_Area
Use this formula
Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,5)

That last 5 represents the last column to print (A:E).

And change the worksheet (sheet1) if necessary (in all the places).

Rich wrote:

I have an excel file that can have multiple pages of Financials. I like to
find a way to go to the end of the file and delete all records after that.
Reason is that I have a formula that I have used for the whole document and
may exceed the end of the document. The formula result will be "blank",
however, when printing, it goes to the end of formulated cells.
Is there a way to find the last "net income" and delete all rows after that?

Any help is appreciated.
Rich

--

Dave Peterson