Resize Range Problem
 

I am trying to resize a named excel array. When I count the startin
rows of the range they equal 17520. When I count the starting column
of the range they equal 244.

I then adjust these values by two variables AMax = 17520 and BMax
244. I then pass the difference between the starting values and thes
adjustment values to two variables. They ar both 0. These variable
are the incremental change to the rows and columns of the range. I the
resize the range with these increment variables aand rename it.
However when I get to the second last line of the code, it says tha
my row count = 244. This cannot be due to the fact that my ro
increment = 0. It should equal 17520. What am I missing?



Old2DRows = Range("ClearArray").Rows.Count
Old2DColumns = Range("ClearArray").Columns.Count

Changeto2DRows = Old2DRows - AMax
Changeto2DColumns = Old2DColumns - BMax

With Range("ClearArray")
.Resize(.Rows.Count + Changeto2DRows).Name = "ClearArray"
.Resize(.Columns.Count + Changeto2DColumns).Name = "ClearArray"
End With

New2DRows = Range("ClearArray").Rows.Count
New2DColumns = Range("ClearArray").Columns.Coun

--
Message posted from http://www.ExcelForum.com

Resize Range Problem
 

You haven't given us correct information. The code as written won't compile.

With Range("ClearArray")
Resize(.Rows.Count + Changeto2DRows).Name = "ClearArray"
Resize(.Columns.Count + Changeto2DColumns).Name = "ClearArray"
End With

doesn't make sense.

Alan Beban

ExcelMonkey < wrote:
I am trying to resize a named excel array. When I count the starting
rows of the range they equal 17520. When I count the starting columns
of the range they equal 244.

I then adjust these values by two variables AMax = 17520 and BMax =
244. I then pass the difference between the starting values and these
adjustment values to two variables. They ar both 0. These variables
are the incremental change to the rows and columns of the range. I then
resize the range with these increment variables aand rename it.
However when I get to the second last line of the code, it says that
my row count = 244. This cannot be due to the fact that my row
increment = 0. It should equal 17520. What am I missing?



Old2DRows = Range("ClearArray").Rows.Count
Old2DColumns = Range("ClearArray").Columns.Count

Changeto2DRows = Old2DRows - AMax
Changeto2DColumns = Old2DColumns - BMax

With Range("ClearArray")
Resize(.Rows.Count + Changeto2DRows).Name = "ClearArray"
Resize(.Columns.Count + Changeto2DColumns).Name = "ClearArray"
End With

New2DRows = Range("ClearArray").Rows.Count
New2DColumns = Range("ClearArray").Columns.Count


---
Message posted from http://www.ExcelForum.com/

Resize Range Problem
 

I've never done anything like this before, but I checked out what you
are talking about in VBA Help and I think you should try it like this:

Old2DRows = Range("ClearArray").Rows.Count
Old2DColumns = Range("ClearArray").Columns.Count

Changeto2DRows = Old2DRows - AMax
Changeto2DColumns = Old2DColumns - BMax

Range("ClearArray").Resize(.Rows.Count + Changeto2Drows, .Columns.Count
+ Changeto2DColumns)

New2DRows = Range("ClearArray").Rows.Count
New2DColumns = Range("ClearArray").Columns.Count

- Pikus


---
Message posted from http://www.ExcelForum.com/

Resize Range Problem
 

Yes this worked
With Range("ClearArray")
..Resize(.Rows.Count + Changeto2DRows, .Columns.Count +
Changeto2DColumns).Name = "ClearArray"
'.Resize(.Columns.Count + Changeto2DColumns).Name = "ClearArray"
End With

I originally had

With Range("ClearArray")
..Resize(.Rows.Count + Changeto2DRows).Name = "ClearArray"
..Resize(.Columns.Count + Changeto2DColumns).Name = "ClearArray"
End With


---
Message posted from http://www.ExcelForum.com/