Confused, help with this line please!

S

serdar

Range(Cells(9, 1), Cells(9, 6)).Delete Shift:=xlUp

works fine, but

Worksheets(3).Range(Cells(9, 1), Cells(9, 6)).Delete Shift:=xlUp

gives a runtime error 1004 message.

why? ( function is in worksheet 1 )
 
S

serdar

Checked forums on the net. This works.

Worksheets(3).Range(Worksheets(3).Cells(9, 1), Worksheets(3).Cells(9,
6)).Delete Shift:=xlUp
 
C

Chip Pearson

The reason is that the you have the code in the Sheet(1) code
module. Thus, the Cells references refer to the cells on Sheet1,
while you are attempting to create a range on Sheet(3). A range
cannot span more than one worksheet. Instead, try

With Worksheets(3)
.Range(.Cells(9,1),.Cells(9,6)).Delete shift:=xlUp
End With

Note the leading periods in 'Range' and 'Cells'.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
A

Alan Beban

So will

Set rng = Worksheets(3).Range("A1")
Range(rng(9,1),rng(9,6)).Delete Shift:=xlUp

not tested
Alan Beban
 
Top