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.

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

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

"David" <[email protected]> escribió en el mensaje
news:[email protected]...
> 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.
> >
> >
> >

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" <[email protected]> wrote in message
news:[email protected]...
> 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.
> >
> >
> >

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" <[email protected]> escribió en el mensaje
news:%[email protected]...
> sometimes it's so easy... i'll try it.
> thx
>
> "David" <[email protected]> escribió en el mensaje
> news:[email protected]...
> > 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.
> > >
> > >
> > >
>
>

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" <[email protected]> wrote in message
news:O6uPi%[email protected]...
> 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.
>
>

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

KL


"KL" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:O6uPi%[email protected]...
>> 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.
>>
>>
>
>

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

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" <[email protected]> wrote in message
news:[email protected]...
> Sorry, the line "For Each a In rng" should actually read "For Each a In
> rng.Areas"
>
> KL
>
>
> "KL" <[email protected]> wrote in message
> news:%[email protected]...
>> 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" <[email protected]> wrote in message
>> news:O6uPi%[email protected]...
>>> 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.
>>>
>>>
>>
>>
>
>

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.

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" <[email protected]> wrote in message
news:[email protected]...
> 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

Thanks Norman, one more thing learnt today.

Regards,
KL

"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>
>

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" <[email protected]> escribió en el mensaje
news:[email protected]...
> 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.
> >
> >
> >