Adrian
To be honest I don't know of the reason why it doesn't work.

As for using msgbox & address it was purely for purposes of demonstration as
you didn't state what you wanted to do with usedrange. The code should
return the address of the usedrange of Sheet1.

Below is another example. Run it in a new book with some rubbishy data on
Sheet1. It tells you what the address of the used range is, copies the used
range to sheet2, changes the background colour to red, clears the contents
of the used range then selects the used range

Sub UsedRangeDemo()
Dim ws As Worksheet
Set ws = Worksheets("sheet1")
With ws
MsgBox "The UsedRange is " & .Name & .UsedRange.Address
.UsedRange.Copy Destination:=Worksheets("Sheet2").Range("A1")
.UsedRange.Interior.Color = vbRed
.UsedRange.ClearContents
.Activate
.UsedRange.Select
End With
Set ws = Nothing
End Sub


another way of doing the same thing.....

Sub UsedRangeDemo()
Dim ws As Worksheet
Set ws = Worksheets("sheet1")
With ws.UsedRange
MsgBox "The UsedRange is " & .Parent.Name & .Address
.Copy Destination:=Worksheets("Sheet2").Range("A1")
.Interior.Color = vbRed
.ClearContents
.Parent.Activate
.Select
End With
Set ws = Nothing
End Sub

Hope this works!!!
;-)

Kobayashi wrote in message
...
Loomah,

I've tried this and it doesn't seem to work? Can you explain why you
are advising using the msgbox and address methods/properties?

Thanks,

Adrian


---
Message posted from http://www.ExcelForum.com/