That reminded me of these two functions that I dummied up awhile ago (one
for determining the maximum row in use and the other for determining the
maximum column in use), but I don't think I ever posted them to any
newsgroups before. If you don't specify a worksheet in the first (optional)
argument, then the active sheet is used. The second optional argument is the
interesting one... it lets you determine whether to include hidden rows or
columns when determining the maximum row and column that are in use; that
is, if a hidden row or column contains the maximum row or column, it will be
ignored unless the second argument is set to True. This allows you to get
the maximum row or column for what you see on the worksheet rather than for
what what any hidden data would return. I wasn't sure which would be the
most logical default for this second argument, so I chose not factor in
hidden rows or columns (that is, the functions return the maximum row and
column for only the visible data); if desired, this can be easily changed in
the declaration headers for each function. I hope you and others will find
this of some use...

Function MaxRowInUse(Optional WS As Worksheet, Optional _
FactorInHiddenRows As Boolean = False) As Long
Dim X As Long
Dim LastRow As Long
If WS Is Nothing Then Set WS = ActiveSheet
With WS
For X = 1 To .UsedRange.Columns.Count
If Not (Not FactorInHiddenRows And Columns(X).Width = 0) Then
LastRow = .Cells(.Rows.Count, X).End(xlUp).Row
If LastRow MaxRowInUse Then MaxRowInUse = LastRow
End If
Next
End With
End Function

Function MaxColumnInUse(Optional WS As Worksheet, Optional _
FactorInHiddenColumns As Boolean = False) As Long
Dim X As Long
Dim LastColumn As Long
If WS Is Nothing Then Set WS = ActiveSheet
With WS
For X = 1 To .UsedRange.Rows.Count
If Not (Not FactorInHiddenColumns And Rows(X).Height = 0) Then
LastColumn = .Cells(X, .Columns.Count).End(xlToLeft).Column
If LastColumn MaxColumnInUse Then MaxColumnInUse = LastColumn
End If
Next
End With
End Function

Rick



"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Rick

I stumbled across this UDF from Bob Flanagan which ignores those once
filled
then cleared cells.

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.

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

Then a statement like Set rng = RangeToUse(ActiveSheet)


Gord


On Sat, 2 Aug 2008 17:28:02 -0700, Xaraam

wrote:

As for what controls the setting/resetting of the UsedRange, it looks
like
deleting entire columns and/or rows is the key. See the "To
programmatically
reset the used range" section on this webpage...