UsedRange problem

  • Thread starter Thread starter Kobayashi
  • Start date Start date
K

Kobayashi

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
 
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

;-)
 
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
 
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!!!
;-)
 
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
 
Back
Top