Hi,

excel 2k
windows2k

I have multiple sheets in my workbook. An event triggered by a button on a userform runs the routine bellow from sheet1 into sheet4, but the only way it runs is if I use 'sheet4.activate' first and then sheet1.activate at the end. is there a way this can be done without having to move from one sheet to the next and without being seen by the user? in another word, I want the whole thing to happen in the background without ever being noticed by anyone.


Sub eleminate()
Dim vrange As Variant
Dim irowcount As Integer
vrange = Sheet4.Range("A1:J11")
irowcount = UBound(vrange)
Dim a As Integer
Sheet4.Activate
For a = 1 To irowcount
If Sheet4.Range("C" & a).Value = 0 And Sheet4.Range("D" & a).Value = 0 And Sheet4.Range("E" & a).Value = 0 Then
Sheet4.Range("A" & a & ":J" & a).Select
Sheet4.Range("A" & a & ":J" & a).Clear
End If
Next a
Sheet1.Activate



End Sub

What you have there is pretty close to what you need. If you are referring to a worksheet that is not active you can not 'Select' Objects on that sheet. Remember an object has to be active in order to Select it. Additionally you must qualify your statements with the name of the object, when you are referring to a worksheet that is not active, "Sheet4" in this case, which you have. You will find the "With" Statement invaluable for this purpose. See the online exel vb help for details on use of the 'With' statement. Also see my reply in your other post "moving a row upward in excel" where I used 'With' and 'Cells' to reference non-selected objects.

This line in your code: "Sheet4.Range("A" & a & ":J" & a).Select"
Will produce an error if the sheet 'Sheet4' is not active.