Redefining the UsedRange of a Worksheet
I have a For Each Loop that looks at cells of a worksheet based on it
UsedRange. For Each cell in Worksheet.Used Range Next I want to be able to allow the user to enter in column and row adjustement that will redefine the used range. Say for example the UsedRange is A1:C90. And the user chooses Row 1 and Column 1. Then the redefined UsedRange would be B2:C90. And how would you pass multiple constraints on rows and columns to this UsedRange? Similar to entering columns and rows to be repeated in the page setup of Excel. The user could enter the rows and column using the RefEdit box. The user could use the control key to ente multiple constraints in teh Ref Edit box. I would want to pass these multiple constraints to a routine which redefines the used range used in the For Loop above. Thanks |
Redefining the UsedRange of a Worksheet
You don't redefine the usedrange. It is a read only property
set rng = Activesheet.Range(Userform1.Refedit1.Text) set rng1 = intersect(Activesheet.Usedrange,rng.EntireRow) might be applicable to what you want to do. -- Regards, Tom Ogilvy "ExcelMonkey" wrote in message m... I have a For Each Loop that looks at cells of a worksheet based on it UsedRange. For Each cell in Worksheet.Used Range Next I want to be able to allow the user to enter in column and row adjustement that will redefine the used range. Say for example the UsedRange is A1:C90. And the user chooses Row 1 and Column 1. Then the redefined UsedRange would be B2:C90. And how would you pass multiple constraints on rows and columns to this UsedRange? Similar to entering columns and rows to be repeated in the page setup of Excel. The user could enter the rows and column using the RefEdit box. The user could use the control key to ente multiple constraints in teh Ref Edit box. I would want to pass these multiple constraints to a routine which redefines the used range used in the For Loop above. Thanks |
All times are GMT +1. The time now is 04:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com