Range & Cells Property Prob

[tdm100]

Application.Goto _
Reference:=Workbooks("Book1.xls").Sheets("Sheet1").[A1]

'the following 3 lines WORk w/ NO problem
Worksheets("Sheet2").Range("A2:G11").ClearContents
Worksheets("Sheet2").Cells(2, 1) = "HW"
Worksheets("Sheet2").Cells(2, 2).ClearContents

'the next one is giving me a headache
Worksheets("Sheet2").Range(Cells(2, 1), Cells(11, 5)).ClearContents
'Results in an "Application-defined or object-defined error"


I am NOT understanding why that's NOT executing properly

Thanx for your time

[mdmackillop]

You need to directly attach the Sheet to the Cells as in Range(Worksheets("Sheet2").Cells(2, 1), Worksheets("Sheet2").Cells(11, 5)).ClearContents
better written as
With Worksheets("Sheet2")
Range(.Cells(2, 1), .Cells(11, 5)).ClearContents
End With

[rbrhodes]

Hi tdm100,

Or add ".address" to the cells reference as below. Also how about an object and a with to avoid all that typing!



Option Explicit

Sub It()

Dim ws As Worksheet

'Create object
Set ws = Sheets("Sheet2")

'avoid repeated calls to Sheet2
With ws
.Range("A2:G11").ClearContents
.Cells(2, 1) = "HW"
.Cells(2, 2).ClearContents
.Range(Cells(2, 1).Address, Cells(11, 5).Address).ClearContents
End With

'destroy object (optional)
Set ws = nothing
End Sub

[mdmackillop]

Hi tdm100,

Or add ".address" to the cells reference as below. Also how about an object and a with to avoid all that typing!



Option Explicit

Sub It()

Dim ws As Worksheet

'Create object
Set ws = Sheets("Sheet2")

'avoid repeated calls to Sheet2
With ws
.Range("A2:G11").ClearContents
.Cells(2, 1) = "HW"
.Cells(2, 2).ClearContents
.Range(Cells(2, 1).Address, Cells(11, 5).Address).ClearContents
End With

'destroy object (optional)
Set ws = nothing
End Sub


This will work with specific addresses, but will fail if you substitute a dynamic reference such as Set ws = Sheets("Sheet2")
With ws
.Range(Cells(2, 1).Address, Cells(Rows.Count, 5).End(xlUp).Address).ClearContents
End With

as Cells will return the value calculated for the active sheet.