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

  • Thread starter Thread starter Casey
  • Start date Start date
C

Casey

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

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
 
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.
 
Back
Top