In looking over my code again, I guess it would be more Excel-like if I used
the Hidden properties of the Columns and Rows in my tests rather than test
for their being "equal to zero" (even though there is no practical
difference between doing either)...

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).Hidden) 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).Hidden) Then
LastColumn = .Cells(X, .Columns.Count).End(xlToLeft).Column
If LastColumn MaxColumnInUse Then MaxColumnInUse = LastColumn
End If
Next
End With
End Function

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
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...