Confused, help with this line please!

  • Thread starter Thread starter serdar
  • Start date Start date
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 )
 
try it this way
Sheets(3).Range("a9:f9").Delete Shift:=xlUp

Don Guillett
SalesAid Software
(e-mail address removed)
 
Checked forums on the net. This works.

Worksheets(3).Range(Worksheets(3).Cells(9, 1), Worksheets(3).Cells(9,
6)).Delete Shift:=xlUp
 
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
 
So will

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

not tested
Alan Beban
 
Back
Top