Limit to Number of Cells a Range Variable Can Hold

[Jeffrey Mahoney]

I'm finding different answers on the internet. Are the number of cells I can alter at once directly related to the amount of RAM on the computer?

I was trying to use this code, but it gave me an out of memory error on the second line

Set Rng = Sht.Range(TL, TL.Offset(1000000, 700))
Rng.Value = Rng.Value2

I use this code and it's fine

Set Rng = Sht.Range(TL, TL.Offset(2000, 200))
Rng.Value = Rng.Value2

 

[thirty_two]

I have no specific answer, but the documentation for Ranges has descriptions for the ".Count" and ".CountLarge" properties. In the latter, it says:

The CountLarge property is functionally the same as the Count property, except that the Count property will generate an overflow error if the specified range has more than 2,147,483,647 cells (one less than 2,048 columns). The CountLarge property, however, can handle ranges up to the maximum size for a worksheet, which is 17,179,869,184 cells.

So, based on that statement, you should be able to have Ranges which encompass the entire sheet. Though, as you've already stated, machine memory may be the true limiting factor.