using ranges on non-active sheets

[Bukovets]

Suppose you want to clear a region of a workbook, whose location is to be determined at run-time.

The obvious code is:

Sub clear1(ws as worksheet, i as integer, j as integer, k as integer, l as integer)
ws.range(cells(i,j),cells(k,l)).clearContents
End Sub

This gives error number 1004 (the catch-all with no explanation) if ws is not the active worksheet when clear1 is called.

The work-round is

Sub clear2(ws as worksheet, i as integer, j as integer, k as integer, l as integer)
Dim cell1 as range, cell2 as Range
Set cell1 = ws.range(cells(i, j))
Set cell2 = ws.range(cells(j, k))
ws.range(cell1,cell2)).clearContents
End Sub

Sub clear2 always works. Why is it necessary and how does it work?

[Andy Pope]

Just fully qualify the range.

Sub clear1(ws As Worksheet, i As Integer, j As Integer, k As Integer, l As Integer)
ws.Range(ws.Cells(i, j), ws.Cells(k, l)).ClearContents
End Sub

Note use Long instead of Integer in order to reach the end rows of a worksheet

[Andy Pope]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Oops forgot to mention....