Find last used row in Range with Data above and below.

[Casey]

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.

[Jim Thomlinson]

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
>
>

[Gary''s Student]

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
>
>

[Casey]

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.

[Tim Barlow]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Casey]

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.