VBA error 1004 "Select method of Range class failed"
 

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.

VBA error 1004 "Select method of Range class failed"
 

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.


In article ,
Matt J wrote:

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.

VBA error 1004 "Select method of Range class failed"
 

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

"Matt J" wrote:

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.

VBA error 1004 "Select method of Range class failed"
 

Tried it.

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


"JE McGimpsey" wrote:

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.


In article ,
Matt J wrote:

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.

VBA error 1004 "Select method of Range class failed"
 

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


In article ,
Matt J wrote:

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

VBA error 1004 "Select method of Range class failed"
 

No, it is a click event in another sheet

"JE McGimpsey" wrote:

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


In article ,
Matt J wrote:

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

VBA error 1004 "Select method of Range class failed"
 

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.

"Matt J" wrote:

No, it is a click event in another sheet

"JE McGimpsey" wrote:

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


In article ,
Matt J wrote:

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