use

Dim rng as Range
Set rng = Range("A1").CurrentRegion

with rng
etc.


"Frederick Chow" wrote:

> Hi all,
>
> Suppose originally I have the following code:
>
> Range("A1").CurrentRegion.Cells(1,1) = Range("A1").CurrentRegion.Cells(1,2)
> + 1
> Range("A1").CurrentRegion.Cells(1,1).font.bold = True
>
> Now I want to simplify the two lines with WITH block:
>
> With Range("A1").CurrentRegion
> With .Cells(1,1)
> .Value = .Cells(1,2) + 1 'This line will fail
> .Font.Bold = True
> End With
> End With
>
> Of course it won't work. To circumvent the problem, I found I have to do
> this:
>
> With Range("A1").CurrentRegion
> With .Cells(1,1)
> .Value = Range("A1").CurrentRegion.Cells(1,2) + 1 'Troublesome
> workaround
> .Font.Bold = True
> End With
> End With
>
> However, I found my workaround really troublesome. Is there any simpler
> approach than my current troublesome workround? I found that the "Parent"
> keyword won't do the job. Thanks for your advice.
>
> Frederick Chow
> Hong Kong
>
>
>