Error on deletion code doesn't make sense

[bdmangum]

I keep getting a "Run-time error '1004': Application-defined or object-defined error" at this line of code:

Sheets(4).Range(Cells((WVar + 2), SVar), Cells((WVar + 2 + Delete_SSCounter), (SVar + 1))).delete Shift:=xlToLeft

At the time of the error WVar = 151, SVar = 3, Delete_SSCounter = 1.

And if it makes any difference, though it shouldn't, the SVar+1 column is hidden.

I can't see why I'm getting an error at this point. Any thoughts? Am I just missing an obvious coding error?

 

[xlbo]

merged cells somewhere ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[bdmangum]

No, none of the cells are merged. This range selects four cells. Three of those contain data, but aren't merged or anything.

 

[PHV]

Perhaps this ?
Sheets(4).Range(Sheets(4).Cells((WVar + 2), SVar), Sheets(4).Cells((WVar + 2 + Delete_SSCounter), (SVar + 1))).Delete Shift:=xlToLeft

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bdmangum]

Thanks PH! That worked.

Could you explain to me why the extra refences to Sheet4 are needed within the range?

 

[PHV]

Because a non qualified Cells is the same as ActiveSheet.Cells, and I guessed tha Sheets(4) wasn't the active sheet ...
Bottom line, always qualify your Range and Cells objects ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bdmangum]

Ok, that makes sense. I never thought that the .Cells simply was the same as ActiveSheet.Cells. Thanks for the help!

 

[xlbo]

common mistake - you didn't have .Cells - you had Cells. A small point but important.

Easier to demonstrate in a with structure:

You need:

WITH Sheets(4)
.Range(.Cells((WVar + 2), SVar), .Cells((WVar + 2 + Delete_SSCounter), (SVar + 1))).Delete Shift:=xlToLeft
END WITH

You Had the equivalent of:

WITH Sheets(4)
.Range(Cells((WVar + 2), SVar), Cells((WVar + 2 + Delete_SSCounter), (SVar + 1))).Delete Shift:=xlToLeft
END WITH


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question