Erratic Sheet Sizing functions???

[Art]

Esteemed Group Members and Lurkers:

I'm having a TERRIBLE time trying to get an ACCURATE size
for a worksheet in a macro. The functions I am using are:

ncol = ActiveSheet.Range(Cells(1, 1), Cells.SpecialCells
(xlLastCell)).Columns.Count
nrow = ActiveSheet.Range(Cells(1, 1), Cells.SpecialCells
(xlLastCell)).Rows.Count

Sometimes, a sheet that has had some cells outside the
currently "filled" range deleted, erroneously reports the
current filled range as bigger than the actual range.
This is driving me crazy! How do I 'reset' a sheet to get
rid of the 'invisible trash' that keeps the sheet from
reporting the correct size?

Thank you for any comments, suggestions, or assistance.

Blessings in abundance, all the best, and ENJOY!

Art Carlisle, PA USA

 

[Gord Dibben]

Art

This UDF and macro will find the actual used range.

Sub UsedRangePick()
Dim tempRange As Range
Set tempRange = RangeToUse(ActiveSheet)
tempRange.Select
End Sub

Function RangeToUse(anySheet As Worksheet) As Range
'this function returns the range from cells A1 to cell which is the
'intersection of the last row with an entry and the last column with an entry.
'used with UsedRangePick macro.....REAL USED RANGE!!
Dim i As Integer, c As Integer, R As Integer
With anySheet.UsedRange
i = .Cells(.Cells.Count).Column + 1
For c = i To 1 Step -1
If Application.CountA(anySheet.Columns(c)) > 0 _
Then Exit For
Next
i = .Cells(.Cells.Count).Row + 1
For R = i To 1 Step -1
If Application.CountA(anySheet.Rows(R)) > 0 Then _
Exit For
Next
End With
With anySheet
Set RangeToUse = .Range(.Cells(1, 1), .Cells(R, c))
End With
End Function

Gord Dibben Excel MVP