Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UsedRange & Formatting of Cells | Excel Programming | |||
UsedRange on blank sheet? | Excel Programming | |||
finding last row number of UsedRange | Excel Programming | |||
UsedRange problem | Excel Programming | |||
Usedrange | Excel Programming |