resizing a range

D

don

When resizing a range, excel doesn't seem to acknowledge the new size
In this example rect is increased by 1 row and 1 column.
yet when the number of rows is asked for in the msgbox in replies with
8.
It seems that it should recognize the new size.
How do you get it to acknowledge the new dimensions.
Thanks
Don


Sub resize()
Worksheets("Sheet3").Activate
Set rect = Range("a1:c8")
numRows = rect.Rows.Count
numColumns = rect.Columns.Count
rect.resize(numRows + 1, numColumns + 1).Select
MsgBox rect.Rows.Count
End Sub
 
G

Gary''s Student

Try:


Sub resize()
Worksheets("Sheet3").Activate
Set rect = Range("a1:c8")
numRows = rect.Rows.Count
numColumns = rect.Columns.Count
rect.resize(numRows + 1, numColumns + 1).Select
Set rect = Selection
MsgBox rect.Rows.Count
End Sub

Looks like the re-size increased the Selected region, but you still need the
set to increase rect.
 
G

Gordon Rainsford

It doesn't look to me as though you have resized the range "rect" -
you've merely made a selection of some cells based on a resizing of that
range.

Try inserting:

Set RECT = RECT.resize(numRows + 1, numcolumns + 1)

in place of the line before the MSGBOX.
 
J

JE McGimpsey

Your statement

rect.resize(numRows + 1, numColumns + 1).Select

Selects a larger area, but doesn't change rect.

If you're trying to change rect, you can use something like

Set rect = rect.Resize(numRows + 1, numColumns + 1)
rect.Select

or, if you just want the size of the selected area, use the Selection
object directly:

Msgbox Selection.Rows.Count
 

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