Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    last row or column with something in it (2003)

    Is there a function which returns the last cell in a worksheet which has anything in it?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: last row or column with something in it (2003)

    What exactly do you mean by that? Perhaps row 37 is the last row to contain a non-blank value, and column K the last column to contain a non-blank value, but cell K37 might still be blank.

    The following function determines the "last" row and column, and returns the intersection. But as mentioned. the intersection might be blank itself.

    Function LastCell(ws As Worksheet) As Range
    Dim LastRow As Long, LastCol As Long
    On Error Resume Next
    With ws
    LastRow = .Cells.Find(What:="*", _
    SearchDirection:=xlPrevious, _
    SearchOrder:=xlByRows).Row
    LastCol = .Cells.Find(What:="*", _
    SearchDirection:=xlPrevious, _
    SearchOrder:=xlByColumns).Column
    End With
    Set LastCell = ws.Cells(LastRow, LastCol)
    End Function

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: last row or column with something in it (2003)

    Thanks

    Your reply will do nicely. I am quite happy for it to return a blank cell.

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: last row or column with something in it (2003)

    Hans,

    I am having getting #VALUE! trying to use your code in Excel 2000.

    If Excel 2000 requires different code would you be able to give me an Excel 2000 version of that code.

    Thanks

    Peter Moran

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: last row or column with something in it (2003)

    It is a function for use in VBA, not a worksheet function. Do you want to use it in a formula? If so, what do you want to accomplish with it?

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: last row or column with something in it (2003)

    Hans,

    OK! Now I understand!

    Regards,

    Peter Moran

Posting Permissions

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