Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Zoetermeer, Zuid-Holland, Netherlands
    Posts
    559
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Last cell containing value (Win98 / Excel97)

    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.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Last cell containing value (Win98 / Excel97)

    <P ID="edit" class=small>(Edited by JohnBF on 01-Sep-04 08:36. )</P>Worksheets("mysheet").SpecialCells(xlLastCell).O ffset(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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Last cell containing value (Win98 / Excel97)

    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:

    <pre>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
    </pre>

    Legare Coleman

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Last cell containing value (Win98 / Excel97)

    <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>

    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(i ntCol)) < ActiveSheet.Rows.Count Then Exit For
    Next intCol
    For lngRow = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
    If Application.WorksheetFunction.CountBlank(Rows(lngR ow)) < ActiveSheet.Columns.Count Then Exit For
    Next lngRow
    lastusedcell = ActiveSheet.Cells(lngRow, intCol).Address
    End Function
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Last cell containing value (Win98 / Excel97)

    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

  6. #6
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Zoetermeer, Zuid-Holland, Netherlands
    Posts
    559
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Last cell containing value (Win98 / Excel97)

    John, Legare, Jim <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    You surely taught me a few things!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •