1004 error when selecting a range

M

Matt J

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
 
R

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
 
M

Matt J

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.
 
M

Matt J

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

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.
 
M

Matt J

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.
 
M

Matt J

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.
 
M

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.
 
Top