Excel - How to ignore rows with formulas that equate to nothing?

I'm working with Microsft Office Interop API. My question pertains to Ex开发者_开发百科cel.

I wrote a program using Interop API to format Excel documents and then send them to the printer. The problem though is that we occasionally run across files in which people have created a column that spans 65,000+ rows, and all it contains is a formula. The formula only creates output for the first 100 or so rows, and then the remaining appear blank. But, when sent to the printer, it tries to print like 10,000 pages or something because of those formulas.

I first looked for ways to tell Excel to simply not print those ranges. I couldn't find any setting to ignore those. Second, I thought about ways to do this programatically, but that logic would be pretty round-about and might cause issues with "regular" files that don't contain formulas like this.

Does anyone know of a good way to have Excel ignore these formulas that equate to nothing?

I'm using Delphi and Office 2007 Interop API.

---

I don't know Delphi, but I created a macro to get the last active column by using

With ActiveSheet
    row = .Cells(.Rows.Count, "B").End(xlUp).row
End With

Then with having the row you could try to print the columns you need.

ActiveSheet.Range("A1:" & "D" row).Select
Selection.PrintOut Copies:=1, Collate:=True

Hope that helps!

---

Nope. As far as Excel is concerned a cell is filled if it contains a formula, regardless of the result of that formula.

Typing [end] and then [down] in a cell with a value takes you to the last filled cell before the first blank one in that column starting from your current position. Typing End-Down in the columns with the formulas should therefore take you all the way down to the bottom.

The option you do have is to tell Excel exactly what range to print before issuing the print command. You do that by using the method that corresponds to the Print | Set Range menu option (sorry, don't have the com interfaces available at the moment).

To find the range to print you need the right most column and the bottom most row of your data range. Assuming your first row containes column captions, simply iterate over the cell values of the first row and stop when you get a blank value.

To find the bottom most row of your data range, you need a column that doesn't have formulas, but "normal" values, and has no blank values in any of the rows within your data range. If your data always contains such a column, you can iterate over that to find the last filled row and thus the bottom bound of your print range.