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

    Last cell (2003)

    I need to be able to find the last cell in a given range of a given worksheet in a given workbook.

    I have the following code which almost works, but not quite:

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

    My various iterations of the code either fail, only provide the answer for the whole worksheet rather than the range stipulated, or default to the active worksheet. Can someone please advise how I can get the code to work correctly?

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

    Re: Last cell (2003)

    It depends on how you specify the arguments. R should be sufficiently qualified:
    - If R is on the active sheet, Range("D7:H11") will do.
    - If R is on another sheet in the active workbook, you need to specify the sheet: Worksheets("Sheet3").Range("D7:H11")
    - If R is in another workbook, you must specify the workbook and the sheet: Workbooks("Test.xls").Worksheets("Sheet3").Range(" D7:H11")

    In fact, you don't need the wb and ws arguments, since they should be provided in the range argument. Try this version:

    Function LastCellInRange(R As Range) As Range
    Dim LastRow As Long, LastCol As Long
    On Error GoTo ErrHandler
    With R
    LastRow = .Cells.Find(What:="*", _
    SearchDirection:=xlPrevious, _
    SearchOrder:=xlByRows).Row
    LastCol = .Cells.Find(What:="*", _
    SearchDirection:=xlPrevious, _
    SearchOrder:=xlByColumns).Column
    End With
    Set LastCellInRange = R.Parent.Cells(LastRow, LastCol)
    Exit Function
    ErrHandler:
    MsgBox Err.Description, vbExclamation
    End Function

    and call it with an argument as in the examples above.

  3. #3
    4 Star Lounger
    Join Date
    Sep 2004
    Location
    Sacramento, California, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Last cell (2003)

    I have used this code to find the last cell in a selected range:

    Dim sRange As Range
    Set sRange = Selection
    sRange.Cells(sRange.Cells.Count).Select

    Carla

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

    Re: Last cell (2003)

    I guess it depends on the definition of "last cell". I think Alex was lookong for code that returns the intersection of the last "used" row and last "used" column in the specified range. Your code returns the last cell in the range, period. If that's what he needs, your code is much simpler.

  5. #5
    4 Star Lounger
    Join Date
    Sep 2004
    Location
    Sacramento, California, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Last cell (2003)

    I think given that the active cell is in the range of cells that he wants to search this would probably work as well.
    Set sRange = ActiveCell.CurrentRegion
    Carla

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

    Re: Last cell (2003)

    Again, it depends on what Alex wants. CurrentRegion might extend outside the range R specified in his original function.

  7. #7
    4 Star Lounger
    Join Date
    Sep 2004
    Location
    Sacramento, California, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Last cell (2003)

    Hans,

    On reading again, I see what you are saying the last cell with a vlaue in it, what I posted would not work to find the last cell with a value. Sometimes it is all in communication.

    Carla

    Carla

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

    Re: Last cell (2003)

    If you are looking for the last cell with data in the selection, that will not work if there are any blank rows or columns, or if there is data after the end of the selection.
    Legare Coleman

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

    Re: Last cell (2003)

    Hans

    Thanks for your code which works a treat. However I am a bit confused regarding the following

    If the coding returns a range why can't I write:
    LastCellInRange(Worksheets("Sheet1").Range("A:B")) .Activate

    Whereas I can write:
    Sub gotolastRowInCol()
    Dim i As Integer
    Dim j As Integer
    'Columns test
    i = LastCellInRange(Worksheets("Sheet1").Range("A:B")) .Row
    j = LastCellInRange(Worksheets("Sheet1").Range("A:B")) .Column
    Worksheets("Sheet1").cells(i,j).Activate
    'Rows test
    i = LastCellInRange(Worksheets(1).Range("26:27")).Row
    j = LastCellInRange(Worksheets(1).Range("26:27")).Colu mn
    Worksheets("Sheet1").cells(i,j).Activate
    End Sub

    With regard to the comment as to what the last cell in a range should constitute, I agree one does need to take a bit of care when using the function, but I find it works well for iterating through lines of data held in disparate workbooks for which I tend to use a version of:
    for i = 1 to LastCellInRange(Worksheets("Sheet1").Range("A:B")) .Row

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

    Re: Last cell (2003)

    What problem do you have with activating the result of LastCellInRange?

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

    Re: Last cell (2003)

    Unless the worksheet specified in the argument is made active before I run the function I get the "Run time error 1004" message with "Active metthod of range class failed"

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Last cell (2003)

    Try using:
    LastCellInRange(Worksheets("Sheet1").Range("A:B")) .Parent.Activate
    LastCellInRange(Worksheets("Sheet1").Range("A:B")) .Activate

    You are not able to activate a range on the sheet unless the sheet is active

    Or instead of "activate", use Goto:
    Application.Goto LastCellInRange(Worksheets("Sheet1").Range("A:B"))

    Steve

Posting Permissions

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