Redefining the UsedRange of a Worksheet

E

ExcelMonkey

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
 
T

Tom Ogilvy

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top