Last Cell

[Steve W]

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

 

[Frank Kabel]

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

 

[Bob Phillips]

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)

 

[Trevor Shuttleworth]

Steve

in the VBA Immediate Window, type:

Activesheet.UsedRange and press Enter

Regards

Trevor

 

[Glen Mettler]

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]

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

 

[Steve W]

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

Thanks a lot guys!!!

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

I sh