UsedRange problem
 

Can the UsedRange method only be used when preceded by 'Activesheet'? I
have a variable for a worksheet which I am trying to use in a 'With'
statement:

With [Variable name]
..UsedRange
and so on.....

but I cannot get this to work unless I replace the place a Select
statement before the 'With' statement and then replace the variable
name with 'Activesheet'?

Now, I am still a relative novice at this Excel VBA stuff but what I
have read in my pursuit of knowledge is that a 'good' programmer will
refrain from using Select/Activate statements in their code wherever
possible? So, I am a both stuck and a little confused???

Therefore, any help in clarifying this would be very much appreciated!

Thanks,

Adrian


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

UsedRange problem
 

Hi
I could be wrong here but you shouldn't need to apply usedrange to the
active sheet
eg
Dim ws As Worksheet
Set ws = Worksheets("sheet1")
With ws
MsgBox .UsedRange.Address
End With
Set ws = Nothing

should work from anywhere within the workbook

;-)
Kobayashi wrote in message
...
Can the UsedRange method only be used when preceded by 'Activesheet'? I
have a variable for a worksheet which I am trying to use in a 'With'
statement:

With [Variable name]
UsedRange
and so on.....

but I cannot get this to work unless I replace the place a Select
statement before the 'With' statement and then replace the variable
name with 'Activesheet'?

Now, I am still a relative novice at this Excel VBA stuff but what I
have read in my pursuit of knowledge is that a 'good' programmer will
refrain from using Select/Activate statements in their code wherever
possible? So, I am a both stuck and a little confused???

Therefore, any help in clarifying this would be very much appreciated!

Thanks,

Adrian


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

UsedRange problem
 

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/

UsedRange problem
 

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/

UsedRange problem
 

Looman,

Thanks again. I was merely trying to refresh the UsedRange, which i
why I didn't precede or follow it with anything.

Regards,

Adria

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