Jim had some good code here - I'm wondering if I can modify the range
so that this code is ONLY executed on a certain range of cells in the
worksheet. For example, range(K10:K100) would bring back the last
populated cell OF THAT RANGE...Can anyone lend some modification
expertise? Thanks SO much.
Steve


Jim Thomlinson wrote:
Keep in mind that Scoops code is not foolproof. The last cell
(xlCellTypeLastCell) is based on the used range which will not be what you
think it is in every instance. Sometimes it will encompass blank cells beyond
the last populated cell. (have you ever had a spreadsheet where the scroll
bar allowed you to scroll well past the last populated cell all the way to
row 65,536?) The code will work in most circumstances but sometimes it will
act in ways you do not expect. Here is some foolproof code.

sub DoStuffToLast()
LastCell.offset(1,0).entirerow.select
end sub

Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)
End Function

--
HTH...

Jim Thomlinson


"Scoops" wrote:


nemadrias wrote:
Thanks to both - and finally how would I then insert a row after either
of your code? Thanks - sorry dumb newbie q's...

Hi Steve

You wouldn't as your already at the last row but

Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0).EntireRow.Select

will select the next row down.

Regards

Steve