Last Populated Row in Range

[BBM]

Hi All,
Does anybody know how to find the last populated row in a range of cells?:

A B C D E F
1 x x x x x x
2 x x x x
3 x x x x
4 x x x x
5 x x x
6
i.e. for the above, where x signifies a non empty cell:
Function GetLastPopulatedRow( Range("A1:F6" ) = 5

Many Thanks.
BBM

[Bob Phillips]

[vba]

Function GetLastPopulatedRow(ByRef rng As Range)
GetLastPopulatedRow =rng.CurrentRegion.Rows.Count - rng.Row + 1
End Function
[/vba]

[mdmackillop]

I thought this would work for any location, but it fails in Excel 2000 (as does yours) Any thought why, Bob?

[VBA]
Function GetLastPopulatedRow(ByRef rng As Range)
Dim r As Range
Set r = rng.CurrentRegion
MsgBox "Range - " & r.Address
MsgBox "First row - " & r(1).Row
MsgBox " Row Count - " & r.Rows.Count
GetLastPopulatedRow = r(1).Row + r.Rows.Count
End Function
[/VBA]

[Kenneth Hobs]

In this, I find the last non-blank absolute last row or column in a range.
[VBA]'=LastNBRow(A3:G10)
Function LastNBRow(rng As Range) As Long
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = rng.Find(What:="*", After:=rng.Cells(rng.Rows.Count, rng.Columns.Count), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End If
LastNBRow = LastRow
End Function

'=LastNBCol(A3:G10)
Function LastNBCol(rng As Range) As Long
Dim LastColumn As Integer
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Columns.
LastColumn = rng.Find(What:="*", After:=rng.Cells(rng.Rows.Count, rng.Columns.Count), _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
End If
LastNBCol = LastColumn
End Function[/VBA]

[Bob Phillips]

I thought this would work for any location, but it fails in Excel 2000 (as does yours) Any thought why, Bob?

[VBA]
Function GetLastPopulatedRow(ByRef rng As Range)
Dim r As Range
Set r = rng.CurrentRegion
MsgBox "Range - " & r.Address
MsgBox "First row - " & r(1).Row
MsgBox " Row Count - " & r.Rows.Count
GetLastPopulatedRow = r(1).Row + r.Rows.Count
End Function
[/VBA]

I think it is just logic errors Malcolm.

You needed to subtract 1 else the first row gets double-counted, mine was adding where it should subtract and vice versa.

[mdmackillop]

It's not that. With 1 or more cells selected in a block
[VBA] GetLastPopulatedRow = rng.CurrentRegion.Rows.Count
[/VBA]returns a value = selection.rows.count
CurrentRegion is ignored.

[Bob Phillips]

Not for me Malcolm, I just selected B4:C5, last data was in B6, and I ran

?GetLastPopulatedRow(range("A1:F10"))

and it returned 6.

[mdmackillop]

With A1:E14 populated, I tried this.
The sub returns 70 in both cells. =Test2(D6) returns 1

[VBA]
Sub Test()
ActiveCell = ActiveCell.Offset(, -5).CurrentRegion.Cells.Count
ActiveCell.Offset(1) = Test2(ActiveCell.Offset(, -5))
End Sub

Function Test2(ByRef rng As Range)
Test2 = rng.CurrentRegion.Cells.Count
End Function
[/VBA]