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.

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.

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.

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.

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"

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"

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"