Closed Thread
Results 1 to 13 of 13

SpecialCells(xlLastCell)

  1. #1
    José Ignacio Bella
    Guest

    SpecialCells(xlLastCell)

    Hello Group, I have a problem with a macro, and maybe you can help me

    I use a sentence 'ActiveCell.SpecialCells(xlLastCell).Select' to go to the
    last worksheet cell.
    The problemis when this cell has been erased. Then it's not really the last
    cell, but Excel still points to this one.
    Saving, closing and re-opening, then Excel points to the real last cell.

    Do you know how to refresh the xlLastCell without closing the book?
    Thanks in advance.



  2. #2
    David
    Guest

    RE: SpecialCells(xlLastCell)

    Hi Jose,
    Using code to delete the row and then saving the file will change the last
    cell. Hope that helps.

    "José Ignacio Bella" wrote:

    > Hello Group, I have a problem with a macro, and maybe you can help me
    >
    > I use a sentence 'ActiveCell.SpecialCells(xlLastCell).Select' to go to the
    > last worksheet cell.
    > The problemis when this cell has been erased. Then it's not really the last
    > cell, but Excel still points to this one.
    > Saving, closing and re-opening, then Excel points to the real last cell.
    >
    > Do you know how to refresh the xlLastCell without closing the book?
    > Thanks in advance.
    >
    >
    >


  3. #3
    José Ignacio Bella
    Guest

    Re: SpecialCells(xlLastCell)

    sometimes it's so easy... i'll try it.
    thx

    "David" <David@discussions.microsoft.com> escribió en el mensaje
    news:580B1F05-CCEE-4605-9568-B0AC09CF899C@microsoft.com...
    > Hi Jose,
    > Using code to delete the row and then saving the file will change the last
    > cell. Hope that helps.
    >
    > "José Ignacio Bella" wrote:
    >
    > > Hello Group, I have a problem with a macro, and maybe you can help me
    > >
    > > I use a sentence 'ActiveCell.SpecialCells(xlLastCell).Select' to go to

    the
    > > last worksheet cell.
    > > The problemis when this cell has been erased. Then it's not really the

    last
    > > cell, but Excel still points to this one.
    > > Saving, closing and re-opening, then Excel points to the real last cell.
    > >
    > > Do you know how to refresh the xlLastCell without closing the book?
    > > Thanks in advance.
    > >
    > >
    > >




  4. #4
    Bob Phillips
    Guest

    Re: SpecialCells(xlLastCell)

    See http://www.contextures.com/xlfaqApp.html#Unused for a way to fix this in
    code.

    --

    HTH

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


    "David" <David@discussions.microsoft.com> wrote in message
    news:580B1F05-CCEE-4605-9568-B0AC09CF899C@microsoft.com...
    > Hi Jose,
    > Using code to delete the row and then saving the file will change the last
    > cell. Hope that helps.
    >
    > "José Ignacio Bella" wrote:
    >
    > > Hello Group, I have a problem with a macro, and maybe you can help me
    > >
    > > I use a sentence 'ActiveCell.SpecialCells(xlLastCell).Select' to go to

    the
    > > last worksheet cell.
    > > The problemis when this cell has been erased. Then it's not really the

    last
    > > cell, but Excel still points to this one.
    > > Saving, closing and re-opening, then Excel points to the real last cell.
    > >
    > > Do you know how to refresh the xlLastCell without closing the book?
    > > Thanks in advance.
    > >
    > >
    > >




  5. #5
    Jos&eacute; Ignacio Bella
    Guest

    Re: SpecialCells(xlLastCell)

    Not so easy
    If you enter something in B21 and I3, the LastCell will be I21.
    So I cannot check for LastCell = empty, and then delete the row



    "José Ignacio Bella" <ji_bella@wanadoo.es> escribió en el mensaje
    news:%23n4lwgK9EHA.1400@TK2MSFTNGP11.phx.gbl...
    > sometimes it's so easy... i'll try it.
    > thx
    >
    > "David" <David@discussions.microsoft.com> escribió en el mensaje
    > news:580B1F05-CCEE-4605-9568-B0AC09CF899C@microsoft.com...
    > > Hi Jose,
    > > Using code to delete the row and then saving the file will change the

    last
    > > cell. Hope that helps.
    > >
    > > "José Ignacio Bella" wrote:
    > >
    > > > Hello Group, I have a problem with a macro, and maybe you can help me
    > > >
    > > > I use a sentence 'ActiveCell.SpecialCells(xlLastCell).Select' to go

    to
    > the
    > > > last worksheet cell.
    > > > The problemis when this cell has been erased. Then it's not really the

    > last
    > > > cell, but Excel still points to this one.
    > > > Saving, closing and re-opening, then Excel points to the real last

    cell.
    > > >
    > > > Do you know how to refresh the xlLastCell without closing the book?
    > > > Thanks in advance.
    > > >
    > > >
    > > >

    >
    >




  6. #6
    KL
    Guest

    Re: SpecialCells(xlLastCell)

    Just a crazy idea (not fully tested). Should ignore any cells that do not
    contain formulas or values (incl. text, numeric and logical).

    Regards,
    KL

    '------Code Start-------
    Function LastCell(Optional Ws As Worksheet) As Range
    Dim consts As Range: Dim frmls As Range
    Dim r As Single: Dim c As Integer
    Dim rTemp As Single: Dim cTemp As Integer
    Dim rng As Range

    If Ws Is Nothing Then Set Ws = ActiveSheet
    With Ws.Cells
    On Error Resume Next
    Set consts = .SpecialCells(xlCellTypeConstants)
    Set frmls = .SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0
    If Not frmls Is Nothing Then
    If Not consts Is Nothing Then
    Set rng = Application.Union(frmls, consts)
    Else
    Set rng = frmls
    End If
    ElseIf Not consts Is Nothing Then
    Set rng = consts
    End If
    End With
    r = 1: c = 1
    For Each a In rng
    rTemp = a.Cells(a.Rows.Count, a.Columns.Count).Row
    cTemp = a.Cells(a.Rows.Count, a.Columns.Count).Column
    If rTemp > r Then r = rTemp
    If cTemp > c Then c = cTemp
    Next
    Set LastCell = Ws.Cells(r, c)
    End Function

    Sub test()
    Dim mysheet As Worksheet
    Dim myrng As Range

    Set mysheet = Sheets(2)
    Set myrng = LastCell(mysheet)
    mysheet.Activate
    myrng.Select
    End Sub
    '------Code End-------

    "José Ignacio Bella" <ji_bella@wanadoo.es> wrote in message
    news:O6uPi%23J9EHA.3700@tk2msftngp13.phx.gbl...
    > Hello Group, I have a problem with a macro, and maybe you can help me
    >
    > I use a sentence 'ActiveCell.SpecialCells(xlLastCell).Select' to go to
    > the
    > last worksheet cell.
    > The problemis when this cell has been erased. Then it's not really the
    > last
    > cell, but Excel still points to this one.
    > Saving, closing and re-opening, then Excel points to the real last cell.
    >
    > Do you know how to refresh the xlLastCell without closing the book?
    > Thanks in advance.
    >
    >




  7. #7
    KL
    Guest

    Re: SpecialCells(xlLastCell)

    Sorry, the line "For Each a In rng" should actually read "For Each a In
    rng.Areas"

    KL


    "KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
    news:%239w$3AM9EHA.2192@TK2MSFTNGP14.phx.gbl...
    > Just a crazy idea (not fully tested). Should ignore any cells that do not
    > contain formulas or values (incl. text, numeric and logical).
    >
    > Regards,
    > KL
    >
    > '------Code Start-------
    > Function LastCell(Optional Ws As Worksheet) As Range
    > Dim consts As Range: Dim frmls As Range
    > Dim r As Single: Dim c As Integer
    > Dim rTemp As Single: Dim cTemp As Integer
    > Dim rng As Range
    >
    > If Ws Is Nothing Then Set Ws = ActiveSheet
    > With Ws.Cells
    > On Error Resume Next
    > Set consts = .SpecialCells(xlCellTypeConstants)
    > Set frmls = .SpecialCells(xlCellTypeFormulas)
    > On Error GoTo 0
    > If Not frmls Is Nothing Then
    > If Not consts Is Nothing Then
    > Set rng = Application.Union(frmls, consts)
    > Else
    > Set rng = frmls
    > End If
    > ElseIf Not consts Is Nothing Then
    > Set rng = consts
    > End If
    > End With
    > r = 1: c = 1
    > For Each a In rng
    > rTemp = a.Cells(a.Rows.Count, a.Columns.Count).Row
    > cTemp = a.Cells(a.Rows.Count, a.Columns.Count).Column
    > If rTemp > r Then r = rTemp
    > If cTemp > c Then c = cTemp
    > Next
    > Set LastCell = Ws.Cells(r, c)
    > End Function
    >
    > Sub test()
    > Dim mysheet As Worksheet
    > Dim myrng As Range
    >
    > Set mysheet = Sheets(2)
    > Set myrng = LastCell(mysheet)
    > mysheet.Activate
    > myrng.Select
    > End Sub
    > '------Code End-------
    >
    > "José Ignacio Bella" <ji_bella@wanadoo.es> wrote in message
    > news:O6uPi%23J9EHA.3700@tk2msftngp13.phx.gbl...
    >> Hello Group, I have a problem with a macro, and maybe you can help me
    >>
    >> I use a sentence 'ActiveCell.SpecialCells(xlLastCell).Select' to go to
    >> the
    >> last worksheet cell.
    >> The problemis when this cell has been erased. Then it's not really the
    >> last
    >> cell, but Excel still points to this one.
    >> Saving, closing and re-opening, then Excel points to the real last cell.
    >>
    >> Do you know how to refresh the xlLastCell without closing the book?
    >> Thanks in advance.
    >>
    >>

    >
    >




  8. #8
    quartz
    Guest

    RE: SpecialCells(xlLastCell)

    Jose,

    This function may do what you want. Copy into a module and run
    "Test_The_Function" below (You may need to correct for line wrapping):

    Public Function CellLastFilled() As String
    'RETURN ADDRESS OF LAST FILLED CELL IN THE ACTIVE SHEET: ELSE 0;
    Dim intCol As Integer
    Dim lngRow As Long
    On Error Resume Next
    lngRow = ActiveSheet.UsedRange.Find(What:="*", SearchOrder:=xlByRows,
    SearchDirection:=xlPrevious).Row
    intCol = ActiveSheet.Range(lngRow & ":" & lngRow).Find(What:="*",
    SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    CellLastFilled = IIf(Err > 0, 0, Application.ConvertFormula("R" & lngRow &
    "C" & intCol, xlR1C1, xlA1))
    On Error GoTo 0
    End Function


    Sub Test_The_Function()

    MsgBox CellLastFilled

    End Sub


    "José Ignacio Bella" wrote:

    > Hello Group, I have a problem with a macro, and maybe you can help me
    >
    > I use a sentence 'ActiveCell.SpecialCells(xlLastCell).Select' to go to the
    > last worksheet cell.
    > The problemis when this cell has been erased. Then it's not really the last
    > cell, but Excel still points to this one.
    > Saving, closing and re-opening, then Excel points to the real last cell.
    >
    > Do you know how to refresh the xlLastCell without closing the book?
    > Thanks in advance.
    >
    >
    >


  9. #9
    KL
    Guest

    Re: SpecialCells(xlLastCell)

    Like I said before - crazy idea. After some testing, it looks like the
    function defaults to the last cell of the sheet after certain size of rango
    or number of areas. The following code I found via Google (slightly
    modified) does seem to 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

    KL

    "KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
    news:OQv$8GM9EHA.3368@TK2MSFTNGP10.phx.gbl...
    > Sorry, the line "For Each a In rng" should actually read "For Each a In
    > rng.Areas"
    >
    > KL
    >
    >
    > "KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
    > news:%239w$3AM9EHA.2192@TK2MSFTNGP14.phx.gbl...
    >> Just a crazy idea (not fully tested). Should ignore any cells that do not
    >> contain formulas or values (incl. text, numeric and logical).
    >>
    >> Regards,
    >> KL
    >>
    >> '------Code Start-------
    >> Function LastCell(Optional Ws As Worksheet) As Range
    >> Dim consts As Range: Dim frmls As Range
    >> Dim r As Single: Dim c As Integer
    >> Dim rTemp As Single: Dim cTemp As Integer
    >> Dim rng As Range
    >>
    >> If Ws Is Nothing Then Set Ws = ActiveSheet
    >> With Ws.Cells
    >> On Error Resume Next
    >> Set consts = .SpecialCells(xlCellTypeConstants)
    >> Set frmls = .SpecialCells(xlCellTypeFormulas)
    >> On Error GoTo 0
    >> If Not frmls Is Nothing Then
    >> If Not consts Is Nothing Then
    >> Set rng = Application.Union(frmls, consts)
    >> Else
    >> Set rng = frmls
    >> End If
    >> ElseIf Not consts Is Nothing Then
    >> Set rng = consts
    >> End If
    >> End With
    >> r = 1: c = 1
    >> For Each a In rng
    >> rTemp = a.Cells(a.Rows.Count, a.Columns.Count).Row
    >> cTemp = a.Cells(a.Rows.Count, a.Columns.Count).Column
    >> If rTemp > r Then r = rTemp
    >> If cTemp > c Then c = cTemp
    >> Next
    >> Set LastCell = Ws.Cells(r, c)
    >> End Function
    >>
    >> Sub test()
    >> Dim mysheet As Worksheet
    >> Dim myrng As Range
    >>
    >> Set mysheet = Sheets(2)
    >> Set myrng = LastCell(mysheet)
    >> mysheet.Activate
    >> myrng.Select
    >> End Sub
    >> '------Code End-------
    >>
    >> "José Ignacio Bella" <ji_bella@wanadoo.es> wrote in message
    >> news:O6uPi%23J9EHA.3700@tk2msftngp13.phx.gbl...
    >>> Hello Group, I have a problem with a macro, and maybe you can help me
    >>>
    >>> I use a sentence 'ActiveCell.SpecialCells(xlLastCell).Select' to go to
    >>> the
    >>> last worksheet cell.
    >>> The problemis when this cell has been erased. Then it's not really the
    >>> last
    >>> cell, but Excel still points to this one.
    >>> Saving, closing and re-opening, then Excel points to the real last cell.
    >>>
    >>> Do you know how to refresh the xlLastCell without closing the book?
    >>> Thanks in advance.
    >>>
    >>>

    >>
    >>

    >
    >




  10. #10
    Lonnie M.
    Guest

    Re: SpecialCells(xlLastCell)

    Dim CountData As Long
    'If you are looking for the last cell in column 'B'
    CountData = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
    Cells(CountData, 2).Select

    HTH--Lonnie M.


  11. #11
    Norman Jones
    Guest

    Re: SpecialCells(xlLastCell)

    Hi KL,

    Hi KL,

    > Like I said before - crazy idea. After some testing, it looks like the
    > function defaults to the last cell of the sheet after certain size of
    > rango or number of areas.


    See Knowledgebase article # 832293:

    The .SpecialCells(xlCellTypeBlanks) VBA function
    does not work as expected in Excel
    http://support.microsoft.com/default...b;en-us;832293


    ---
    Regards,
    Norman


    "KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
    news:O4jtG6M9EHA.2112@TK2MSFTNGP14.phx.gbl...
    > Like I said before - crazy idea. After some testing, it looks like the
    > function defaults to the last cell of the sheet after certain size of
    > rango or number of areas. The following code I found via Google (slightly
    > modified) does seem to 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
    >
    > KL




  12. #12
    KL
    Guest

    Re: SpecialCells(xlLastCell)

    Thanks Norman, one more thing learnt today.

    Regards,
    KL

    "Norman Jones" <normanjones@whereforartthou.com> wrote in message
    news:Oja6yMN9EHA.3124@TK2MSFTNGP11.phx.gbl...
    > Hi KL,
    >
    > Hi KL,
    >
    >> Like I said before - crazy idea. After some testing, it looks like the
    >> function defaults to the last cell of the sheet after certain size of
    >> rango or number of areas.

    >
    > See Knowledgebase article # 832293:
    >
    > The .SpecialCells(xlCellTypeBlanks) VBA function
    > does not work as expected in Excel
    > http://support.microsoft.com/default...b;en-us;832293
    >
    >
    > ---
    > Regards,
    > Norman
    >
    >
    > "KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
    > news:O4jtG6M9EHA.2112@TK2MSFTNGP14.phx.gbl...
    >> Like I said before - crazy idea. After some testing, it looks like the
    >> function defaults to the last cell of the sheet after certain size of
    >> rango or number of areas. The following code I found via Google (slightly
    >> modified) does seem to 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
    >>
    >> KL

    >
    >




  13. #13
    Jos&eacute; Ignacio Bella
    Guest

    Re: SpecialCells(xlLastCell)

    Hello Quartz. Ok, my initial question was bad posed; first of all, I need to
    define what means LastCell.

    Entering data in D9 and E7, your function says LastCell is D9 (cell with
    entry in greatest row)

    What I'm looking for is the last corner of the rectangle beginning with A1
    and ending with the last used row and the last used column; in this case,
    LastCell is E9 (and doesn't matter if it's empty)

    KL sent me this code that works

    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

    Thanks to all again


    "quartz" <quartz@discussions.microsoft.com> escribió en el mensaje
    news:4894CB8E-7B3E-449D-8266-4B97CDBDF226@microsoft.com...
    > Jose,
    >
    > This function may do what you want. Copy into a module and run
    > "Test_The_Function" below (You may need to correct for line wrapping):
    >
    > Public Function CellLastFilled() As String
    > 'RETURN ADDRESS OF LAST FILLED CELL IN THE ACTIVE SHEET: ELSE 0;
    > Dim intCol As Integer
    > Dim lngRow As Long
    > On Error Resume Next
    > lngRow = ActiveSheet.UsedRange.Find(What:="*", SearchOrder:=xlByRows,
    > SearchDirection:=xlPrevious).Row
    > intCol = ActiveSheet.Range(lngRow & ":" & lngRow).Find(What:="*",
    > SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    > CellLastFilled = IIf(Err > 0, 0, Application.ConvertFormula("R" & lngRow &
    > "C" & intCol, xlR1C1, xlA1))
    > On Error GoTo 0
    > End Function
    >
    >
    > Sub Test_The_Function()
    >
    > MsgBox CellLastFilled
    >
    > End Sub
    >
    >
    > "José Ignacio Bella" wrote:
    >
    > > Hello Group, I have a problem with a macro, and maybe you can help me
    > >
    > > I use a sentence 'ActiveCell.SpecialCells(xlLastCell).Select' to go to

    the
    > > last worksheet cell.
    > > The problemis when this cell has been erased. Then it's not really the

    last
    > > cell, but Excel still points to this one.
    > > Saving, closing and re-opening, then Excel points to the real last cell.
    > >
    > > Do you know how to refresh the xlLastCell without closing the book?
    > > Thanks in advance.
    > >
    > >
    > >




Closed 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