Astuces Excel
  1. Astuces Excel
  3. Looping through rows dans un ListObject pour les supprimer est très lent
Intereting Posts
Ajouter de la couleur avec VBA à une ligne entière d'Excel Erreur de fonction SendKeys Ajouter un numéro de page d'excel à word, VBA Comment faire pour que VLOOKUP sélectionne la ligne la plus basse dans VBA? Excel VBA Ouvrez le classur, effectuez des actions, enregistrez, fermez Pouvons-nous empêcher une cellule d'une feuille excel de supprimer une opération? Formules de masortingce nestedes Convertir la fonction Excel "If" en requête T-SQL VBA, si la cellule active dans la plage ne correspond pas à une macro d'appel de string quel est le sens de ce code VBA Sumifs avec des critères de tableau? Vérification d'URL dans l'erreur VBA Vérifier si la cellule sélectionnée est dans une plage spécifique Comment modifier la plage dans le sous-set après avoir copié et collé datatables sur une nouvelle feuille Ajouter des valeurs dans la colonne excel pour des valeurs distinctes dans une autre colonne

Looping through rows dans un ListObject pour les supprimer est très lent

J'ai une table ListObject avec ~ 500 lignes, j'ai également 4 valeurs dans une gamme nommée.

Il existe peut-être 30 valeurs uniques qui se produisent à plusieurs resockets (Au hasard) pour les 500 lignes, je veux supprimer toutes les lignes dont les valeurs ne sont pas dans la plage nommée.

J'ai le suivant qui fonctionne, mais il court plus lentement que prévu (environ 2 min): 

Sub removeAccounts() Dim tbl As ListObject Dim i As Integer Set tbl = ThisWorkbook.Sheets("TheSheet").ListObjects("TheTable") i = tbl.ListRows.Count While i > 0 If Application.WorksheetFunction.CountIf(Range("Included_Rows"), tbl.ListRows(i).Range.Cells(1).Value) = 0 Then tbl.ListRows(i).Delete End If i = i - 1 Wend End Sub

Je ne suis pas sûr de savoir si c'est la confiance dans la fonction de la feuille de calcul ou simplement la boucle dans les lignes qui ralentit.

Existe-t-il un moyen de filterr le listobject et de jeter le rest?

J'étais en train de choisir une barre de progression pour que les users puissent voir quelque chose se produire …

    Essayez ce code: 

     Sub removeAccounts() Dim tbl As ListObject Dim i As Long Dim uRng As Range Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual Set tbl = ThisWorkbook.Sheets("TheSheet").ListObjects("TheTable") i = tbl.ListRows.Count While i > 0 If Application.WorksheetFunction.CountIf(Range("Included_Rows"), tbl.ListRows(i).Range.Cells(1).Value) = 0 Then 'tbl.ListRows(i).Delete If uRng Is Nothing Then Set uRng = tbl.ListRows(i).Range Else Set uRng = Union(uRng, tbl.ListRows(i).Range) End If End If i = i - 1 Wend If Not uRng Is Nothing Then uRng.Delete xlUp Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub Sub removeAccounts () Sub removeAccounts() Dim tbl As ListObject Dim i As Long Dim uRng As Range Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual Set tbl = ThisWorkbook.Sheets("TheSheet").ListObjects("TheTable") i = tbl.ListRows.Count While i > 0 If Application.WorksheetFunction.CountIf(Range("Included_Rows"), tbl.ListRows(i).Range.Cells(1).Value) = 0 Then 'tbl.ListRows(i).Delete If uRng Is Nothing Then Set uRng = tbl.ListRows(i).Range Else Set uRng = Union(uRng, tbl.ListRows(i).Range) End If End If i = i - 1 Wend If Not uRng Is Nothing Then uRng.Delete xlUp Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub Dim tbl As ListObject Sub removeAccounts() Dim tbl As ListObject Dim i As Long Dim uRng As Range Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual Set tbl = ThisWorkbook.Sheets("TheSheet").ListObjects("TheTable") i = tbl.ListRows.Count While i > 0 If Application.WorksheetFunction.CountIf(Range("Included_Rows"), tbl.ListRows(i).Range.Cells(1).Value) = 0 Then 'tbl.ListRows(i).Delete If uRng Is Nothing Then Set uRng = tbl.ListRows(i).Range Else Set uRng = Union(uRng, tbl.ListRows(i).Range) End If End If i = i - 1 Wend If Not uRng Is Nothing Then uRng.Delete xlUp Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub Dim i As Long Sub removeAccounts() Dim tbl As ListObject Dim i As Long Dim uRng As Range Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual Set tbl = ThisWorkbook.Sheets("TheSheet").ListObjects("TheTable") i = tbl.ListRows.Count While i > 0 If Application.WorksheetFunction.CountIf(Range("Included_Rows"), tbl.ListRows(i).Range.Cells(1).Value) = 0 Then 'tbl.ListRows(i).Delete If uRng Is Nothing Then Set uRng = tbl.ListRows(i).Range Else Set uRng = Union(uRng, tbl.ListRows(i).Range) End If End If i = i - 1 Wend If Not uRng Is Nothing Then uRng.Delete xlUp Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub Dim uRng As Range Sub removeAccounts() Dim tbl As ListObject Dim i As Long Dim uRng As Range Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual Set tbl = ThisWorkbook.Sheets("TheSheet").ListObjects("TheTable") i = tbl.ListRows.Count While i > 0 If Application.WorksheetFunction.CountIf(Range("Included_Rows"), tbl.ListRows(i).Range.Cells(1).Value) = 0 Then 'tbl.ListRows(i).Delete If uRng Is Nothing Then Set uRng = tbl.ListRows(i).Range Else Set uRng = Union(uRng, tbl.ListRows(i).Range) End If End If i = i - 1 Wend If Not uRng Is Nothing Then uRng.Delete xlUp Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub Application.ScreenUpdating = False Sub removeAccounts() Dim tbl As ListObject Dim i As Long Dim uRng As Range Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual Set tbl = ThisWorkbook.Sheets("TheSheet").ListObjects("TheTable") i = tbl.ListRows.Count While i > 0 If Application.WorksheetFunction.CountIf(Range("Included_Rows"), tbl.ListRows(i).Range.Cells(1).Value) = 0 Then 'tbl.ListRows(i).Delete If uRng Is Nothing Then Set uRng = tbl.ListRows(i).Range Else Set uRng = Union(uRng, tbl.ListRows(i).Range) End If End If i = i - 1 Wend If Not uRng Is Nothing Then uRng.Delete xlUp Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub Application.EnableEvents = Faux Sub removeAccounts() Dim tbl As ListObject Dim i As Long Dim uRng As Range Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual Set tbl = ThisWorkbook.Sheets("TheSheet").ListObjects("TheTable") i = tbl.ListRows.Count While i > 0 If Application.WorksheetFunction.CountIf(Range("Included_Rows"), tbl.ListRows(i).Range.Cells(1).Value) = 0 Then 'tbl.ListRows(i).Delete If uRng Is Nothing Then Set uRng = tbl.ListRows(i).Range Else Set uRng = Union(uRng, tbl.ListRows(i).Range) End If End If i = i - 1 Wend If Not uRng Is Nothing Then uRng.Delete xlUp Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub Application.Calculation = xlCalculationManual Sub removeAccounts() Dim tbl As ListObject Dim i As Long Dim uRng As Range Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual Set tbl = ThisWorkbook.Sheets("TheSheet").ListObjects("TheTable") i = tbl.ListRows.Count While i > 0 If Application.WorksheetFunction.CountIf(Range("Included_Rows"), tbl.ListRows(i).Range.Cells(1).Value) = 0 Then 'tbl.ListRows(i).Delete If uRng Is Nothing Then Set uRng = tbl.ListRows(i).Range Else Set uRng = Union(uRng, tbl.ListRows(i).Range) End If End If i = i - 1 Wend If Not uRng Is Nothing Then uRng.Delete xlUp Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub Définissez tbl = ThisWorkbook.Sheets ("TheSheet"). ListObjects ("TheTable") Sub removeAccounts() Dim tbl As ListObject Dim i As Long Dim uRng As Range Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual Set tbl = ThisWorkbook.Sheets("TheSheet").ListObjects("TheTable") i = tbl.ListRows.Count While i > 0 If Application.WorksheetFunction.CountIf(Range("Included_Rows"), tbl.ListRows(i).Range.Cells(1).Value) = 0 Then 'tbl.ListRows(i).Delete If uRng Is Nothing Then Set uRng = tbl.ListRows(i).Range Else Set uRng = Union(uRng, tbl.ListRows(i).Range) End If End If i = i - 1 Wend If Not uRng Is Nothing Then uRng.Delete xlUp Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub i = tbl.ListRows.Count Sub removeAccounts() Dim tbl As ListObject Dim i As Long Dim uRng As Range Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual Set tbl = ThisWorkbook.Sheets("TheSheet").ListObjects("TheTable") i = tbl.ListRows.Count While i > 0 If Application.WorksheetFunction.CountIf(Range("Included_Rows"), tbl.ListRows(i).Range.Cells(1).Value) = 0 Then 'tbl.ListRows(i).Delete If uRng Is Nothing Then Set uRng = tbl.ListRows(i).Range Else Set uRng = Union(uRng, tbl.ListRows(i).Range) End If End If i = i - 1 Wend If Not uRng Is Nothing Then uRng.Delete xlUp Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub Alors que je> 0 Sub removeAccounts() Dim tbl As ListObject Dim i As Long Dim uRng As Range Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual Set tbl = ThisWorkbook.Sheets("TheSheet").ListObjects("TheTable") i = tbl.ListRows.Count While i > 0 If Application.WorksheetFunction.CountIf(Range("Included_Rows"), tbl.ListRows(i).Range.Cells(1).Value) = 0 Then 'tbl.ListRows(i).Delete If uRng Is Nothing Then Set uRng = tbl.ListRows(i).Range Else Set uRng = Union(uRng, tbl.ListRows(i).Range) End If End If i = i - 1 Wend If Not uRng Is Nothing Then uRng.Delete xlUp Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub Si Application.WorksheetFunction.CountIf (Range ("Included_Rows"), tbl.ListRows (i) .Range.Cells (1) .Value) = 0 Then Sub removeAccounts() Dim tbl As ListObject Dim i As Long Dim uRng As Range Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual Set tbl = ThisWorkbook.Sheets("TheSheet").ListObjects("TheTable") i = tbl.ListRows.Count While i > 0 If Application.WorksheetFunction.CountIf(Range("Included_Rows"), tbl.ListRows(i).Range.Cells(1).Value) = 0 Then 'tbl.ListRows(i).Delete If uRng Is Nothing Then Set uRng = tbl.ListRows(i).Range Else Set uRng = Union(uRng, tbl.ListRows(i).Range) End If End If i = i - 1 Wend If Not uRng Is Nothing Then uRng.Delete xlUp Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub 'tbl.ListRows (i). Supprimer Sub removeAccounts() Dim tbl As ListObject Dim i As Long Dim uRng As Range Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual Set tbl = ThisWorkbook.Sheets("TheSheet").ListObjects("TheTable") i = tbl.ListRows.Count While i > 0 If Application.WorksheetFunction.CountIf(Range("Included_Rows"), tbl.ListRows(i).Range.Cells(1).Value) = 0 Then 'tbl.ListRows(i).Delete If uRng Is Nothing Then Set uRng = tbl.ListRows(i).Range Else Set uRng = Union(uRng, tbl.ListRows(i).Range) End If End If i = i - 1 Wend If Not uRng Is Nothing Then uRng.Delete xlUp Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub Si uRng n'est rien alors Sub removeAccounts() Dim tbl As ListObject Dim i As Long Dim uRng As Range Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual Set tbl = ThisWorkbook.Sheets("TheSheet").ListObjects("TheTable") i = tbl.ListRows.Count While i > 0 If Application.WorksheetFunction.CountIf(Range("Included_Rows"), tbl.ListRows(i).Range.Cells(1).Value) = 0 Then 'tbl.ListRows(i).Delete If uRng Is Nothing Then Set uRng = tbl.ListRows(i).Range Else Set uRng = Union(uRng, tbl.ListRows(i).Range) End If End If i = i - 1 Wend If Not uRng Is Nothing Then uRng.Delete xlUp Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub Définissez uRng = tbl.ListRows (i) .Range Sub removeAccounts() Dim tbl As ListObject Dim i As Long Dim uRng As Range Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual Set tbl = ThisWorkbook.Sheets("TheSheet").ListObjects("TheTable") i = tbl.ListRows.Count While i > 0 If Application.WorksheetFunction.CountIf(Range("Included_Rows"), tbl.ListRows(i).Range.Cells(1).Value) = 0 Then 'tbl.ListRows(i).Delete If uRng Is Nothing Then Set uRng = tbl.ListRows(i).Range Else Set uRng = Union(uRng, tbl.ListRows(i).Range) End If End If i = i - 1 Wend If Not uRng Is Nothing Then uRng.Delete xlUp Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub Set uRng = Union (uRng, tbl.ListRows (i) .Range) Sub removeAccounts() Dim tbl As ListObject Dim i As Long Dim uRng As Range Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual Set tbl = ThisWorkbook.Sheets("TheSheet").ListObjects("TheTable") i = tbl.ListRows.Count While i > 0 If Application.WorksheetFunction.CountIf(Range("Included_Rows"), tbl.ListRows(i).Range.Cells(1).Value) = 0 Then 'tbl.ListRows(i).Delete If uRng Is Nothing Then Set uRng = tbl.ListRows(i).Range Else Set uRng = Union(uRng, tbl.ListRows(i).Range) End If End If i = i - 1 Wend If Not uRng Is Nothing Then uRng.Delete xlUp Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub i = i - 1 Sub removeAccounts() Dim tbl As ListObject Dim i As Long Dim uRng As Range Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual Set tbl = ThisWorkbook.Sheets("TheSheet").ListObjects("TheTable") i = tbl.ListRows.Count While i > 0 If Application.WorksheetFunction.CountIf(Range("Included_Rows"), tbl.ListRows(i).Range.Cells(1).Value) = 0 Then 'tbl.ListRows(i).Delete If uRng Is Nothing Then Set uRng = tbl.ListRows(i).Range Else Set uRng = Union(uRng, tbl.ListRows(i).Range) End If End If i = i - 1 Wend If Not uRng Is Nothing Then uRng.Delete xlUp Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub Wend Sub removeAccounts() Dim tbl As ListObject Dim i As Long Dim uRng As Range Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual Set tbl = ThisWorkbook.Sheets("TheSheet").ListObjects("TheTable") i = tbl.ListRows.Count While i > 0 If Application.WorksheetFunction.CountIf(Range("Included_Rows"), tbl.ListRows(i).Range.Cells(1).Value) = 0 Then 'tbl.ListRows(i).Delete If uRng Is Nothing Then Set uRng = tbl.ListRows(i).Range Else Set uRng = Union(uRng, tbl.ListRows(i).Range) End If End If i = i - 1 Wend If Not uRng Is Nothing Then uRng.Delete xlUp Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub Si Not uRng n'est rien, puis uRng.Delete xlUp Sub removeAccounts() Dim tbl As ListObject Dim i As Long Dim uRng As Range Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual Set tbl = ThisWorkbook.Sheets("TheSheet").ListObjects("TheTable") i = tbl.ListRows.Count While i > 0 If Application.WorksheetFunction.CountIf(Range("Included_Rows"), tbl.ListRows(i).Range.Cells(1).Value) = 0 Then 'tbl.ListRows(i).Delete If uRng Is Nothing Then Set uRng = tbl.ListRows(i).Range Else Set uRng = Union(uRng, tbl.ListRows(i).Range) End If End If i = i - 1 Wend If Not uRng Is Nothing Then uRng.Delete xlUp Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub Application.ScreenUpdating = True Sub removeAccounts() Dim tbl As ListObject Dim i As Long Dim uRng As Range Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual Set tbl = ThisWorkbook.Sheets("TheSheet").ListObjects("TheTable") i = tbl.ListRows.Count While i > 0 If Application.WorksheetFunction.CountIf(Range("Included_Rows"), tbl.ListRows(i).Range.Cells(1).Value) = 0 Then 'tbl.ListRows(i).Delete If uRng Is Nothing Then Set uRng = tbl.ListRows(i).Range Else Set uRng = Union(uRng, tbl.ListRows(i).Range) End If End If i = i - 1 Wend If Not uRng Is Nothing Then uRng.Delete xlUp Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub Application.EnableEvents = True Sub removeAccounts() Dim tbl As ListObject Dim i As Long Dim uRng As Range Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual Set tbl = ThisWorkbook.Sheets("TheSheet").ListObjects("TheTable") i = tbl.ListRows.Count While i > 0 If Application.WorksheetFunction.CountIf(Range("Included_Rows"), tbl.ListRows(i).Range.Cells(1).Value) = 0 Then 'tbl.ListRows(i).Delete If uRng Is Nothing Then Set uRng = tbl.ListRows(i).Range Else Set uRng = Union(uRng, tbl.ListRows(i).Range) End If End If i = i - 1 Wend If Not uRng Is Nothing Then uRng.Delete xlUp Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub Application.Calculation = xlCalculationAutomatic Sub removeAccounts() Dim tbl As ListObject Dim i As Long Dim uRng As Range Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual Set tbl = ThisWorkbook.Sheets("TheSheet").ListObjects("TheTable") i = tbl.ListRows.Count While i > 0 If Application.WorksheetFunction.CountIf(Range("Included_Rows"), tbl.ListRows(i).Range.Cells(1).Value) = 0 Then 'tbl.ListRows(i).Delete If uRng Is Nothing Then Set uRng = tbl.ListRows(i).Range Else Set uRng = Union(uRng, tbl.ListRows(i).Range) End If End If i = i - 1 Wend If Not uRng Is Nothing Then uRng.Delete xlUp Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub

    Votre problème n'est pas tellement que vous faites passer des cellules. C'est dans le fait que vous essayez de supprimer de nombreuses lignes inconnues d'une table; chacun nécessitant une réorganisation interne et une restructuration de la table ListObject. Tout ce que vous pouvez faire pour supprimer toutes les lignes à la fois aidera et si vous pouvez les supprimer en bloc, cela serait encore mieux. En outre, vous pouvez recalculer des colonnes de formules entières à plusieurs resockets et de façon redondante.

    Vous devriez find plus rapidement un scootch. 

     Sub removeAccounts() Dim i As Long Debug.Print Timer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual With ThisWorkbook.Sheets("TheSheet") With .ListObjects("TheTable") '.Range.Columns(2).Delete .Range.Columns(2).Insert With .DataBodyRange.Cells(1, 2).Resize(.DataBodyRange.Rows.Count, 1) .FormulaR1C1 = "=isnumber(match(RC[-1], Included_Rows, 0))" .Calculate End With .Range.Cells.Sort Key1:=.Range.Columns(2), Order1:=xlDescending, _ Orientation:=xlTopToBottom, Header:=xlYes With .DataBodyRange i = Application.Match(False, .Columns(2), 0) Application.DisplayAlerts = False .Cells(i, 1).Resize(.Rows.Count - i + 1, .Columns.Count).Delete Application.DisplayAlerts = True End With .Range.Columns(2).Delete End With End With Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Debug.Print Timer End Sub Sub removeAccounts () Sub removeAccounts() Dim i As Long Debug.Print Timer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual With ThisWorkbook.Sheets("TheSheet") With .ListObjects("TheTable") '.Range.Columns(2).Delete .Range.Columns(2).Insert With .DataBodyRange.Cells(1, 2).Resize(.DataBodyRange.Rows.Count, 1) .FormulaR1C1 = "=isnumber(match(RC[-1], Included_Rows, 0))" .Calculate End With .Range.Cells.Sort Key1:=.Range.Columns(2), Order1:=xlDescending, _ Orientation:=xlTopToBottom, Header:=xlYes With .DataBodyRange i = Application.Match(False, .Columns(2), 0) Application.DisplayAlerts = False .Cells(i, 1).Resize(.Rows.Count - i + 1, .Columns.Count).Delete Application.DisplayAlerts = True End With .Range.Columns(2).Delete End With End With Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Debug.Print Timer End Sub Dim i As Long Sub removeAccounts() Dim i As Long Debug.Print Timer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual With ThisWorkbook.Sheets("TheSheet") With .ListObjects("TheTable") '.Range.Columns(2).Delete .Range.Columns(2).Insert With .DataBodyRange.Cells(1, 2).Resize(.DataBodyRange.Rows.Count, 1) .FormulaR1C1 = "=isnumber(match(RC[-1], Included_Rows, 0))" .Calculate End With .Range.Cells.Sort Key1:=.Range.Columns(2), Order1:=xlDescending, _ Orientation:=xlTopToBottom, Header:=xlYes With .DataBodyRange i = Application.Match(False, .Columns(2), 0) Application.DisplayAlerts = False .Cells(i, 1).Resize(.Rows.Count - i + 1, .Columns.Count).Delete Application.DisplayAlerts = True End With .Range.Columns(2).Delete End With End With Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Debug.Print Timer End Sub Debug.Print Timer Sub removeAccounts() Dim i As Long Debug.Print Timer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual With ThisWorkbook.Sheets("TheSheet") With .ListObjects("TheTable") '.Range.Columns(2).Delete .Range.Columns(2).Insert With .DataBodyRange.Cells(1, 2).Resize(.DataBodyRange.Rows.Count, 1) .FormulaR1C1 = "=isnumber(match(RC[-1], Included_Rows, 0))" .Calculate End With .Range.Cells.Sort Key1:=.Range.Columns(2), Order1:=xlDescending, _ Orientation:=xlTopToBottom, Header:=xlYes With .DataBodyRange i = Application.Match(False, .Columns(2), 0) Application.DisplayAlerts = False .Cells(i, 1).Resize(.Rows.Count - i + 1, .Columns.Count).Delete Application.DisplayAlerts = True End With .Range.Columns(2).Delete End With End With Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Debug.Print Timer End Sub Application.ScreenUpdating = False Sub removeAccounts() Dim i As Long Debug.Print Timer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual With ThisWorkbook.Sheets("TheSheet") With .ListObjects("TheTable") '.Range.Columns(2).Delete .Range.Columns(2).Insert With .DataBodyRange.Cells(1, 2).Resize(.DataBodyRange.Rows.Count, 1) .FormulaR1C1 = "=isnumber(match(RC[-1], Included_Rows, 0))" .Calculate End With .Range.Cells.Sort Key1:=.Range.Columns(2), Order1:=xlDescending, _ Orientation:=xlTopToBottom, Header:=xlYes With .DataBodyRange i = Application.Match(False, .Columns(2), 0) Application.DisplayAlerts = False .Cells(i, 1).Resize(.Rows.Count - i + 1, .Columns.Count).Delete Application.DisplayAlerts = True End With .Range.Columns(2).Delete End With End With Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Debug.Print Timer End Sub Application.Calculation = xlCalculationManual Sub removeAccounts() Dim i As Long Debug.Print Timer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual With ThisWorkbook.Sheets("TheSheet") With .ListObjects("TheTable") '.Range.Columns(2).Delete .Range.Columns(2).Insert With .DataBodyRange.Cells(1, 2).Resize(.DataBodyRange.Rows.Count, 1) .FormulaR1C1 = "=isnumber(match(RC[-1], Included_Rows, 0))" .Calculate End With .Range.Cells.Sort Key1:=.Range.Columns(2), Order1:=xlDescending, _ Orientation:=xlTopToBottom, Header:=xlYes With .DataBodyRange i = Application.Match(False, .Columns(2), 0) Application.DisplayAlerts = False .Cells(i, 1).Resize(.Rows.Count - i + 1, .Columns.Count).Delete Application.DisplayAlerts = True End With .Range.Columns(2).Delete End With End With Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Debug.Print Timer End Sub Avec ThisWorkbook.Sheets ("TheSheet") Sub removeAccounts() Dim i As Long Debug.Print Timer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual With ThisWorkbook.Sheets("TheSheet") With .ListObjects("TheTable") '.Range.Columns(2).Delete .Range.Columns(2).Insert With .DataBodyRange.Cells(1, 2).Resize(.DataBodyRange.Rows.Count, 1) .FormulaR1C1 = "=isnumber(match(RC[-1], Included_Rows, 0))" .Calculate End With .Range.Cells.Sort Key1:=.Range.Columns(2), Order1:=xlDescending, _ Orientation:=xlTopToBottom, Header:=xlYes With .DataBodyRange i = Application.Match(False, .Columns(2), 0) Application.DisplayAlerts = False .Cells(i, 1).Resize(.Rows.Count - i + 1, .Columns.Count).Delete Application.DisplayAlerts = True End With .Range.Columns(2).Delete End With End With Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Debug.Print Timer End Sub Avec .ListObjects ("TheTable") Sub removeAccounts() Dim i As Long Debug.Print Timer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual With ThisWorkbook.Sheets("TheSheet") With .ListObjects("TheTable") '.Range.Columns(2).Delete .Range.Columns(2).Insert With .DataBodyRange.Cells(1, 2).Resize(.DataBodyRange.Rows.Count, 1) .FormulaR1C1 = "=isnumber(match(RC[-1], Included_Rows, 0))" .Calculate End With .Range.Cells.Sort Key1:=.Range.Columns(2), Order1:=xlDescending, _ Orientation:=xlTopToBottom, Header:=xlYes With .DataBodyRange i = Application.Match(False, .Columns(2), 0) Application.DisplayAlerts = False .Cells(i, 1).Resize(.Rows.Count - i + 1, .Columns.Count).Delete Application.DisplayAlerts = True End With .Range.Columns(2).Delete End With End With Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Debug.Print Timer End Sub '.Range.Columns (2). Supprimer Sub removeAccounts() Dim i As Long Debug.Print Timer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual With ThisWorkbook.Sheets("TheSheet") With .ListObjects("TheTable") '.Range.Columns(2).Delete .Range.Columns(2).Insert With .DataBodyRange.Cells(1, 2).Resize(.DataBodyRange.Rows.Count, 1) .FormulaR1C1 = "=isnumber(match(RC[-1], Included_Rows, 0))" .Calculate End With .Range.Cells.Sort Key1:=.Range.Columns(2), Order1:=xlDescending, _ Orientation:=xlTopToBottom, Header:=xlYes With .DataBodyRange i = Application.Match(False, .Columns(2), 0) Application.DisplayAlerts = False .Cells(i, 1).Resize(.Rows.Count - i + 1, .Columns.Count).Delete Application.DisplayAlerts = True End With .Range.Columns(2).Delete End With End With Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Debug.Print Timer End Sub .Range.Columns (2) .Insérer Sub removeAccounts() Dim i As Long Debug.Print Timer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual With ThisWorkbook.Sheets("TheSheet") With .ListObjects("TheTable") '.Range.Columns(2).Delete .Range.Columns(2).Insert With .DataBodyRange.Cells(1, 2).Resize(.DataBodyRange.Rows.Count, 1) .FormulaR1C1 = "=isnumber(match(RC[-1], Included_Rows, 0))" .Calculate End With .Range.Cells.Sort Key1:=.Range.Columns(2), Order1:=xlDescending, _ Orientation:=xlTopToBottom, Header:=xlYes With .DataBodyRange i = Application.Match(False, .Columns(2), 0) Application.DisplayAlerts = False .Cells(i, 1).Resize(.Rows.Count - i + 1, .Columns.Count).Delete Application.DisplayAlerts = True End With .Range.Columns(2).Delete End With End With Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Debug.Print Timer End Sub Avec .DataBodyRange.Cells (1, 2) .Resize (.DataBodyRange.Rows.Count, 1) Sub removeAccounts() Dim i As Long Debug.Print Timer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual With ThisWorkbook.Sheets("TheSheet") With .ListObjects("TheTable") '.Range.Columns(2).Delete .Range.Columns(2).Insert With .DataBodyRange.Cells(1, 2).Resize(.DataBodyRange.Rows.Count, 1) .FormulaR1C1 = "=isnumber(match(RC[-1], Included_Rows, 0))" .Calculate End With .Range.Cells.Sort Key1:=.Range.Columns(2), Order1:=xlDescending, _ Orientation:=xlTopToBottom, Header:=xlYes With .DataBodyRange i = Application.Match(False, .Columns(2), 0) Application.DisplayAlerts = False .Cells(i, 1).Resize(.Rows.Count - i + 1, .Columns.Count).Delete Application.DisplayAlerts = True End With .Range.Columns(2).Delete End With End With Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Debug.Print Timer End Sub .FormulaR1C1 = "= isnumber (match (RC [-1], Included_Rows, 0))" Sub removeAccounts() Dim i As Long Debug.Print Timer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual With ThisWorkbook.Sheets("TheSheet") With .ListObjects("TheTable") '.Range.Columns(2).Delete .Range.Columns(2).Insert With .DataBodyRange.Cells(1, 2).Resize(.DataBodyRange.Rows.Count, 1) .FormulaR1C1 = "=isnumber(match(RC[-1], Included_Rows, 0))" .Calculate End With .Range.Cells.Sort Key1:=.Range.Columns(2), Order1:=xlDescending, _ Orientation:=xlTopToBottom, Header:=xlYes With .DataBodyRange i = Application.Match(False, .Columns(2), 0) Application.DisplayAlerts = False .Cells(i, 1).Resize(.Rows.Count - i + 1, .Columns.Count).Delete Application.DisplayAlerts = True End With .Range.Columns(2).Delete End With End With Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Debug.Print Timer End Sub .Range.Cells.Sort Key1: =. Range.Columns (2), Order1: = xlDescending, _ Sub removeAccounts() Dim i As Long Debug.Print Timer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual With ThisWorkbook.Sheets("TheSheet") With .ListObjects("TheTable") '.Range.Columns(2).Delete .Range.Columns(2).Insert With .DataBodyRange.Cells(1, 2).Resize(.DataBodyRange.Rows.Count, 1) .FormulaR1C1 = "=isnumber(match(RC[-1], Included_Rows, 0))" .Calculate End With .Range.Cells.Sort Key1:=.Range.Columns(2), Order1:=xlDescending, _ Orientation:=xlTopToBottom, Header:=xlYes With .DataBodyRange i = Application.Match(False, .Columns(2), 0) Application.DisplayAlerts = False .Cells(i, 1).Resize(.Rows.Count - i + 1, .Columns.Count).Delete Application.DisplayAlerts = True End With .Range.Columns(2).Delete End With End With Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Debug.Print Timer End Sub Orientation: = xlTopToBottom, Header: = xlYes Sub removeAccounts() Dim i As Long Debug.Print Timer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual With ThisWorkbook.Sheets("TheSheet") With .ListObjects("TheTable") '.Range.Columns(2).Delete .Range.Columns(2).Insert With .DataBodyRange.Cells(1, 2).Resize(.DataBodyRange.Rows.Count, 1) .FormulaR1C1 = "=isnumber(match(RC[-1], Included_Rows, 0))" .Calculate End With .Range.Cells.Sort Key1:=.Range.Columns(2), Order1:=xlDescending, _ Orientation:=xlTopToBottom, Header:=xlYes With .DataBodyRange i = Application.Match(False, .Columns(2), 0) Application.DisplayAlerts = False .Cells(i, 1).Resize(.Rows.Count - i + 1, .Columns.Count).Delete Application.DisplayAlerts = True End With .Range.Columns(2).Delete End With End With Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Debug.Print Timer End Sub Avec .DataBodyRange Sub removeAccounts() Dim i As Long Debug.Print Timer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual With ThisWorkbook.Sheets("TheSheet") With .ListObjects("TheTable") '.Range.Columns(2).Delete .Range.Columns(2).Insert With .DataBodyRange.Cells(1, 2).Resize(.DataBodyRange.Rows.Count, 1) .FormulaR1C1 = "=isnumber(match(RC[-1], Included_Rows, 0))" .Calculate End With .Range.Cells.Sort Key1:=.Range.Columns(2), Order1:=xlDescending, _ Orientation:=xlTopToBottom, Header:=xlYes With .DataBodyRange i = Application.Match(False, .Columns(2), 0) Application.DisplayAlerts = False .Cells(i, 1).Resize(.Rows.Count - i + 1, .Columns.Count).Delete Application.DisplayAlerts = True End With .Range.Columns(2).Delete End With End With Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Debug.Print Timer End Sub i = Application.Match (False, .Columns (2), 0) Sub removeAccounts() Dim i As Long Debug.Print Timer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual With ThisWorkbook.Sheets("TheSheet") With .ListObjects("TheTable") '.Range.Columns(2).Delete .Range.Columns(2).Insert With .DataBodyRange.Cells(1, 2).Resize(.DataBodyRange.Rows.Count, 1) .FormulaR1C1 = "=isnumber(match(RC[-1], Included_Rows, 0))" .Calculate End With .Range.Cells.Sort Key1:=.Range.Columns(2), Order1:=xlDescending, _ Orientation:=xlTopToBottom, Header:=xlYes With .DataBodyRange i = Application.Match(False, .Columns(2), 0) Application.DisplayAlerts = False .Cells(i, 1).Resize(.Rows.Count - i + 1, .Columns.Count).Delete Application.DisplayAlerts = True End With .Range.Columns(2).Delete End With End With Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Debug.Print Timer End Sub Application.DisplayAlerts = False Sub removeAccounts() Dim i As Long Debug.Print Timer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual With ThisWorkbook.Sheets("TheSheet") With .ListObjects("TheTable") '.Range.Columns(2).Delete .Range.Columns(2).Insert With .DataBodyRange.Cells(1, 2).Resize(.DataBodyRange.Rows.Count, 1) .FormulaR1C1 = "=isnumber(match(RC[-1], Included_Rows, 0))" .Calculate End With .Range.Cells.Sort Key1:=.Range.Columns(2), Order1:=xlDescending, _ Orientation:=xlTopToBottom, Header:=xlYes With .DataBodyRange i = Application.Match(False, .Columns(2), 0) Application.DisplayAlerts = False .Cells(i, 1).Resize(.Rows.Count - i + 1, .Columns.Count).Delete Application.DisplayAlerts = True End With .Range.Columns(2).Delete End With End With Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Debug.Print Timer End Sub .Cells (i, 1) .Resize (.Rows.Count - i + 1, .Columns.Count). Supprimer Sub removeAccounts() Dim i As Long Debug.Print Timer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual With ThisWorkbook.Sheets("TheSheet") With .ListObjects("TheTable") '.Range.Columns(2).Delete .Range.Columns(2).Insert With .DataBodyRange.Cells(1, 2).Resize(.DataBodyRange.Rows.Count, 1) .FormulaR1C1 = "=isnumber(match(RC[-1], Included_Rows, 0))" .Calculate End With .Range.Cells.Sort Key1:=.Range.Columns(2), Order1:=xlDescending, _ Orientation:=xlTopToBottom, Header:=xlYes With .DataBodyRange i = Application.Match(False, .Columns(2), 0) Application.DisplayAlerts = False .Cells(i, 1).Resize(.Rows.Count - i + 1, .Columns.Count).Delete Application.DisplayAlerts = True End With .Range.Columns(2).Delete End With End With Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Debug.Print Timer End Sub Application.DisplayAlerts = True Sub removeAccounts() Dim i As Long Debug.Print Timer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual With ThisWorkbook.Sheets("TheSheet") With .ListObjects("TheTable") '.Range.Columns(2).Delete .Range.Columns(2).Insert With .DataBodyRange.Cells(1, 2).Resize(.DataBodyRange.Rows.Count, 1) .FormulaR1C1 = "=isnumber(match(RC[-1], Included_Rows, 0))" .Calculate End With .Range.Cells.Sort Key1:=.Range.Columns(2), Order1:=xlDescending, _ Orientation:=xlTopToBottom, Header:=xlYes With .DataBodyRange i = Application.Match(False, .Columns(2), 0) Application.DisplayAlerts = False .Cells(i, 1).Resize(.Rows.Count - i + 1, .Columns.Count).Delete Application.DisplayAlerts = True End With .Range.Columns(2).Delete End With End With Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Debug.Print Timer End Sub .Range.Columns (2). Supprimer Sub removeAccounts() Dim i As Long Debug.Print Timer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual With ThisWorkbook.Sheets("TheSheet") With .ListObjects("TheTable") '.Range.Columns(2).Delete .Range.Columns(2).Insert With .DataBodyRange.Cells(1, 2).Resize(.DataBodyRange.Rows.Count, 1) .FormulaR1C1 = "=isnumber(match(RC[-1], Included_Rows, 0))" .Calculate End With .Range.Cells.Sort Key1:=.Range.Columns(2), Order1:=xlDescending, _ Orientation:=xlTopToBottom, Header:=xlYes With .DataBodyRange i = Application.Match(False, .Columns(2), 0) Application.DisplayAlerts = False .Cells(i, 1).Resize(.Rows.Count - i + 1, .Columns.Count).Delete Application.DisplayAlerts = True End With .Range.Columns(2).Delete End With End With Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Debug.Print Timer End Sub Application.Calculation = xlCalculationAutomatic Sub removeAccounts() Dim i As Long Debug.Print Timer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual With ThisWorkbook.Sheets("TheSheet") With .ListObjects("TheTable") '.Range.Columns(2).Delete .Range.Columns(2).Insert With .DataBodyRange.Cells(1, 2).Resize(.DataBodyRange.Rows.Count, 1) .FormulaR1C1 = "=isnumber(match(RC[-1], Included_Rows, 0))" .Calculate End With .Range.Cells.Sort Key1:=.Range.Columns(2), Order1:=xlDescending, _ Orientation:=xlTopToBottom, Header:=xlYes With .DataBodyRange i = Application.Match(False, .Columns(2), 0) Application.DisplayAlerts = False .Cells(i, 1).Resize(.Rows.Count - i + 1, .Columns.Count).Delete Application.DisplayAlerts = True End With .Range.Columns(2).Delete End With End With Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Debug.Print Timer End Sub Application.ScreenUpdating = True Sub removeAccounts() Dim i As Long Debug.Print Timer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual With ThisWorkbook.Sheets("TheSheet") With .ListObjects("TheTable") '.Range.Columns(2).Delete .Range.Columns(2).Insert With .DataBodyRange.Cells(1, 2).Resize(.DataBodyRange.Rows.Count, 1) .FormulaR1C1 = "=isnumber(match(RC[-1], Included_Rows, 0))" .Calculate End With .Range.Cells.Sort Key1:=.Range.Columns(2), Order1:=xlDescending, _ Orientation:=xlTopToBottom, Header:=xlYes With .DataBodyRange i = Application.Match(False, .Columns(2), 0) Application.DisplayAlerts = False .Cells(i, 1).Resize(.Rows.Count - i + 1, .Columns.Count).Delete Application.DisplayAlerts = True End With .Range.Columns(2).Delete End With End With Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Debug.Print Timer End Sub Debug.Print Timer Sub removeAccounts() Dim i As Long Debug.Print Timer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual With ThisWorkbook.Sheets("TheSheet") With .ListObjects("TheTable") '.Range.Columns(2).Delete .Range.Columns(2).Insert With .DataBodyRange.Cells(1, 2).Resize(.DataBodyRange.Rows.Count, 1) .FormulaR1C1 = "=isnumber(match(RC[-1], Included_Rows, 0))" .Calculate End With .Range.Cells.Sort Key1:=.Range.Columns(2), Order1:=xlDescending, _ Orientation:=xlTopToBottom, Header:=xlYes With .DataBodyRange i = Application.Match(False, .Columns(2), 0) Application.DisplayAlerts = False .Cells(i, 1).Resize(.Rows.Count - i + 1, .Columns.Count).Delete Application.DisplayAlerts = True End With .Range.Columns(2).Delete End With End With Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Debug.Print Timer End Sub

    J'ai couru cela contre 500 lignes de données d'échantillons (AZ) avec AD dans la gamme nommée Included_Rows . Il a fallu 0.02 secondes.

    Essaye ça: 

     Dim Tbl As ListObject Set Tbl = Sheets(indx).ListObjects(Tabla) With Tbl If .ListRows.Count >= 1 Then .DataBodyRange.Delete End With Dim Tbl As ListObject Dim Tbl As ListObject Set Tbl = Sheets(indx).ListObjects(Tabla) With Tbl If .ListRows.Count >= 1 Then .DataBodyRange.Delete End With Set Tbl = Sheets (ind.) .ListObjects (Tabla) Dim Tbl As ListObject Set Tbl = Sheets(indx).ListObjects(Tabla) With Tbl If .ListRows.Count >= 1 Then .DataBodyRange.Delete End With Avec Tbl Dim Tbl As ListObject Set Tbl = Sheets(indx).ListObjects(Tabla) With Tbl If .ListRows.Count >= 1 Then .DataBodyRange.Delete End With Si .ListRows.Count> = 1 puis .DataBodyRange.Delete Dim Tbl As ListObject Set Tbl = Sheets(indx).ListObjects(Tabla) With Tbl If .ListRows.Count >= 1 Then .DataBodyRange.Delete End With

    Utilisez ce code pour supprimer tout sauf la première ligne d'un object de list. En supprimant la ligne entière, elle redimensionne également le tableau de manière appropriée. tblData est une variable ListObject indiquant un tableau / listobject existant. 

     tblData.DataBodyRange.Offset(1, 0).EntireRow.Delete

    Bien sûr, vous ne pouvez pas avoir de données à gauche ou à droite d'une table car elle sera également supprimée. Mais c'est beaucoup plus rapide que la boucle.