VBA Clear Macro Not Working Unless on Filter Criteria Sheet

Question

I built a userform for doing a simple filter search. I can open the form and filter while any worksheet is selected. However, when I use the clear button from the userform but I am not on the filter criteria worksheet (Sheet1) I get a "1004: Select Method of Range class failed" error for the first select statement.

Sub Clear()
'
' Clear Macro
'
    Sheet1.Range("A2:H2").Select
    Selection.ClearContents
    Sheet1.Range("A5:H1725").Select
    Selection.ClearContents
    Sheet1.Range("A2").Select

End Sub

Am I forced to have the filter sheet open or is my code just not working correctly? I wanted to have a sheet with the open form button with the data (Sheet2) and filter criteria (Sheet1) hidden.

On cmdClear_click() I am calling this before running Clear:

Private Sub ClearForm()
'
'clear values
    With Me
        .txtCustId = ""
        .txtCustName = ""
        .txtAddress = ""
        .txtCity = ""
        .txtState = ""
        .txtZip = ""
        .txtCountry = ""
        .txtStatus = ""
    End With
End Sub

The only other thing I can think of that may screw it up is the Me....

In general, you want to [avoid using activate and select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code — cybernetic.nomad, Apr 02 '19 at 16:36
As mentioned it is better to avoid using Activate and Select, however for a quick fix, instead of `Sheet1` can you try `ActiveSheet` like `ActiveSheet.Range(...).Select` Also, do you have this code in a separate Module or inside a Sheet Module? — Ricardo A, Apr 02 '19 at 16:38
@RicardoA: It is in a separate module and not a sheet module.What happens if the form is not looking to the active sheet? — WHC, Apr 02 '19 at 16:44
@cybernetic.nomad: i know of this question and answer but it is beyond my comprehension right now — WHC, Apr 02 '19 at 16:46
Then you avoid using Select, my answer i just posted has it that way, reply to the answer if any issues. — Ricardo A, Apr 02 '19 at 16:47

score 2 · Accepted Answer · answered Apr 02 '19 at 16:44

2

.Select only works if you are on the specified Sheet, in your code you are using Sheet1(..).Select meaning that you are selecting something on Sheet1, if you are on a different Sheet/Tab it will fail. To avoid this, you have to avoid using Select.

Same outcome without Select:

Sub Clear()
'
' Clear Macro
    With ThisWorkbook
        'Replace "Sheet1" if you have renamed your Sheet to something else
        .Sheets("Sheet1").Range("A2:H2").ClearContents
        .Sheets("Sheet1").Range("A5:H1725").ClearContents
    End With
End Sub

answered Apr 02 '19 at 16:44

Ricardo A

1,752
1
10
16

1

If you are only working with Sheet1 you can call the workbook and the sheet together: `With ThisWorkbook.Sheets("Sheet1")` and continue with just `.Range("A2:H2").ClearContents`. Depending on how much you do inside the `With` sometimes its a lot easier/cleaner calling the sheet name too. – Ricardo A Apr 02 '19 at 16:53

VBA Clear Macro Not Working Unless on Filter Criteria Sheet

1 Answers1