PDA

View Full Version : Last Populated Row in Range



BBM
12-10-2008, 07:20 AM
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
12-10-2008, 07:42 AM
Function GetLastPopulatedRow(ByRef rng As Range)
GetLastPopulatedRow =rng.CurrentRegion.Rows.Count - rng.Row + 1
End Function

mdmackillop
12-10-2008, 09:10 AM
I thought this would work for any location, but it fails in Excel 2000 (as does yours) Any thought why, Bob?


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

Kenneth Hobs
12-10-2008, 10:03 AM
In this, I find the last non-blank absolute last row or column in a range.
'=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

Bob Phillips
12-10-2008, 10:55 AM
I thought this would work for any location, but it fails in Excel 2000 (as does yours) Any thought why, Bob?


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


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
12-10-2008, 11:00 AM
It's not that. With 1 or more cells selected in a block
GetLastPopulatedRow = rng.CurrentRegion.Rows.Count
returns a value = selection.rows.count
CurrentRegion is ignored.

Bob Phillips
12-10-2008, 11:11 AM
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
12-10-2008, 11:24 AM
:dunno
With A1:E14 populated, I tried this.
The sub returns 70 in both cells. =Test2(D6) returns 1


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