Thanks for your time and knowledge Jim.

Jim, what is the bst way to code the Range("A1:E10") would be relative, so
that the "row" range would be .Offset(1, 3) to the last contiguious cell with
data (XLDown) and the last "column" would be from .Offset(1, 3) to the last
contiguious cell with data (XLRight)?

Dennis

"Jim Rech" wrote:

It is almost always possible to write a macro that does what you want
without "selecting". It's more efficient and more professional to do so
and, in this case, it seems to have the additional benefit of working around
your problem. Here's an example:

Sub aa()
Dim MyRange As Range
Set MyRange = Cells.Find(What:="IMPACTED ACCOUNTS")
Set MyRange = MyRange.Offset(1, 3).Range("A1:E10")
MsgBox MyRange.Address ''Test - delete
End Sub

You should be able to replace your Cells.Find/Select/Set MyRange = Selection
with code similar to this.

--
Jim
"Dennis" wrote in message
...
| XL 2003
|
| The following works fine:
|
| Sub OneCellText()
|
| Dim MyRange As Range
| Dim MyCell As Range
| Dim TempVar As String
| Set MyRange = Selection
|
| For Each MyCell In MyRange
| If MyCell.Value < "" Then TempVar = TempVar + MyCell.Value +
Chr(10)
| Next MyCell
| Range("E41").Formula = TempVar
| End Sub
|
| I would like to "compute" MyRange as follows:
|
| Cells.Find(What:="IMPACTED ACCOUNTS").Activate
| ActiveCell.Offset(1, 3).Range("A1:E10").Select
|
| Where things get tough is that the W/S that I review have
| merged cells in many places. Merged cells seem to screw
| up what .Range("A1:E10") selects. (Meaning, if I un-merge the merged
cells
| then .Range("A1:E10") works perfect. But with merged the selection of
| .Range("A1:E10") picks up a different range)
|
| Also, I am not sure how to formulate the ability of the macro to select
the
| row range to include all rows from
| ActiveCell.Offset(1, 3) (above) XLDown to the first cell that is empty.
In
| addition, the Column range to move XLRight
| to the first cell that is empty.
|
| In short, how do I,
| 1)work around the merged cell vs. Offset()issue? (Note:
| I can not change the merged cells (rights issue)
|
| 2)enhance the macro to compute the range to insert into
| "MyRange" in the first macro?
|
| Thanks, Dennis