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

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

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

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

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

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

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

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

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

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

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

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