Jim, below is corrected for a description error.

************************************************** ****

Sub Test4()
Dim LastDataColumn As Integer
Dim LastDataRow As Integer

LastDataColumn = 5 'First Column with meaningful data
' the above number should be the column number of
' Cells.Find(What:="IMPACTED ACCOUNTS").Offset(1,3)
'
Do While Not IsEmpty(Rows(34).Cells(LastDataColumn))
' Rows(34) above should be the row number of
' Cells.Find(What:="IMPACTED ACCOUNTS").Offset(1,3)
'
LastDataColumn = LastDataColumn + 1
Loop
LastDataColumn = LastDataColumn - 1

LastDataRow = 34 'First Row with meaningful data
' the above number should be the Row number of
' Cells.Find(What:="IMPACTED ACCOUNTS").Offset(1,3)
'
Do While Not IsEmpty(Columns(5).Cells(LastDataRow))
' Columns(5) above should be the row number of
' Cells.Find(What:="IMPACTED ACCOUNTS").Offset(1,3)
'
LastDataColumn = LastDataRow + 1
Loop
LastDataRow = LastDataRow - 1
Range("A1").Select
End Sub

The above give me the LastDataColumn and the LastDataRow, or, the bottom
right of my range
Cells.Find(What:="IMPACTED ACCOUNTS").Offset(1,3) should give me the
upper left of my range

How do I get these parameters to place into your "line"
Set MyRange = MyRange.End(xlDown).End(xlToRight).Offset(1, 3)
effectively MyRange.MyLastRow.MyLastColumn.Offset(1, 3)

And how do I get the initial start point integers for:
LastDataColumn (Initially the first data Column)
LastDataRow (Initially the first data Row)
out of "Cells.Find(What:="IMPACTED ACCOUNTS").Offset(1,3)" ??


Dennis
'************************************************* ******

"Jim Rech" wrote:

If I understand what you're asking perhaps...

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

--
Jim
"Dennis" wrote in message
...
| 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
|
|
|