+ Reply to Thread
Results 1 to 9 of 9

.Cells.SpecialCells(xlLastCell)

  1. #1
    Simon Shaw
    Guest

    .Cells.SpecialCells(xlLastCell)

    using the code ActiveSheet.Cells.SpecialCells(xlLastCell) normally works
    fine. But, sometimes after working on a sheet for a while, the code returns
    the address to cell IV65536 - the last cell in the sheet. I have tried Clear
    => All from the unused columns and rows, deleting the unused columns and
    rows, but once the sheet thinks IV65536 is the last cell, it is stuck. how
    can I clear up the sheet, so it correctly calculates the last cell?

  2. #2
    Jim Thomlinson
    Guest

    RE: .Cells.SpecialCells(xlLastCell)

    Last cell is not updated until you save. Deleting the rows and columns will
    fix the problem, but not until you save the spreadsheet. That is the problem
    with using last cell. For that reason you are better off using either
    ..usedrange or .end

    HTH

    "Simon Shaw" wrote:

    > using the code ActiveSheet.Cells.SpecialCells(xlLastCell) normally works
    > fine. But, sometimes after working on a sheet for a while, the code returns
    > the address to cell IV65536 - the last cell in the sheet. I have tried Clear
    > => All from the unused columns and rows, deleting the unused columns and
    > rows, but once the sheet thinks IV65536 is the last cell, it is stuck. how
    > can I clear up the sheet, so it correctly calculates the last cell?


  3. #3
    indiana1138
    Guest

    Re: .Cells.SpecialCells(xlLastCell)

    Hello Simon,

    Other folks have found that Excel does not work as advertised in this
    respect. Try this code for something that might work:

    Function LastCell(Optional ws As Worksheet) As Range
    If ws Is Nothing Then Set ws = ActiveSheet
    Set Rng = ws.Cells
    Set LastCell = Rng(1)
    On Error Resume Next
    Set LastCell = Intersect( _
    Rng.Find("*", Rng(1), xlValues, xlWhole, xlByRows, _
    xlPrevious).EntireRow, _
    Rng.Find("*", Rng(1), xlValues, xlWhole, xlByColumns, _
    xlPrevious).EntireColumn)
    End Function

    Hope this helps,

    Bob


  4. #4
    Bob Phillips
    Guest

    Re: .Cells.SpecialCells(xlLastCell)

    Usedrange also suffers that problem Jim.

    To be safe, it is best to reset as described in
    http://www.contextures.com/xlfaqApp.html#Unused

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Jim Thomlinson" <[email protected]> wrote in message
    news:[email protected]...
    > Last cell is not updated until you save. Deleting the rows and columns

    will
    > fix the problem, but not until you save the spreadsheet. That is the

    problem
    > with using last cell. For that reason you are better off using either
    > .usedrange or .end
    >
    > HTH
    >
    > "Simon Shaw" wrote:
    >
    > > using the code ActiveSheet.Cells.SpecialCells(xlLastCell) normally works
    > > fine. But, sometimes after working on a sheet for a while, the code

    returns
    > > the address to cell IV65536 - the last cell in the sheet. I have tried

    Clear
    > > => All from the unused columns and rows, deleting the unused columns and
    > > rows, but once the sheet thinks IV65536 is the last cell, it is stuck.

    how
    > > can I clear up the sheet, so it correctly calculates the last cell?




  5. #5
    Jim Thomlinson
    Guest

    Re: .Cells.SpecialCells(xlLastCell)

    Thanks Bob I will remenber that. Can you tell I don't use used range a lot.

    "Bob Phillips" wrote:

    > Usedrange also suffers that problem Jim.
    >
    > To be safe, it is best to reset as described in
    > http://www.contextures.com/xlfaqApp.html#Unused
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "Jim Thomlinson" <[email protected]> wrote in message
    > news:[email protected]...
    > > Last cell is not updated until you save. Deleting the rows and columns

    > will
    > > fix the problem, but not until you save the spreadsheet. That is the

    > problem
    > > with using last cell. For that reason you are better off using either
    > > .usedrange or .end
    > >
    > > HTH
    > >
    > > "Simon Shaw" wrote:
    > >
    > > > using the code ActiveSheet.Cells.SpecialCells(xlLastCell) normally works
    > > > fine. But, sometimes after working on a sheet for a while, the code

    > returns
    > > > the address to cell IV65536 - the last cell in the sheet. I have tried

    > Clear
    > > > => All from the unused columns and rows, deleting the unused columns and
    > > > rows, but once the sheet thinks IV65536 is the last cell, it is stuck.

    > how
    > > > can I clear up the sheet, so it correctly calculates the last cell?

    >
    >
    >


  6. #6
    Bob Phillips
    Guest

    Re: .Cells.SpecialCells(xlLastCell)

    Me neither, usually only in responses here <bg>

    Bob


    "Jim Thomlinson" <[email protected]> wrote in message
    news:[email protected]...
    > Thanks Bob I will remenber that. Can you tell I don't use used range a

    lot.
    >
    > "Bob Phillips" wrote:
    >
    > > Usedrange also suffers that problem Jim.
    > >
    > > To be safe, it is best to reset as described in
    > > http://www.contextures.com/xlfaqApp.html#Unused
    > >
    > > --
    > >
    > > HTH
    > >
    > > RP
    > > (remove nothere from the email address if mailing direct)
    > >
    > >
    > > "Jim Thomlinson" <[email protected]> wrote in

    message
    > > news:[email protected]...
    > > > Last cell is not updated until you save. Deleting the rows and columns

    > > will
    > > > fix the problem, but not until you save the spreadsheet. That is the

    > > problem
    > > > with using last cell. For that reason you are better off using either
    > > > .usedrange or .end
    > > >
    > > > HTH
    > > >
    > > > "Simon Shaw" wrote:
    > > >
    > > > > using the code ActiveSheet.Cells.SpecialCells(xlLastCell) normally

    works
    > > > > fine. But, sometimes after working on a sheet for a while, the code

    > > returns
    > > > > the address to cell IV65536 - the last cell in the sheet. I have

    tried
    > > Clear
    > > > > => All from the unused columns and rows, deleting the unused columns

    and
    > > > > rows, but once the sheet thinks IV65536 is the last cell, it is

    stuck.
    > > how
    > > > > can I clear up the sheet, so it correctly calculates the last cell?

    > >
    > >
    > >




  7. #7

    Re: .Cells.SpecialCells(xlLastCell)

    Alright.
    We dont use last cell as well as usedrange.
    Then how do we find out the actual last cell?
    Kindly post the solution so that we can use the better function then
    these sloppy ones.

    Thanks.
    Nathpai.


  8. #8
    CyndyG
    Guest

    Re: .Cells.SpecialCells(xlLastCell)

    How could I use this code when the last cell of the data contains character
    127(the end of file character) and all the data is in column A,there is only
    1 worksheet.

    "indiana1138" wrote:

    > Hello Simon,
    >
    > Other folks have found that Excel does not work as advertised in this
    > respect. Try this code for something that might work:
    >
    > Function LastCell(Optional ws As Worksheet) As Range
    > If ws Is Nothing Then Set ws = ActiveSheet
    > Set Rng = ws.Cells
    > Set LastCell = Rng(1)
    > On Error Resume Next
    > Set LastCell = Intersect( _
    > Rng.Find("*", Rng(1), xlValues, xlWhole, xlByRows, _
    > xlPrevious).EntireRow, _
    > Rng.Find("*", Rng(1), xlValues, xlWhole, xlByColumns, _
    > xlPrevious).EntireColumn)
    > End Function
    >
    > Hope this helps,
    >
    > Bob
    >
    >


  9. #9
    Dave Peterson
    Guest

    Re: .Cells.SpecialCells(xlLastCell)

    If you know that only column A was used, you could do something like:

    dim LastCell as range
    with activesheet
    set lastcell = .cells(.rows.count,"A").end(xlup)
    end with



    CyndyG wrote:
    >
    > How could I use this code when the last cell of the data contains character
    > 127(the end of file character) and all the data is in column A,there is only
    > 1 worksheet.
    >
    > "indiana1138" wrote:
    >
    > > Hello Simon,
    > >
    > > Other folks have found that Excel does not work as advertised in this
    > > respect. Try this code for something that might work:
    > >
    > > Function LastCell(Optional ws As Worksheet) As Range
    > > If ws Is Nothing Then Set ws = ActiveSheet
    > > Set Rng = ws.Cells
    > > Set LastCell = Rng(1)
    > > On Error Resume Next
    > > Set LastCell = Intersect( _
    > > Rng.Find("*", Rng(1), xlValues, xlWhole, xlByRows, _
    > > xlPrevious).EntireRow, _
    > > Rng.Find("*", Rng(1), xlValues, xlWhole, xlByColumns, _
    > > xlPrevious).EntireColumn)
    > > End Function
    > >
    > > Hope this helps,
    > >
    > > Bob
    > >
    > >


    --

    Dave Peterson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1