CODEWITHSUNDEEP
vbaexcel
daviiid
daviiid

Reputation: 15

Delete sheets which DON'T have specific text in

In Excel, I need to delete all sheets which don't end in (2)

The following code currently deletes those which DO end in (2), I'm just not sure how to reverse this, 'Not Like' doesn't seem to work

Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets
    If ws.Name Like "*" & "(2)" Then
        '~~> This check is required to ensure that you don't get an error
        '~~> if there is only one sheet left and it matches the delete criteria
        If ThisWorkbook.Sheets.Count = 1 Then
            MsgBox "There is only one sheet left and you cannot delete it"
        Else
            '~~> This is required to suppress the dialog box which Excel shows
            Application.DisplayAlerts = False
            ws.delete
            Application.DisplayAlerts = True
        End If
    End If
Next

Upvotes: 0

Views: 628

Answers (3)

Brian Camilo Saiz Cavanzo
Brian Camilo Saiz Cavanzo

Reputation: 1

I am using this code to remove sheets that do not contain the word ING in their names.

'Eliminación de hojas que no se van a utilizar
Dim s

'se apagan las alertas del sistema para evitar cuadros emergentes al intentar eliminar las hojas
Application.DisplayAlerts = False

'ciclo que recorre el libro para verificar el nombre de las hojas
For Each s In ActiveWorkbook.Sheets
    'condicion de que si el nombre de la hoja no tiene ING se elimine la hoja
    If Not s.Name Like "ING*" Then
        s.Delete
    End If
Next s

'se vuelven a prender las alertas
Application.DisplayAlerts = True

Upvotes: -1

Tom
Tom

Reputation: 9878

Just a slight tip over your current code. Move the Application.DisplayAlerts outside of your loop. These only need to be turned off and on once in the entire macro, where as you're turning it off an on for each sheet. This increases the number of iterations in your code and bloats your macro.

Also for your original question your if statement runs if the (2) is present in the sheet name. To change this simply put Not after the if.

You can also do this using just one If statement slimming down your code once again. 

Dim ws As Worksheet

Application.DisplayAlerts = False

For Each ws In ThisWorkbook.Sheets
    If Not ws.Name Like "*" & "(2)" and ThisWorkbook.Sheets.Count > 1 Then
       ws.delete
    ElseIf ThisWorkbook.sheets.count = 1 then
        MsgBox "There is only one sheet left and you cannot delete it"
        Exit Sub
    End If
Next ws

Application.DisplayAlerts = True

Upvotes: 0

TechImpossible
TechImpossible

Reputation: 126

Not works fine here, you just have to negate the full expression 

If Not ws.Name Like "*" & "(2)" Then

Upvotes: 1

Related Questions