• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Setting the size of an array using 'Cells'

P

PipBoy808

Member
Howdy. I'm trying to set a range variable ('IngFore') as equal to a number of rows long and a number of columns wide via 'Cells'. The range needs to be dynamic in that the total number of rows and columns will vary over time. Hence, I've defined 'lastdate' to determine the last row in the array, and 'lastcol' to determine the last column in the array. The starting point for the array is I12.

It would appear that I can't quite get the syntax right. Can anyone give me a hand?

Code: 
Set IngFore = (Cells(12,9 to lastdate,9)),(Cells(12, 9 to 12, lastcol))

I've tinkered with it here and there, removing and adding brackets but I can't get it to work
confused.png
 
Hi, PipBoy808!
Don't be so creative when defining ranges... :p
Try this:
Code: 
Set InfFore = Range(Cells(12, 9), Cells(lastdate, lastcol))
Regards!
 
PipBoy88

You could also do something like
Set InfFore = Cells(12, 9).Resize(10, 5)
or
Set InfFore = Cells(12, 9).Resize(lastDate, lastCol)
 
PipBoy88,

I think this will work as well

Cells(12,9).Select
Set InfFore = Selection.CurrentRegion

with this you do not have to establish the lastrow or cell, provided the range is one contiguous block

Kanti
 
Kanti

There the rarest of exceptions but generally there is no need to select in VBA so your coding effort could be truncated to

Code: 
Set InfFore = Cells(12,9).currentregion

Simple and will do the same. Try and get out of the habbit of using Select as this is a throw back the macro recorder.

Take care

Smallman
 
@kchiba
Hi!
The .CurrentRegion method will only work if the external border cells (row 11 at top, column G at left, row lastdate+1 at bottom and column lastcol+1 at right) are empty.
Regards!
 
You must log in or register to reply here.
Back
Top