Logic arguments in Do loop

[Christian411]

Dear all,

I am trying to put some code together to get Excel to delete lines of my spreadsheet if they meet certain conditions.

There are 2 cells of interest on each line of the spreadsheet. One relates to something called 'module' and the other to something called 'configuration'. Basically I want the line of the spreadsheet deleted if these 2 cells don't meet certain conditions. I have the code below which cycles through the spreadsheet and does this (It works!).

Do Until ActiveCell.Value = ""
    If ActiveCell.Value <> Module Then
        ActiveCell.EntireRow.Delete
        ActiveCell.Offset(-1, 0).Select
    Else
        ActiveCell.Offset(0, 7).Select
        If ActiveCell.Value <> Configuration Then
            ActiveCell.EntireRow.Delete
            ActiveCell.Offset(-1, -7).Select
        End If
    End If
    
    ActiveCell.Offset(1, 0).Select
Loop

My current dilemma concerns the line:

If ActiveCell.Value <> Configuration Then

This cell could actually contain 2 values, i.e. successive lines of the spreadsheet could read as follows:

Configuration A
Configuration B
Configuration D
Configuration C
Configuration A
Configuration A or Configuration C
Configuration D
Configuration A or D
Configuration C

Is there some way I can alter this line of code so it is more along the lines of 'if Activecell.Value contains Configuration Then'??

Thanks,

Chris

[Phil_V]

There is indeed
You can use the "LIKE" comparitor to check a value against a reference that accepts patterns (so you can use wildcards etc).

What isn't clear by this code segment is whether 'module' and 'configuration' are the ACTUAL strings "module", and "Configuration", or whether they are variable names. From your code I have assumed the latter.

You code also does a LOT of unneccasary 'select'-ing and offseting of the activecell.
In VBA it is rarely needed to actually select a cell, and doing so will slow a macro down considerably.

In this first section of code I have taken your original macro, and used the 'LIKE' operator so that you can see how it fits in. On the second code block I have taken the liberty of recoding your function to avoid the 'select' statements, so that you can see how that works too.

Do Until ActiveCell.Value = ""
    If ActiveCell.Value <> Module Then
        ActiveCell.EntireRow.Delete
        ActiveCell.Offset(-1, 0).Select
    Else
        ActiveCell.Offset(0, 7).Select
        ' Delete the row, if the cell doesn't contain the configuration
        If Not ActiveCell.Value Like "*" & Configuration & "*" Then
            ActiveCell.EntireRow.Delete
            ActiveCell.Offset(-1, -7).Select
        End If
    End If
    
    ActiveCell.Offset(1, 0).Select
Loop

Code without the selects and offsets.
NOTE: As your code does, this code also assumes that the activecell is currently in the 'module' column somewhere.:

Dim this_row As Long

' Start at the bottom of the datarange and work up to the activecell
For this_row = ActiveCell.End(xlDown).Row To ActiveCell.Row Step -1
    ' Check both values at the same time using an 'OR'
    If (Cells(this_row, ActiveCell.Column).Value <> Module) Or _
       (Not Cells(this_row, ActiveCell.Column + 7).Value Like "*" & Configuration & "*") Then
       ' if either of the values aren't correct then delete the entire row
       Rows(this_row).EntireRow.Delete
    End If
' move up one row
Next

Note that your original code, (and hence these codes), only operate on the row that the activecell is on, and the rows BELOW it, not the whole list. If it is supposed to operate on the whole list let me know and the change can be made

[Christian411]

Thanks very much Phil.

I haven't managed to get this to work just yet though.

To make things a little clearer:

I have defined 'Configuration' at the start of the code as a string. The value of this string is then defined by a form which can assign it one of the following:

A
B
C
D
E
F

My spreadsheet has a column which contains a value for configuration in each row. A snapshot of this exactly as lifted from my spreadsheet is:

A
A
A
B
B
A or B
B
C
C
C
A or C
B or C

I ran your first piece of code. Using my form I had defined the Configuration I was interested in as 'A'. This was then used in the code to eliminate rows that didn't contain an 'A' in their configuration column. Unfortunately it hasn't worked and I don't know why. All that happended was it deleted all the rows except one which contained an 'A'. There were a lot of other rows that contained an 'A' which were deleted for some reason. Also there were rows that were 'A or B' that were deleted too?

Do you have any ideas where this might be going wrong? I've been going around it myself for a while and can't work it out. I think what you're suggesting with the 'like' operator is definately the way to go, but I just can't get it to work in the way I want it to yet.

Any help would be greatly appreciated!

Cheers,

Chris

[Phil_V]

Where was your cursor when you started the macro? (It HAS to be in the column that has the 'module' data), unless you tell me which column the module data is in.

For each Configuration 'A' row that was deleted, what was the value of 'Module' in that row, and what was the value that you had set 'Module' to?

It might be useful for you to upload an example workbook with some test data in it

[Christian411]

File attached!

Well, I've basically extracted this part of the code and the appropriate parts of the worksheets have been slimmed down to fit the upload size. This workbook and the accompanying code basically still present the same problem though.

The cursor should be in the appropriate column Phil - I have selected the cell at the top of the row in the code before the deleting part of the macro runs.

Thanks again for your help!

Chris

[DonkeyOte]

I would probably approach this a different way...

Regardless of approach you should revise your UF code such that the variables Module & Configuration are both declared as Public, eg:

Config UF

Public Configuration As String
Private Sub CommandButton1_Click
...
End Sub

Selection UF

Public Module As String
Private Sub CommandButton1_Click
...
End Sub

Then in your main code one approach would be:

Public Sub Delete()
Selection.Show
Config.Show
With Range(Cells(2, "A"), Cells(Rows.Count, "A").End(xlUp)).Offset(, Columns.Count - 1)
    .FormulaR1C1 = "=IF(OR(RC4<>""" & Selection.Module & """,ISERR(SEARCH(""" & Config.Configuration & """,RC11))),""x"",0)"
    .SpecialCells(xlCellTypeFormulas, xlTextValues).EntireRow.Delete
    .Clear
End With
End Sub

Unfortunately running Auto Filters could get awkward given you would need to run twice - ie you can't apply both rules simultaneously (as I see it)

[Christian411]

Morning,

This still isn't working unfortunately. I've run the code as you suggest an it gives me an error saying 'object required' on the line that says ".Clear". In addition to this, the macro has deleted all the data in my spreadsheet, and not just the ones we're trying to filter out through use of the formulaR1C1 method.

I've tried playing around with this, but to no avail as yet. I think one problem is I don't fully understand the formulaR1C1 method. I've searched online but can't seem to find anything good that tells me how to go about using this.

Any thoughts on where I can get info on formulaR1C1, or how this macro can be tweaked to work?

As always, any help greatly appreciated!

Chris

[DonkeyOte]

Attached is your file with the recommended revisions - this file worked without incident for me based on your requirements.