ansaurus

Question

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

Answer 1

+4 A:

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.

Marjan Venema 2010-09-28 19:12:09

Programmatically, though, that logic is difficult to safely apply to "every" Excel file. The program I've written is intended to run somewhat on its own without any user defined print range, etc. It seems a little unreliable to iterate over every row and check the column cells for formulas, and then come up with an algorithm that allows for some degree of blank cells in between itself and future cells being checked. It's ... just ... argggh. :(

Ryan 2010-09-29 16:27:14

@Ryan: yeah, I know the feeling... :} Oh, to be a bit more general, you could also check all columns with "captions" (value in first row) and stop at the row where the only filled cells are ones containing formulas... At least formulas always start with a "=".

Marjan Venema 2010-09-29 16:47:50

@Marjan: yeah, that might be the best option. loop through each column in the given "used range" and count the number of trailing rows that contain only formulas. if that count becomes more than like 100, or so, begin removing the remaining rows from that column. This is only tricky because 100 rows of formulas isn't bad, neither is 5,000 rows of formulas. But, it's bad if those formulas are all blank!

Ryan 2010-09-29 16:54:44

Answer 2

+3 A:

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!

zLan 2010-09-28 19:42:42

I'm not familiar with that 'with' syntax, so it's a little confusing to me, but I don't understand how that first code snippet gets the last active column. It looks to me like it's getting the last row with a hardcoded column for "B", or "D"? Does this logic ignore cells containing formulas that have no visible result?

Ryan 2010-09-29 16:30:55

@Ryan: the syntax is VB. No it won't ignore cells containing formulas that have no visible result. The macro is basically doing the End-Down, or in this case End-Up "trick" to find the next filled cell (after a couple of blank ones) starting from the last row going in the xlUp direction.

Marjan Venema 2010-09-29 16:56:13

@Ryan: The first snippet is returning the row number of the last active cell in the column "B" by counting up from the bottom of the Excel sheet. Unfortunately I don't know Delphi otherwise I would give a better example.

zLan 2010-09-29 18:09:50

ansaurus

tags:

views:

answers:

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

related questions