Solved: Help with resize

[xluser2007]

Hi All,

I am doing the following:
[vba]
Option Explicit

Sub test()

ThisWorkbook.Worksheets("Expected").Range("T1").Resize(1, -16).calculate

End Sub[/vba]
That is, select the relevant region with respect to T1 and calculate it.

However, I will be requiring to calculate this many times in a loop and wanted to SET this calculation range in the macro as follows:

[vba]Option Explicit

Sub test()

Dim rng As Range

Set rng = ThisWorkbook.Worksheets("Expected").Range("T1").Resize(1, -16)

' Then do more steps here and then recalculate range

rng.Calculate

End Sub[/vba]
The red throws an '1004' error Application defined or object defined error.

Ani ideas on how to correct for this as optimally as possible (i.e. without selecting the rng)?

Any help appreciated.

[Zack Barresse]

You have to resize from the other direction (right/down), so just Offset it to the left, then resize to the right...

[vba]Option Explicit

Sub test()

Dim rng As Range

Set rng = ThisWorkbook.Worksheets("Expected").Range("T1").Offset(0, -15).Resize(1, 16)

' Then do more steps here and then recalculate range

rng.Calculate

End Sub[/vba]

HTH

[xluser2007]

You have to resize from the other direction (right/down), so just Offset it to the left, then resize to the right...

[vba]Option Explicit

Sub test()

Dim rng As Range

Set rng = ThisWorkbook.Worksheets("Expected").Range("T1").Offset(0, -15).Resize(1, 16)

' Then do more steps here and then recalculate range

rng.Calculate

End Sub[/vba]
HTH

Works a treat Zack, appreciate your help !