Last Row with Content - As a row
 

How would I select (and I do mean actually select the row) the last row
that has data in it?

I need to be able to say:

LastRow.select in essence and then do whatever I need with that row.
Thanks!
Steve

Last Row with Content - As a row
 

Cells(Rows.Count,"A").End(xlUp).Entirerow.Select

based on the contents of column A.
If you want to search on multiple columns, it's a bit more complicated.

HTH
--
AP

"nemadrias" a écrit dans le message de news:
...
How would I select (and I do mean actually select the row) the last row
that has data in it?

I need to be able to say:

LastRow.select in essence and then do whatever I need with that row.
Thanks!
Steve

Last Row with Content - As a row
 

nemadrias wrote:

I need to be able to say:
LastRow.select in essence and then do whatever I need with that row.


Hi Steve

Cells.SpecialCells(xlCellTypeLastCell).Select

will select the last cell in the last row

Regards

Steve

Last Row with Content - As a row
 

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


Scoops wrote:
nemadrias wrote:

I need to be able to say:
LastRow.select in essence and then do whatever I need with that row.


Hi Steve

Cells.SpecialCells(xlCellTypeLastCell).Select

will select the last cell in the last row

Regards

Steve

Last Row with Content - As a row
 

Sorry Steve

Must. Slow. Down. And. Read.

Cells.SpecialCells(xlCellTypeLastCell).EntireRow.S elect

Regards

Steve

Last Row with Content - As a row
 

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

Last Row with Content - As a row
 

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

Last Row with Content - As a row
 

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

Last Row with Content - As a row
 

nemadrias wrote:
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

Jim Thomlinson wrote:
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

Hi Steve

Jim is searching the entire sheet with "wks.Cells" where he's specified
that wks is the active sheet.

If you want to search a specific range, replace the "Cells" with your
range e.g. wks.Range("K10:K100").

In the same example you'll also need to replace
"After:=wks.Range("A1")" with After:=wks.Range("K10").

Regards

Steve