1004 error when selecting a range

[Guest]

I have the follwoing code in a macro and it works fine

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

When I copy it to a ClickEvent for a command button it returns a Run-time 1004 error "Select method of Range class failed"

I'M new to VBA and please excuse me for being so naive

 

[Ron de Bruin]

Hi Matt

If you use Excel 97?

This is a bug in 97

You can put this line in your code(first line)
ActiveCell.Activate

Or Set the SetFocusOnClick property of the button to false
in the properties of the button

 

[Guest]

I'm using Office 2003

I set the focus to false
AND
added ...

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

still get error. Debug indicates 3rd Line (Rows("2:2").Select as location.

 

[JE McGimpsey]

See my reply to one of your posts - there's no need to select.

 

[Guest]

Tried that - didn't work either. If it matters the commandbutton is on a different sheet named (instructions).

 

[Ron de Bruin]

Hi Matt

J.E already told you that you don't have to select.
I test your old code with selecting in 2003 and have no problems.

 

[Guest]

Sorry but his suggestion did not work on my machine. Must be something else wrong. I do have Visual Studio 2003 installed, possibly it updated some of the imbedded VBA code causing it to act different, if that is possible.

I will try it on another machine.

 

[Guest]

Tried both my original code and JE's code on a different machine running Excel 2000. Still get the same erorr.

I am very new to this. Is there something basic I am missing? Please don't assume there is any other code to this axcept the commandbutton.

 

[JE McGimpsey]

Doesn't for me as long as they're in the same workbook.

 

[Guest]

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.