Resetting Last Cell

[davidm]

I am a fond user of resetting last cell via the UsedRange property. My
favorite one is:

Sub ResetLastCel()
For each sh in Worksheets
sh.activate
x=Activesheet.UsedRange.Rows.count
Next
Sub

The above serves me well most of the time. There is however the odd 10%
(or so)chance of this failing and when it does, testing to locate the
last cell with *Cells.SpecialCells(xlCellTypeXlLastCell)* produces an
error. To convince myself that there are no false blank cells, I would
highlight and delete all rows beyond the "visible" usedrange.
Nothwithstanding this, the error persists.What gives?


David.

 

[RB Smissaert]

Try this one:

Function SetRealLastCell(shSheet As Worksheet) As Range
Set SetRealLastCell = Cells(Range(shSheet.Cells(1),
shSheet.UsedRange).Rows.count, _
Range(shSheet.Cells(1),
shSheet.UsedRange).Columns.count)
End Function

I think I got this from John Walkenbach's site.

RBS

 

[Ron de Bruin]

See also
http://www.contextures.com/xlfaqApp.html#Unused


--
Regards Ron de Bruin
http://www.rondebruin.nl

Try this one:

Function SetRealLastCell(shSheet As Worksheet) As Range
Set SetRealLastCell = Cells(Range(shSheet.Cells(1), shSheet.UsedRange).Rows.count, _
Range(shSheet.Cells(1), shSheet.UsedRange).Columns.count)
End Function

I think I got this from John Walkenbach's site.

RBS

 

[RB Smissaert]

Thanks for the tip.
What is the purpose though of dummyRng?
Souldn't you do With dummyRng after Set dummyRng?

RBS

 

[RB Smissaert]

To avoid the problem with merged cells I added a function that finds the
last merged cell:

Sub DeleteUnused(shSheet As Worksheet)

Dim lLR As Long
Dim lLC As Long
Dim wks As Worksheet
Dim rngDummy As Range
Dim arr

With shSheet

lLR = 0
lLC = 0

Set rngDummy = .UsedRange

On Error Resume Next
With rngDummy
lLR = _
.Cells.Find("*", _
after:=.Cells(1), _
LookIn:=xlFormulas, _
lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows).Row
lLC = _
.Cells.Find("*", _
after:=.Cells(1), _
LookIn:=xlFormulas, _
lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByColumns).Column
End With
On Error GoTo 0

'to avoid deleting a range that is part of a merged range
'--------------------------------------------------------
arr = getLastMergedCell(shSheet)

If arr(0) > lLR Then
lLR = arr(0)
End If

If arr(1) > lLC Then
lLC = arr(1)
End If

If lLR * lLC = 0 Then
.Columns.Delete
Else
.Range(.Cells(lLR + 1, 1), _
.Cells(.Rows.Count, 1)).EntireRow.Delete
.Range(.Cells(1, lLC + 1), _
.Cells(1, .Columns.Count)).EntireColumn.Delete
End If
End With

End Sub


Function getLastMergedCell(shSheet As Worksheet) As Variant

'will give the row and column of the last merged cell in the sheet
'-----------------------------------------------------------------
Dim rngCell As Range
Dim rngMerge As Range
Dim arr(0 To 1) As Long

For Each rngCell In shSheet.UsedRange.Cells
Set rngMerge = rngCell.MergeArea
If rngCell.MergeCells Then
If rngCell.Row > arr(0) Then
arr(0) = rngCell.Row
End If
If rngCell.Column > arr(1) Then
arr(1) = rngCell.Column
End If
End If
Next

getLastMergedCell = arr

End Function


RBS

 

[Dave Peterson]

Sometimes, excel will "fix" the lastusedcell just by using .usedrange. The code
is just trying to reset that usedrange with a single command...(and continues
just in case it failed).

 

[davidm]

Thanks for all the replies. Having gone through the various variants of
applying the Usedrange property to reset the last cell, am I right to
conclude that the method is not 100% fail-safe?

The following code (from
http://www.contextures.com/xlfaqApp.html#Unused) raises that question.
Take a fresh Worksheet, and enter a figure in Range($H$58000). Clear the
cell and run the code. Result? The scroll bar stays down at
$H$58000!

 

[Dave Peterson]

One of the warnings on Debra's site:

4. Save the file. Note: In older versions of Excel, you may have to Save, then
close and re-open the file before the used range is reset.

What version of excel are you using and did you save, close and reopen?

 

[Tom Ogilvy]

I did what you said with H58000 in a new worksheet in a new workbook.

After clearing the cell, I when to the immediate window and did

Activesheet.usedRange
and hit enter

I then when to A1 in the sheet and the scrollbar was immediately normal. I
did Edit=>Goto=>Special and chose lastcell. The selection remained in A1.

xl2003

So worked fine for me.