Macro Excel 400 erreurs

Question

J'essaie d'exécuter une macro qui supprimera les lignes qui ne contiennent pas une valeur particulière dans la colonne B. Voici mon code :

Sub deleteRows()
    Dim count As Integer
    count = Application.WorksheetFunction.CountA(Range("AF:AF"))
    Dim i As Integer
    i = 21
    Do While i <= count
        If (Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search("OSR Platform", Range("B" & i))) = False) Then
            If (Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search("IAM", Range("B" & i))) = False) Then
                Rows(i).EntireRow.Delete
                i = i - 1
                count = count - 1
            End If
        End If
        i = i + 1
    Loop
End Sub

Ce qu'il DEVRAIT faire, c'est ce qui suit :

1.) Trouvez le nombre de lignes à parcourir et définissez-le en tant que nombre (cela fonctionne).

2.) Commencez à la ligne 21 et cherchez "OSR Platform" et "IAM" dans la colonne B [cela fonctionne en quelque sorte (voir ci-dessous)].

3.) S'il ne trouve ni l'un ni l'autre, supprimez la ligne entière et ajustez le compte et le numéro de ligne si nécessaire (cette méthode fonctionne).

Pour une raison quelconque, chaque fois que le code arrive à la première instruction If, une fenêtre d'erreur avec un X rouge apparaît et dit simplement "400". Pour autant que je puisse en juger, j'ai tout écrit de manière syntaxique, mais il y a clairement quelque chose qui ne va pas.

Answer 1

Vous pouvez commencer par faire une boucle dans l'autre sens. Lorsque vous supprimez une ligne, toutes les lignes précédentes sont décalées. Vous devez en tenir compte, mais une boucle inversée est plus simple ( pour moi en tout cas ) que de garder trace du moment où j'ai décalé la position actuelle dans la boucle :

For i = count To 21 Step -1

Par ailleurs, vous vous appuyez trop sur les Application.WorksheetFunction :

(Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search("OSR Platform", Range("B" & i))) = False)

a

InStr(Range("B" & i).value, "OSR Platform") > 0

Application.WorksheetFunction nécessite beaucoup plus de puissance de traitement et, en fonction de ce que vous essayez d'accomplir, cela peut prendre beaucoup plus de temps. En outre, cette modification suggérée permet de réduire la taille du code et de le rendre plus facile à lire.

Votre count peut également être obtenue sans A.WF :

• Excel 2000/03 : count = Range("AF65536").End(xlUp).Row
• Excel 2007/10 : count = Range("AF1048576").End(xlUp).Row
• Version indépendante : count = Range("AF" & Rows.Count).End(xlUp).Row

Une autre chose est que vous pouvez faire (et devrait dans ce cas) est de combiner votre If en une seule.

En procédant à ces changements, vous vous retrouvez avec :

Sub deleteRows()
    Dim count As Integer
    count = Range("AF" & Rows.Count).End(xlUp).Row
    Dim i As Integer
    For i = count To 21 Step -1
        If Len(Range("B" & i).value) > 0 Then
            If InStr(Range("B" & i).value, "OSR Platform") > 0 Or InStr(Range("B" & i).value, "IAM") > 0 Then
                Range("B" & i).Interior.Color = RGB(255, 0, 0)
            End If
        End If
    Next i
End Sub

Si cela ne suffit pas, vous pouvez examiner le code ligne par ligne. Ajoutez un point d'arrêt et passez à l'étape suivante avec F8 . Mettez en évidence les variables dans votre code, faites un clic droit, choisissez "add Watch...", cliquez sur "OK", ( Voici une excellente ressource pour vous aider dans votre débogage en général ) et noter ce qui suit :

• Quelle est la ligne qui provoque l'erreur ?
• Quelle est la valeur de i y count (ajouter une surveillance sur ces variables pour vous aider).

Answer 2

Cela a fonctionné pour moi. Il utilise AutoFilter, ne nécessite pas de boucles ou de fonctions de feuille de calcul.

Sub DeleteRows()

Dim currentSheet As Excel.Worksheet
Dim rngfilter As Excel.Range
Dim lastrow As Long, lastcolumn As Long

Set currentSheet = ActiveSheet

' get range
lastrow = currentSheet.Cells(Excel.Rows.Count, "AF").End(xlUp).Row
lastcolumn = currentSheet.Cells(1, Excel.Columns.Count).End(xlToLeft).Column
Set rngfilter = currentSheet.Range("A1", currentSheet.Cells(lastrow, lastcolumn))

' filter by column B criteria
rngfilter.AutoFilter Field:=2, Criteria1:="<>*OSR Platform*", Operator:= _
        xlAnd, Criteria2:="<>*IAM*"

' delete any visible row greater than row 21 which does not meet above criteria
rngfilter.Offset(21).SpecialCells(xlCellTypeVisible).EntireRow.Delete

' remove autofilter arrows
currentSheet.AutoFilterMode = False
End Sub

Ce code applique le filtre automatique à la colonne B pour voir quelles lignes ne contiennent ni "OSR Platform" ni "IAM" dans la colonne B. Ensuite, il supprime simplement les lignes restantes supérieures à 21. Testez-le d'abord sur une copie de votre classeur.

Avec un énorme clin d'œil à cette grille OzGrid car je n'arrive jamais à me souvenir de la syntaxe correcte pour sélectionner les cellules visibles après le filtrage.