Hi,

If you press CTRL+End the cursor goes to the last cell in the worksheet. You
can also find the same cell in VBA using SpecialCells(xlCellTypeLastCell)

When you delete contents of cells or whole rows/columns, the address of the
last cell doesn't change immediately, but it will recalculate it
automatically - eventually.

My question is, how can I speed up this recalculation process, or cause it
to re-find the real last cell right away? And is there a method I can use in
VBA and directly in the spreadsheet?

By the way, this applies to Excel 2000 and 2002, maybe others as well.

Thanks in advance for your help!

S

Hi
saving the file should do the trick in nearly all cases (either VBA or
manually)

--
Regards
Frank Kabel
Frankfurt, Germany


Steve W wrote:
Hi,

If you press CTRL+End the cursor goes to the last cell in the
worksheet. You can also find the same cell in VBA using
SpecialCells(xlCellTypeLastCell)

When you delete contents of cells or whole rows/columns, the address
of the last cell doesn't change immediately, but it will recalculate
it automatically - eventually.

My question is, how can I speed up this recalculation process, or
cause it to re-find the real last cell right away? And is there a
method I can use in VBA and directly in the spreadsheet?

By the way, this applies to Excel 2000 and 2002, maybe others as
well.

Thanks in advance for your help!

S

Steve,

Here is a little sub that will reset it. The code is originally supplied by
Debra Dalgleish

Sub ResetEnd()
Dim cLastRow As Long
Dim cLastCol As Long
Dim oWS As Worksheet
Dim rng As Range

For Each oWS In ActiveWorkbook.Worksheets
With oWS
cLastRow = 0: cLastCol = 0
Set rng = .UsedRange
On Error Resume Next
cLastRow = .Cells.Find("*", After:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows).Row
cLastCol = .Cells.Find("*", After:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByColumns).Column
On Error GoTo 0

If cLastRow = 0 And cLastCol = 0 Then
.Columns.Delete
Else
.Range(.Cells(cLastRow + 1, 1), _
.Cells(.Rows.Count, 1)).EntireRow.Delete
.Range(.Cells(1, cLastCol + 1), _
.Cells(1, .Columns.Count)).EntireColumn.Delete
End If
End With
Next oWS

End Sub

--

HTH

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


"Steve W" wrote in message
...
Hi,

If you press CTRL+End the cursor goes to the last cell in the worksheet.
You
can also find the same cell in VBA using SpecialCells(xlCellTypeLastCell)

When you delete contents of cells or whole rows/columns, the address of
the
last cell doesn't change immediately, but it will recalculate it
automatically - eventually.

My question is, how can I speed up this recalculation process, or cause it
to re-find the real last cell right away? And is there a method I can use
in
VBA and directly in the spreadsheet?

By the way, this applies to Excel 2000 and 2002, maybe others as well.

Thanks in advance for your help!

S

Steve

in the VBA Immediate Window, type:

Activesheet.UsedRange and press Enter

Regards

Trevor


"Steve W" wrote in message
...
Hi,

If you press CTRL+End the cursor goes to the last cell in the worksheet.
You can also find the same cell in VBA using
SpecialCells(xlCellTypeLastCell)

When you delete contents of cells or whole rows/columns, the address of
the last cell doesn't change immediately, but it will recalculate it
automatically - eventually.

My question is, how can I speed up this recalculation process, or cause it
to re-find the real last cell right away? And is there a method I can use
in VBA and directly in the spreadsheet?

By the way, this applies to Excel 2000 and 2002, maybe others as well.

Thanks in advance for your help!

S

You can always find the last row with

lastrow = Cells(Rows.Count, 1).End(xlUp).Row

where Count, 1 is the longest column with data. this assums A or column 1.
If another column has more rows populated, use that column - if it is "C"
then use 3 etc.

Glen

"Steve W" wrote in message
...
Hi,

If you press CTRL+End the cursor goes to the last cell in the worksheet.
You can also find the same cell in VBA using
SpecialCells(xlCellTypeLastCell)

When you delete contents of cells or whole rows/columns, the address of
the last cell doesn't change immediately, but it will recalculate it
automatically - eventually.

My question is, how can I speed up this recalculation process, or cause it
to re-find the real last cell right away? And is there a method I can use
in VBA and directly in the spreadsheet?

By the way, this applies to Excel 2000 and 2002, maybe others as well.

Thanks in advance for your help!

S

Hi Frank,

Thanks for your quick reply! Saving the file does indeed do the trick!

However this is can be a slow job, particularly over a VPN, for example. Are
there are any other ways round this besides saving the file?

Best Regards

S


"Frank Kabel" wrote in message
...
Hi
saving the file should do the trick in nearly all cases (either VBA or
manually)

--
Regards
Frank Kabel
Frankfurt, Germany


Steve W wrote:
Hi,

If you press CTRL+End the cursor goes to the last cell in the
worksheet. You can also find the same cell in VBA using
SpecialCells(xlCellTypeLastCell)

When you delete contents of cells or whole rows/columns, the address
of the last cell doesn't change immediately, but it will recalculate
it automatically - eventually.

My question is, how can I speed up this recalculation process, or
cause it to re-find the real last cell right away? And is there a
method I can use in VBA and directly in the spreadsheet?

By the way, this applies to Excel 2000 and 2002, maybe others as
well.

Thanks in advance for your help!

S

I should have checked for other replies before posting my last comment.

Thanks a lot guys!!!

S


"Steve W" wrote in message
...
Hi Frank,

Thanks for your quick reply! Saving the file does indeed do the trick!

However this is can be a slow job, particularly over a VPN, for example.
Are there are any other ways round this besides saving the file?

Best Regards

S


"Frank Kabel" wrote in message
...
Hi
saving the file should do the trick in nearly all cases (either VBA or
manually)

--
Regards
Frank Kabel
Frankfurt, Germany


Steve W wrote:
Hi,

If you press CTRL+End the cursor goes to the last cell in the
worksheet. You can also find the same cell in VBA using
SpecialCells(xlCellTypeLastCell)

When you delete contents of cells or whole rows/columns, the address
of the last cell doesn't change immediately, but it will recalculate
it automatically - eventually.

My question is, how can I speed up this recalculation process, or
cause it to re-find the real last cell right away? And is there a
method I can use in VBA and directly in the spreadsheet?

By the way, this applies to Excel 2000 and 2002, maybe others as
well.

Thanks in advance for your help!

S




I sh