usedrange

[Mike]

hi,

i am trying to establish the part of a worksheet that contains values. the
usedrange function would seem to fit the bill (and works fine most of the
time), but every now and then it returns virtually the entire sheet as a
range rather than just the used part.

there is clearly something confusing it but there is no data obviously in
the cells it returns. does anyone have any ideas as to how to get around
this? is there some other way of finding the used part of a sheet?

the other thing i have noticed with the usedrange function is that when all
the columns are returned (as per problem above) it then leaves the column
letters out of the range's address (if you look at the address property of
the range object).

any ideas would be welcome.

mike

 

[Nick Hodge]

Mike

Excels UsedRange is a little flaky. If the cells have ever had data then it
will believe they are still there no matter what.

If your data is contiguous you might check out .CurrentRegion or you could
check out .SpecialCells(xlCellTypeLastCell). Both may help and return a
range object

I prefer to use .End(xlUp) or .End(xlToLeft) assigning them to variables and
coming from a known last cell like so

Dim lLastRow as Long
Dim iLastColumn as Integer
lLastRow=Range("A65536").End(xlUp).Row
iLastColumn=Range("IV1").End(xlToLeft).Column

You can then use the variables to set the range. I find this 100% reliable
in XL97 up

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Thomas Ramel]

Grüezi Mike

Mike schrieb am 17.02.2005

i am trying to establish the part of a worksheet that contains values. the
usedrange function would seem to fit the bill (and works fine most of the
time), but every now and then it returns virtually the entire sheet as a
range rather than just the used part.

there is clearly something confusing it but there is no data obviously in
the cells it returns. does anyone have any ideas as to how to get around
this? is there some other way of finding the used part of a sheet?

Assuming your datas start in A1, then you my get the last cell with the
following function:

Function GetLastCell(Optional rng As Range, _
Optional sh As Worksheet) _
As Range

If sh Is Nothing Then Set sh = ActiveSheet
If rng Is Nothing Then
Set rng = sh.Cells
Else:
Set rng = sh.Range(rng.Address)
End If

Set GetLastCell = sh.Cells(rng.Find(What:="*", _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row, _
rng.Find(What:="*", _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column)
End Function



If you call it without any parameters, yoz'll get the lastcell of the
active sheet.




Regards
Thomas Ramel

 

[Guest]

Mike,

The following sub calls the function below and returns the "filled" range of
the active sheet (you will need to adjust for line wrapping):

Private Sub Get_Filled_Range()

If RangeFilled = "" Then MsgBox "Sheet empty!" Else MsgBox RangeFilled

End Sub

Public Function RangeFilled() As String
'RETURN THE FILLED RANGE OF A SHEET
Dim rngFind As Range
Dim strAddressTL As String
Dim strAddressBR As String
Set rngFind = ActiveSheet.UsedRange.Find(What:="*", SearchOrder:=xlByRows,
SearchDirection:=xlNext)
If rngFind Is Nothing Then On Error GoTo 0: Exit Function Else strAddressTL
= rngFind.Row
Set rngFind = ActiveSheet.UsedRange.Find(What:="*",
SearchOrder:=xlByColumns, SearchDirection:=xlNext)
strAddressTL = Cells(strAddressTL, rngFind.Column).Address
Set rngFind = ActiveSheet.UsedRange.Find(What:="*", SearchOrder:=xlByRows,
SearchDirection:=xlPrevious)
strAddressBR = rngFind.Row
Set rngFind = ActiveSheet.UsedRange.Find(What:="*",
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
strAddressBR = Cells(strAddressBR, rngFind.Column).Address
RangeFilled = Range(strAddressTL & ":" & strAddressBR).Address
End Function

HTH

 

[Guest]

OOOOPS!

In my first post, I accidentally gave you an unperfected version. Use the
following function instead (sorry for confusion):

Public Function RangeFilled() As String
'RETURN THE FILLED RANGE OF A SHEET
Dim rngFind As Range
Dim strAddressTL As String
Dim strAddressBR As String
Set rngFind = ActiveSheet.UsedRange.Find(What:="*", SearchOrder:=xlByRows,
SearchDirection:=xlPrevious)
If rngFind Is Nothing Then On Error GoTo 0: Exit Function Else strAddressBR
= rngFind.Row
Set rngFind = ActiveSheet.UsedRange.Find(What:="*",
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
strAddressBR = Cells(strAddressBR, rngFind.Column).Address
Set rngFind = ActiveSheet.UsedRange.Find(What:="*",
After:=Range(strAddressBR), SearchOrder:=xlByRows, SearchDirection:=xlNext)
strAddressTL = rngFind.Row
Set rngFind = ActiveSheet.UsedRange.Find(What:="*",
After:=Range(strAddressBR), SearchOrder:=xlByColumns, SearchDirection:=xlNext)
strAddressTL = Cells(strAddressTL, rngFind.Column).Address
RangeFilled = Range(strAddressTL & ":" & strAddressBR).Address
End Function