# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  > [SOLVED] SpecialCells(xlLastCell)

## Jos&eacute; Ignacio Bella

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.

----------


## David

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.
>
>
>

----------


## Jos&eacute; Ignacio Bella

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.
> >
> >
> >

----------


## Bob Phillips

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.
> >
> >
> >

----------


## Jos&eacute; Ignacio Bella

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.
> > >
> > >
> > >
>
>

----------


## KL

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.
>
>

----------


## KL

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.
>>
>>
>
>

----------


## quartz

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.
>
>
>

----------


## 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. 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.
>>>
>>>
>>
>>
>
>

----------


## Lonnie M.

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.

----------


## Norman Jones

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

----------


## KL

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

----------


## Jos&eacute; Ignacio Bella

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.
> >
> >
> >

----------

