Cells function attribute .resize

%3CLINGO-SUB%20id%3D%22lingo-sub-1441865%22%20slang%3D%22en-US%22%3ECells%20function%20attribute%20.resize%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1441865%22%20slang%3D%22en-US%22%3E%3CP%3EI%20found%20this%20line%20that%20works%3C%2FP%3E%3CP%3Ev1%20%3D%20Range(%22J10%22)%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%5Beg%20J10%20%3D%206%5D%3CBR%20%2F%3ERow%20%3D%2010%20-%20v1%20-%201%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%5BRow%20%3D%203%5D%3CBR%20%2F%3Ev%20%3D%20%5B%7B0%3B1%3B2%3B3%3B4%3B5%3B6%3B7%3B%22%22%7D%5D%3CBR%20%2F%3ECells(Row%2C%2010).Resize(9%2C%201)%20%3D%20v%3C%2FP%3E%3CP%3EIt%20alters%20the%20contents%20of%20cells%20in%20column%2010%20from%20rows%204%20to%2011%20to%20'jump'%20up%201%20cell.%3C%2FP%3E%3CP%3EMy%20question%20is%20how%20can%20I%20find%20out%20more%20about%20the%20.resize(9%2C1)%20attributes%3A%20what%20they%20mean%20or%20do%3F%3C%2FP%3E%3CP%3EThe%20Excel%20help%20is%20useless%20for%20this...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1441865%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1441890%22%20slang%3D%22en-US%22%3ERe%3A%20Cells%20function%20attribute%20.resize%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1441890%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F689676%22%20target%3D%22_blank%22%3E%40clonemonkey%3C%2FA%3E%26nbsp%3B%20I'm%20no%20expert%20on%20this%20but%20I%20believe%20.resize%20just%20changes%20the%20range%20selection.%3C%2FP%3E%3CP%3ESo%20in%20your%20example%20you%20start%20with%20a%20single%20cell%20then%20resize%20it%20to%20be%209%20rows%20high%20by%201%20column%20wide%20so%20the%20range%20selection%20went%20from%20cell%20J3%20to%20a%20range%20j3%3Aj11%20and%20then%20you%20assign%20that%20array%20to%20that%20range%20of%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I found this line that works

v1 = Range("J10")                                 [eg J10 = 6]
Row = 10 - v1 - 1                                  [Row = 3]
v = [{0;1;2;3;4;5;6;7;""}]
Cells(Row, 10).Resize(9, 1) = v

It alters the contents of cells in column 10 from rows 4 to 11 to 'jump' up 1 cell.

My question is how can I find out more about the .resize(9,1) attributes: what they mean or do?

The Excel help is useless for this...

1 Reply
Highlighted

@clonemonkey  I'm no expert on this but I believe .resize just changes the range selection.

So in your example you start with a single cell then resize it to be 9 rows high by 1 column wide so the range selection went from cell J3 to a range j3:j11 and then you assign that array to that range of cells.