I'm stumped on the fastest/cleanest way to do the following:

After copying in an entire worksheet from a source workbook into my
open workbook in vba, I would like to name a range of cells on that
worksheet just copied in.

I know that the range I want to name will _begin_ just below a cell
with the keyword Ticker in it (that Ticker keyword cell is currently
A21). Where the range ends will always be variable.

I don't know how to name a range in VB and I don't know how to find the
last cell that has data. In this example the last cell is FK136 and
then there are some blank cells and then in FK140 there is a shaded
cell where there will somtimes be data. But whether it is row 140 or
220 or 180 it will always be different. I can count on column FK right
now, but in the future, who knows.

Any suggestions?

Sub SubFindIt()
Dim RealLastRow As Long
Dim RealLastColumn As Long
Dim rng as Range, rng1 as Range
dim rng2 as Range, sStr as String

sStr = "ticker"
Set rng = Cells.Find(What:=sStr, _
After:=Range("IV65536"), _
LookIn:=xlConstatns, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then

On Error Resume Next
RealLastRow = _
Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
RealLastColumn = _
Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
set rng1 = Cells(RealLastRow, RealLastColumn)
On Error goto 0
if not rng1 is nothing then
set rng2 = Range(rng,rng1)
rng2.Name = "MyData"
End if
else
msgbox "Ticker not found"
End If
End Sub

--
Regards,
Tom Ogilvy

"nycjdc" wrote:


I'm stumped on the fastest/cleanest way to do the following:

After copying in an entire worksheet from a source workbook into my
open workbook in vba, I would like to name a range of cells on that
worksheet just copied in.

I know that the range I want to name will _begin_ just below a cell
with the keyword Ticker in it (that Ticker keyword cell is currently
A21). Where the range ends will always be variable.

I don't know how to name a range in VB and I don't know how to find the
last cell that has data. In this example the last cell is FK136 and
then there are some blank cells and then in FK140 there is a shaded
cell where there will somtimes be data. But whether it is row 140 or
220 or 180 it will always be different. I can count on column FK right
now, but in the future, who knows.

Any suggestions?