SpecialCells(xlLastCell)

[José 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]

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

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

 

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

 

[José 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

 

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

 

[KL]

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

KL

 

[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

 

[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

 

[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.aspx?scid=kb;en-us;832293

 

[KL]

Thanks Norman, one more thing learnt today.

Regards,
KL

 

[José 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