Confused, help with this line please!

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
[email protected]
"serdar" <[email protected]> wrote in message
news:[email protected]...
> 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 )
>
>

i need to use variables instead of "a9:f9" but this helped me, thanks:

http://www.xtremevbtalk.com/archive/.../t-225724.html






"Don Guillett" <[email protected]>, haber iletisinde şunları
yazdı:[email protected]...
> try it this way
> Sheets(3).Range("a9:f9").Delete Shift:=xlUp
>
> Don Guillett
> SalesAid Software
> [email protected]

Checked forums on the net. This works.

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


"serdar" <[email protected]>, haber iletisinde şunları
yazdı:[email protected]...
> 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 )
>
>

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






"serdar" <[email protected]> wrote in message
news:[email protected]...
> 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 )
>
>

So will

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

not tested
Alan Beban

serdar wrote:
> Checked forums on the net. This works.
>
> Worksheets(3).Range(Worksheets(3).Cells(9, 1), Worksheets(3).Cells(9,
> 6)).Delete Shift:=xlUp
>
>
> "serdar" <[email protected]>, haber iletisinde şunları
> yazdı:[email protected]...
>
>>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 )
>>
>>
>
>
>