Problems With If ... Then ... With

[Ark68]

Hi Everyone.

I'm having a hard time with the code below. It is designed to check several worksheets to see if that worksheet data is being influenced by an advanced filter, and if it is, to cancel it and display all the data. Once displayed it will delete Rows 2-6000 of that sheet. (purging data)

I am receiving compile errors "Expected: End of statement" with the "THEN" after Filtermode, and compile errors "Expected: expression" after .End

With Workbooks("Rental_Main.xls")
    .With Worksheets("Diamonds_Regular")
        .if FilterMode [color=red]then[/color] ShowAllData
        .Range("A2:K6000").Delete
    .End [color=red]With[/color]
    .With Worksheets("Diamonds_Tournament")
        .if FilterMode [color=red]then[/color] ShowAllData
        .Range("A2:K6000").Delete
    .End [color=red]With[/color]
    .WithWorksheets ("Fields_Regular")
         .if FilterMode [color=red]then[/color] ShowAllData
         .Range("A2:K6000").Delete
    .End [color=red]With[/color]
    .With Worksheets("Fields_Tournament")
         .if FilterMode [color=red]then[/color] ShowAllData
         .Range("A2:K6000").Delete
    .End [color=red]With[/color]
    .With Worksheets("Courts_Regular")
          .if FilterMode [color=red]then[/color] ShowAllData
          .Range("A2:K6000").Delete
    .End [color=red]With[/color]
    .With Worksheets("Courts_Tournament")
          .if FilterMode [color=red]then[/color] ShowAllData
          .Range("A2:K6000").Delete
    .End [color=red]With[/color]
End With

Thanks in advance.

Jenn

 

[Seti]

Try getting rid of the period before the .if

 

[yytsunamiyy]

your code would need to follow the following structure (example for 1st listed worksheet only).

Sub foo()

With Workbooks("Rental_Main.xls")
    If .Worksheets("Diamonds_Regular").FilterMode Then
        .Worksheets("Diamonds_Regular").ShowAllData
        .Worksheets("Diamonds_Regular").Range("A2:K6000").Delete
    End If
End With


End Sub

Your code can possibly by easily tightened up, if you are willing to answer a few questions:

Any particular reason why only line 2 - 6000? are you simply trying to clear all Data minus the column headers?

Are the 6 sheets listed in your code all the sheets you work with?
Are there any other sheets in the workbook?
If so, are there any sheets that have, as part of their name, _Tournament or _regular?

 

[Norie]

Jenn

Why do you have a dot qualifier ( . ) in front of the Withs, Ifs and Ends?

 

[Ark68]

Thanks for your replies!

Norie, answer to your question is easy. I don't know. I had adapted the code from another similar routine that worked. I'm not formally versed in VB so have no idea the purpose of (.) ... I've been learning trial and error (and good help to!).

yytsunamiyy ... you suggestion worked although I had to remove the (.) preceeding "Worksheets". As mentioned, my limited VB knowledge doesn't always make for a pretty or efficient code. Right now, it's all about function! If it works, I'm not going to experiment. To answer your questions ...

1) Line 2-6000 is, yes, meant to delete all the rows with the exception of the header row.
2) The 6 sheets are part of a 10 sheet workbook. Each sheet has to hold uniquely filtered data. Their is a master datasheet which copies ranges of advanced filtered data to each specific worksheet. In some cases, further filtering is done at the target worksheets.
3) The additional sheets do not have _Regular or _Tournament in their names.

Thanks for all you help!!!

Jenn