Problems With If ... Then ... With

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,562
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Code:
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
your code would need to follow the following structure (example for 1st listed worksheet only).

Code:
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?
 
Upvote 0
Jenn

Why do you have a dot qualifier ( . ) in front of the Withs, Ifs and Ends?:eek:
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top