Last cell containing value (Win98 / Excel97)

Topic

New Reply

I’m familiar with Word objectmodel and VBA, but not at all with Excel. Using VBA, how can I get the coordinates of the last cell (row/column) that contains a value (is not empty)?
A client receives worksheets that contain a varying number of rows and sometimes 1 or 2 extra columns and she wants to put the Sum() of the last column two rows below the last cell with a value in that column.

Reply | Quote

Replies

(Edited by JohnBF on 01-Sep-04 08:36. )

Worksheets(“mysheet”).SpecialCells(xlLastCell).Offset(2, 0).FormulaR1C1 = “=SUM(R[*]C[*]:R[*]C[*])”

where for “*” you will need to supply the coordinates for the beginning and ending range for the sum, should get you started.

Reply | Quote

John: That method can return unexpected results on at least some versions of Excel (XL2K which I use for example). If rows and/or columns have been deleted from the worksheet, and the workbook has not been saved, then your method can return a cell that is much below and/or to the right of the actual last used cell. The VBA function below should be more reliable:

Public Function FindLastCell(strWorksheet As String) As Range
Dim oUsed As Range
Dim I As Long, lLastRow As Long, lLastCol As Long, lLast As Long
    Set oUsed = Worksheets(strWorksheet).UsedRange
    lLastCol = 0
    For I = 0 To oUsed.Rows.Count - 1
        lLast = oUsed.Offset(I, oUsed.Columns.Count + 1).End(xlToLeft).Column
        If lLast > lLastCol Then
            lLastCol = lLast
        End If
    Next I
    lLastRow = 0
    For I = 0 To oUsed.Rows.Count - 1
        lLast = oUsed.Offset(oUsed.Rows.Count + 1, I).End(xlUp).Column
        If lLast > lLastRow Then
            lLastRow = lLast
        End If
    Next I
    Set FindLastCell = Range("A1").Offset(lLastRow - 1, lLastCol - 1)
End Function

Reply | Quote

Function lastusedcell() As String
Dim intCol As Integer
Dim lngRow As Long
For intCol = ActiveSheet.UsedRange.Columns.Count To 1 Step -1
If Application.WorksheetFunction.CountBlank(Columns(intCol)) < ActiveSheet.Rows.Count Then Exit For
Next intCol
For lngRow = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountBlank(Rows(lngRow)) < ActiveSheet.Columns.Count Then Exit For
Next lngRow
lastusedcell = ActiveSheet.Cells(lngRow, intCol).Address
End Function

Reply | Quote

Function lastusedcell() As String
Dim intCol As Integer
Dim lngRow As Long
For intCol = ActiveSheet.UsedRange.Columns.Count To 1 Step -1
If Application.WorksheetFunction.CountBlank(Columns(intCol)) < ActiveSheet.Rows.Count Then Exit For
Next intCol
For lngRow = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountBlank(Rows(lngRow)) < ActiveSheet.Columns.Count Then Exit For
Next lngRow
lastusedcell = ActiveSheet.Cells(lngRow, intCol).Address
End Function

Reply | Quote

John: That method can return unexpected results on at least some versions of Excel (XL2K which I use for example). If rows and/or columns have been deleted from the worksheet, and the workbook has not been saved, then your method can return a cell that is much below and/or to the right of the actual last used cell. The VBA function below should be more reliable:

Public Function FindLastCell(strWorksheet As String) As Range
Dim oUsed As Range
Dim I As Long, lLastRow As Long, lLastCol As Long, lLast As Long
    Set oUsed = Worksheets(strWorksheet).UsedRange
    lLastCol = 0
    For I = 0 To oUsed.Rows.Count - 1
        lLast = oUsed.Offset(I, oUsed.Columns.Count + 1).End(xlToLeft).Column
        If lLast > lLastCol Then
            lLastCol = lLast
        End If
    Next I
    lLastRow = 0
    For I = 0 To oUsed.Rows.Count - 1
        lLast = oUsed.Offset(oUsed.Rows.Count + 1, I).End(xlUp).Column
        If lLast > lLastRow Then
            lLastRow = lLast
        End If
    Next I
    Set FindLastCell = Range("A1").Offset(lLastRow - 1, lLastCol - 1)
End Function

Reply | Quote

(Edited by JohnBF on 01-Sep-04 08:36. )

Worksheets(“mysheet”).SpecialCells(xlLastCell).Offset(2, 0).FormulaR1C1 = “=SUM(R[*]C[*]:R[*]C[*])”

where for “*” you will need to supply the coordinates for the beginning and ending range for the sum, should get you started.

Reply | Quote

There is no need to run a loop…

‘=============================
‘Function BottomRightCorner
‘Jim Cone – San Francisco, CA
‘=============================
Function BottomRightCorner(ByRef objSheet As Worksheet) As Range
On Error GoTo NoCorner
Dim BottomRow As Long
Dim LastColumn As Long

If objSheet.FilterMode Then objSheet.ShowAllData

BottomRow = objSheet.Cells.Find(what:=”*”, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastColumn = objSheet.Cells.Find(what:=”*”, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Set BottomRightCorner = objSheet.Cells(BottomRow, LastColumn)

Exit Function

NoCorner:
beep
Set BottomRightCorner = objSheet.Cells(1, 1)
End Function

Reply | Quote

John, Legare, Jim
You surely taught me a few things!

Reply | Quote

John, Legare, Jim
You surely taught me a few things!

Reply | Quote

There is no need to run a loop…

‘=============================
‘Function BottomRightCorner
‘Jim Cone – San Francisco, CA
‘=============================
Function BottomRightCorner(ByRef objSheet As Worksheet) As Range
On Error GoTo NoCorner
Dim BottomRow As Long
Dim LastColumn As Long

If objSheet.FilterMode Then objSheet.ShowAllData

BottomRow = objSheet.Cells.Find(what:=”*”, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastColumn = objSheet.Cells.Find(what:=”*”, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Set BottomRightCorner = objSheet.Cells(BottomRow, LastColumn)

Exit Function

NoCorner:
beep
Set BottomRightCorner = objSheet.Cells(1, 1)
End Function

Reply | Quote