[Lyn]

I have a number of rows hidden on a spreadsheet and I do not want them
included when I do a mail merge; is there a way to exclude them other than
"deselecting" them when the Mail Merge Receipients box pops up?

[Peter Jamieson]

I don't think there's a way to select rows solely on the basis of
whether they are hidden or not.

If it makes sense to introduce another column called "Hidden" (say) and
mark hidden entries as y (or perhaps), then you can at least select
using the dropdown or query options within Word's Edit Recipients dialog
box, rather than having to select each record individually. But then you
would have to maintain the values in that column in Excel...

....unless you can define a user-defined worksheet function using Excel
VBA to detect whether the row is hidden.

e.g., in a new Module of the Workbook, put the following function code:

Function isHidden() As Variant
Application.Volatile (True)
isHidden = Abs(CInt(Application.Caller.EntireRow.HIDDEN))
End Function

Then put the formula

=isHidden()

in the first data cell of the Hidden column, and copy the formula into
all the cells in the column

Then as you hide/unhide rows, the value should change - it should be 1
for hidden rows and 0 for visible rows. Since of course you cannot
actually see the value in hidden rows, you might want to test that it is
behaving as you want - e.g. if the Hidden column is column F and row 5
is hidden, a cell containing a formula such as =F5 should have value 1

And even then, you still have to make sure that every cell in that
column has the =isHidden() formula in it, e.g. when you add rows to your
sheet. Also, be aware that using Application.Volatile can slow down
sheet recalculation.


Peter Jamieson

http://tips.pjmsn.me.uk

On 10/11/2009 17:56, Lyn wrote:
I have a number of rows hidden on a spreadsheet and I do not want them
included when I do a mail merge; is there a way to exclude them other than
"deselecting" them when the Mail Merge Receipients box pops up?