Resize Range Problem

  • Thread starter Thread starter ExcelMonkey
  • Start date Start date
E

ExcelMonkey

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
 
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
 
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
 
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
 
Back
Top