Hi,
I have a named range of cells D19:D1000 (EntryAreaProposal). There i
data above this range and below this range. I want to find the las
used row within this range and of course if I use something like:

LastRow = wks2.Cells(Rows.Count, "D").End(xlUp).Row

it hits the data below my range and give me "that" last row instead o
what I need. I know this has to be simple, but it's worn me out tryin
to find something in the archives.
Help please

--
Case

-----------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...nfo&userid=454
View this thread: http://www.excelforum.com/showthread.php?threadid=51700

Give this a try...

Sub LastCell()
Dim rng As Range

With Range("EntryAreaProposal")
Set rng = Cells(.Item(1).Row + .Rows.Count,
..Column).End(xlUp).Offset(1, 0)
End With
MsgBox rng.Address
End Sub
--
HTH...

Jim Thomlinson


"Casey" wrote:


Hi,
I have a named range of cells D19:D1000 (EntryAreaProposal). There is
data above this range and below this range. I want to find the last
used row within this range and of course if I use something like:

LastRow = wks2.Cells(Rows.Count, "D").End(xlUp).Row

it hits the data below my range and give me "that" last row instead of
what I need. I know this has to be simple, but it's worn me out trying
to find something in the archives.
Help please.


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=517002

How about:


Sub Macro1()
Dim s As Range
Dim i As Long
For i = 1000 To 19 Step -1
If IsEmpty(Cells(i, "D")) Then
Else
LastRow = i
Exit For
End If
Next
MsgBox (i)
End Sub
--
Gary''s Student


"Casey" wrote:


Hi,
I have a named range of cells D19:D1000 (EntryAreaProposal). There is
data above this range and below this range. I want to find the last
used row within this range and of course if I use something like:

LastRow = wks2.Cells(Rows.Count, "D").End(xlUp).Row

it hits the data below my range and give me "that" last row instead of
what I need. I know this has to be simple, but it's worn me out trying
to find something in the archives.
Help please.


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=517002

Jim and Gary's Student,
Thank you both for the bits of code. I'm going to try Jim's first
because it makes use of the Named Range. But I really appreciate the
reply from both of you. I have gleened numerous lessons, just reading
all the great posts you guys contribute. Thanks.


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=517002

Casey,

A few suggestions:

lastRow = Range("D19").End(xlDown).Row
If lastRow 1000 Then lastRow = 19

or

lastRow = Range("D1001").End(xlUp).Row
If lastRow 19 Then lastRow = 19

or

lastRow = 18 + Application.CountA(Range("D19:D1000"))


HTH

Tim

"Casey" wrote in
message ...

Hi,
I have a named range of cells D19:D1000 (EntryAreaProposal). There is
data above this range and below this range. I want to find the last
used row within this range and of course if I use something like:

LastRow = wks2.Cells(Rows.Count, "D").End(xlUp).Row

it hits the data below my range and give me "that" last row instead of
what I need. I know this has to be simple, but it's worn me out trying
to find something in the archives.
Help please.


--
Casey


------------------------------------------------------------------------
Casey's Profile:
http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=517002

Tim,
Thank you for your reply to my post. I found a fit with the code that
Jim posted, however, I appreciate the input and between the three
replies I have some great code to work into other areas. I am amazed at
the diversity of ways the same task can be accomplished. Thanks again.


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=517002