Refer to Ranges using Cells notation

Hi,

I am trying to pass through a worksheet using VBA with a nested For loop and
would like to refer to the cells that I pass through using the Cells(row,
col) notation. Is there a way to refer to a range of cells (e.g. A1:A100)
using the Cells(row, col) notation instead of having to use the Range
notation?

Thanks in advance.

There are several ways. One is

Set rng = Range("A1")
MsgBox Range(rng(1),rng(100)).Address

It will display $A$1:$A$100

Alan Beban

Scott P wrote:
> Hi,
>
> I am trying to pass through a worksheet using VBA with a nested For loop and
> would like to refer to the cells that I pass through using the Cells(row,
> col) notation. Is there a way to refer to a range of cells (e.g. A1:A100)
> using the Cells(row, col) notation instead of having to use the Range
> notation?
>
> Thanks in advance.

I believe that I have found the answer to my own question, so I am posting it
here. It appears Excel allows you to refer to ranges of cells using the
"cells" notation using syntax such as this:

Set rng = Range(Cells(1, 1), Cells(1, 100))



"Scott P" wrote:

> Hi,
>
> I am trying to pass through a worksheet using VBA with a nested For loop and
> would like to refer to the cells that I pass through using the Cells(row,
> col) notation. Is there a way to refer to a range of cells (e.g. A1:A100)
> using the Cells(row, col) notation instead of having to use the Range
> notation?
>
> Thanks in advance.

with activesheet
set rng = range(.cells(1,1),.cells(100,1))
'or
set rng = .cells(1,1).resize(100,1)
end with

Tim

"Scott P" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I am trying to pass through a worksheet using VBA with a nested For
> loop and
> would like to refer to the cells that I pass through using the
> Cells(row,
> col) notation. Is there a way to refer to a range of cells (e.g.
> A1:A100)
> using the Cells(row, col) notation instead of having to use the
> Range
> notation?
>
> Thanks in advance.