VBA error 1004 "Select method of Range class failed"

[Matt J]

I have the following code in a Macro for deleting a range of cells and it works fine but when I use it in a click event for a command button I receive the error.

Sheets("SOAUDITSO").Select
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp

I know it is something simple. I am new to VBA so please excuse my ignorance.

 

[JE McGimpsey]

If you're using XL97, you have to set the button's Take Focus on Click
property to False.

But you don't need to do all those selections. Try:

With Sheets("SOAUDITSO").Range("2:2")
Range(.Cells, .Cells.End(xlDown)).Delete
End With

Using the Range objects directly makes your code smaller, faster and,
IMO, easier to maintain.

 

[Matt J]

Sorry for the duplicate I got an error when it was posting

 

[Matt J]

Tried it.

1004 error changed to "Method 'Range' of object_'Worksheet' failed"

 

[JE McGimpsey]

Are you running the code from the same worksheet as Sheet SOAUDITSO?

 

[Matt J]

No, it is a click event in another sheet

Are you running the code from the same worksheet as Sheet SOAUDITSO?

 

[Matt J]

GOT IT!

Took your code and set it into a separate public subprocedure ...
Sheets("SOAUDITITEM").Select
Call PubProcedure.pcdrClearSheet
Application.Goto Reference:="R2C1"

I just select the sheets and call the subprocedure.

thanks for your help.