I am getting the following error when trying to set up a Union of multiple
ranges located on the same sheet.

Run-time error '91': Object variable or With block variable not set.

RO_Wkbk refers the workbook number I am working with.
Here is the code:


Dim RangeYr10 As Range
Dim RangeAllYears As Range

With Workbooks(RO_Wkbk).Sheets("10-Year Analysis")
'''''error occurs on the next line.
RangeAllYears = Union(.Range("F29:AO46"), .Range("F59:AO76"), _
.Range("F89:AO106"), .Range("F119:AO136"), .Range("F149:AO166"), _
.Range("F179:AO196"), .Range("F209:AO226"),
..Range("F239:AO256"), _
.Range("F269:AO286"), .Range("F299:AO316"))
RangeYr10 = .Range("F299:AO316")
End With

I'm use the range later to clear contents based on the color of the cell.

Any thoughts? Thanks,
--
JNW

To follow up... I added Application. in front of union and that did not work
either.
--
JNW


"JNW" wrote:

I am getting the following error when trying to set up a Union of multiple
ranges located on the same sheet.

Run-time error '91': Object variable or With block variable not set.

RO_Wkbk refers the workbook number I am working with.
Here is the code:


Dim RangeYr10 As Range
Dim RangeAllYears As Range

With Workbooks(RO_Wkbk).Sheets("10-Year Analysis")
'''''error occurs on the next line.
RangeAllYears = Union(.Range("F29:AO46"), .Range("F59:AO76"), _
.Range("F89:AO106"), .Range("F119:AO136"), .Range("F149:AO166"), _
.Range("F179:AO196"), .Range("F209:AO226"),
.Range("F239:AO256"), _
.Range("F269:AO286"), .Range("F299:AO316"))
RangeYr10 = .Range("F299:AO316")
End With

I'm use the range later to clear contents based on the color of the cell.

Any thoughts? Thanks,
--
JNW

RangeAllYears =

should be

Set RangeAllYears =

same for

RangeYr10 =

--
Regards,
Tom Ogilvy


"JNW" wrote:

I am getting the following error when trying to set up a Union of multiple
ranges located on the same sheet.

Run-time error '91': Object variable or With block variable not set.

RO_Wkbk refers the workbook number I am working with.
Here is the code:


Dim RangeYr10 As Range
Dim RangeAllYears As Range

With Workbooks(RO_Wkbk).Sheets("10-Year Analysis")
'''''error occurs on the next line.
RangeAllYears = Union(.Range("F29:AO46"), .Range("F59:AO76"), _
.Range("F89:AO106"), .Range("F119:AO136"), .Range("F149:AO166"), _
.Range("F179:AO196"), .Range("F209:AO226"),
.Range("F239:AO256"), _
.Range("F269:AO286"), .Range("F299:AO316"))
RangeYr10 = .Range("F299:AO316")
End With

I'm use the range later to clear contents based on the color of the cell.

Any thoughts? Thanks,
--
JNW

The range variables are objects, so you need to use:

Set RangeAllYears = Union(.. whatever)
and
Set RangeYr10 = .Range("F299:AO316")




JNW wrote:
I am getting the following error when trying to set up a Union of multiple
ranges located on the same sheet.

Run-time error '91': Object variable or With block variable not set.

RO_Wkbk refers the workbook number I am working with.
Here is the code:


Dim RangeYr10 As Range
Dim RangeAllYears As Range

With Workbooks(RO_Wkbk).Sheets("10-Year Analysis")
'''''error occurs on the next line.
RangeAllYears = Union(.Range("F29:AO46"), .Range("F59:AO76"), _
.Range("F89:AO106"), .Range("F119:AO136"), .Range("F149:AO166"), _
.Range("F179:AO196"), .Range("F209:AO226"),
.Range("F239:AO256"), _
.Range("F269:AO286"), .Range("F299:AO316"))
RangeYr10 = .Range("F299:AO316")
End With

I'm use the range later to clear contents based on the color of the cell.

Any thoughts? Thanks,
--
JNW

That did it. Thanks!
--
JNW


"Tom Ogilvy" wrote:

RangeAllYears =

should be

Set RangeAllYears =

same for

RangeYr10 =

--
Regards,
Tom Ogilvy


"JNW" wrote:

I am getting the following error when trying to set up a Union of multiple
ranges located on the same sheet.

Run-time error '91': Object variable or With block variable not set.

RO_Wkbk refers the workbook number I am working with.
Here is the code:


Dim RangeYr10 As Range
Dim RangeAllYears As Range

With Workbooks(RO_Wkbk).Sheets("10-Year Analysis")
'''''error occurs on the next line.
RangeAllYears = Union(.Range("F29:AO46"), .Range("F59:AO76"), _
.Range("F89:AO106"), .Range("F119:AO136"), .Range("F149:AO166"), _
.Range("F179:AO196"), .Range("F209:AO226"),
.Range("F239:AO256"), _
.Range("F269:AO286"), .Range("F299:AO316"))
RangeYr10 = .Range("F299:AO316")
End With

I'm use the range later to clear contents based on the color of the cell.

Any thoughts? Thanks,
--
JNW