ExcelBanter - used range between two cells...

Hi Shishi,

Depending on how you define the 'used range', try:

Sub TestIt()

Dim startCell As Range
Dim endCell As Range
Dim firstRow As Long
Dim lastCol As Long
Dim firstNewRow As Long, firstNewCol As Long
Dim lastNewRow As Long, lastNewCol As Long
Dim searchRng As Range
Dim MyUsedRng As Range

Set startCell = Range("C3") '<<======== CHANGE
Set endCell = Range("J21") '<<======== CHANGE

firstRow = startCell.Row + 1
lastCol = endCell.Column

Set searchRng = Range(Cells(firstRow, "A"), _
endCell.Offset(-1))

firstNewRow = searchRng.Find(what:="*", _
After:=searchRng(1), _
SearchOrder:=xlByRows, _
searchdirection:=xlNext).Row

firstNewCol = searchRng. _
Find(what:="*", _
After:=searchRng(1), _
SearchOrder:=xlByColumns, _
searchdirection:=xlNext).Column

lastNewRow = searchRng. _
Find(what:="*", _
After:=searchRng(1), _
SearchOrder:=xlByRows, _
searchdirection:=xlPrevious).Row

lastNewCol = searchRng. _
Find(what:="*", _
After:=searchRng(1), _
SearchOrder:=xlByColumns, _
searchdirection:=xlPrevious).Column

Set MyUsedRng = Range(Cells(firstNewRow, firstNewCol), _
Cells(lastNewRow, lastNewCol))

MyUsedRng.Select

MsgBox MyUsedRng.Address

End Sub

You can amend the definition of the 'used range' by changing the values for:

firstRow = startCell.Row + 1
lastCol = endCell.Column

---
Regards,
Norman

"shishi" wrote in message
oups.com...
Hi all,

I am trying to find the range(or may the used range) between two
cells. the situation is as below.

--------------
| First cell |
---------------
The first cell above spans only one colum. Everything that is below
this cell spans over multiple number of columns. The Last cell is also
spans only one column as below.

---------------
| Last Cell |
---------------

Now I am trying to get the used range between these two cells, given
that I have the address for the first cell and the last cell. Thanks in
advance for your help...

Shishi