J'ai une table avec environ 100 000 lignes et 40 colonnes.
Je dois copier certaines des lignes dans un autre classeur basé sur un tableau avec des chaînes qui correspondent aux valeurs des colonnes.
' Copy and Paste whole table
dst_wks.UsedRange.Offset(1, 0).Value = ""
addr = src_wks.UsedRange.Address
dst_wks.Range(addr).Value = src_wks.UsedRange.Value
' Filter data
dst_wks.ListObjects("Table1").Range.AutoFilter _
Field:=dst_wks.ListObjects("Table1").ListColumns("Column1").Index, _
Criteria1:=cond_list, Operator:=xlFilterValues
' Remove rest
Application.DisplayAlerts = False ' Suppress "delete row?" promt
Dim i, numRows As Long
numRows = dst_wks.UsedRange.Rows.Count
For i = numRows To 1 Step -1
If (dst_wks.Range("A" & i).EntireRow.Hidden = True) Then
dst_wks.Range("A" & i).Delete
End If
Next i
Application.DisplayAlerts = True
3 Réponses :
tu pourrais essayer :
la technique de la solution acceptée de la question SO que vous avez liée
c'est-à-dire: parcourez les zones et travaillez avec les propriétés Value
référence src_wks.ListObjects ("Table1"). Plage également pour l'opération de copier / coller des valeurs
comme suit:
Dim area As Range
With src_wks.ListObjects("Table1").Range ' reference your table Range
' Filter referenced range
.AutoFilter _
Field:=src_wks.ListObjects("Table1").ListColumns("Column1").Index, _
Criteria1:=cond_list, Operator:=xlFilterValues
' Copy and paste values from each single referenced range "visible" area
For Each area In .SpecialCells(xlCellTypeVisible).Areas
With area
dst_wks.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(.Rows.Count, .Columns.Count).Value = .Value
End With
Next
End With
et si vous pouviez également trier votre table, cela pourrait considérablement l'accélérer davantage p>
dst_wks.Cells (Rows.Count ... être dst_wks.Cells (.Rows.Count ... ?
@RyanWildry, Non. Puisque, étant donné le With src_wks.ListObjects ("Table1"). Range… End With c'est dedans, il ferait référence à la plage référencée .Rows.Count . Il devrait être plus approprié dst_wks.Cells (dst_wks.Rows.Count… , mais j'ai supposé (à mes risques et périls…) que les feuilles source et destination étaient dans le même classeur, ou, au moins, ils étaient dans un classeur différent de la même version d'Excel afin d'avoir le même nombre maximum de lignes
Hmm, il fera référence à la feuille active?
@RyanWildry, oui. Mais dans le seul but de renvoyer son nombre maximal de lignes, qui est le même dans toutes les feuilles de calcul du classeur. Et qui serait le même de toutes les autres feuilles de calcul de classeur à condition qu'elles soient de la même version d'Excel
@RyanWildry ouais, je pensais la même chose mais sans le point, ce code mettait tout dans la première rangée.
@HTH, malheureusement, cela prend encore une éternité. J'essaye de trier ma table mais sans succès.
que signifie réellement le succès "sans" ?
@Gumaa, "chez RyanWildry ouais, je pensais la même chose mais sans le point ce code mettait tout dans la première ligne." . Vous devez faire référence à la deuxième partie de la même déclaration (c'est-à-dire ... .Resize (.Rows.Count, .Columns.Count) .Value ), tandis que Ryan faisait référence à sa première partie. Quant à ce dernier, j'ai déjà donné des explications. Comme pour le premier, ces points doivent être là car ils font référence à la zone actuelle afin de dimensionner de manière appropriée la plage de destination
@HTH Je faisais référence à la même partie que Ryan mais je me suis mal exprimé. Je voulais dire "avec le point" et non "sans". Je le déboguais et il semblait que dans ce cas (avec un point), tout était écrit uniquement sur la première ligne.
Et à propos du tri, j'ai du mal à définir CustomList utilisé pour le tri. Application.AddCustomList ListArray: = cond_list renvoie la méthode 'addcustomlist' de l'objet '_application' a échoué . Mais je vais essayer de google quelque chose de plus.
@Gumaa, quant à votre avant-dernier commentaire: ok. Et j'ai déjà expliqué la raison pour laquelle le point ne doit pas être là, donc mon code ne l'avait pas.
@Gumaa, comme pour votre dernier commentaire, vous feriez mieux de jeter un œil aux autres réponses ici en utilisant d'abord SQL, qui s'annoncent très rapides
Je ne suis pas sûr de l'apparence de vos données, mais à mon avis, il n'est pas efficace d'utiliser un filtre. Ici, je publierai une démo pour votre référence. Mieux vaut utiliser SQL.
Sub filterProcess()
Dim filterArray
Dim conn As Object
Set conn = CreateObject("adodb.connection")
strPath = ThisWorkbook.FullName
If Application.Version < 12 Then
connString = "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & strPath
Else
connString = "Provider=Microsoft.ACE.OLEDB.12.0; Extended Properties = 'Excel 12.0; HDR=YES;IMEX=0'; Data Source = " & strPath
End If
filterArray = Array("ta001", "01", "A")
conn.Open connString
strSQL = " SELECT * FROM [a$] where [title1] = '" & filterArray(0) & "'" & " and [title2] = '" & filterArray(1) & "'" & "and [title3] = '" & filterArray(2) & "'"
Set rst = conn.Execute(strSQL)
Worksheets.Add
For j = 0 To rst.Fields.Count - 1
Cells(1, j + 1) = rst.Fields(j).Name
Next
ActiveSheet.Range("A2").CopyFromRecordset rst
rst.Close
conn.Close
Set conn = Nothing
End Sub
Une autre approche (il y a plusieurs façons de le faire) pourrait être d'utiliser une instruction SQL pour interroger les données de la feuille en question, puis de les copier dans une nouvelle feuille. Cela peut être préférable si les conditions de sélection des données deviennent plus complexes.
J'ai la configuration de mes données comme celle-ci sur Sheet1:
Code
Option Explicit
Private Const adCmdText As Long = 1
Private Const adStateOpen As Long = 1
Public Sub DisplayView(Conditions As String)
Dim dbField As Variant
Dim fieldCounter As Long
Dim dbConnection As Object
Dim dbRecordset As Object
Dim dbCommand As Object
Dim OutputSheet As Excel.Worksheet
Set dbConnection = CreateObject("ADODB.Connection")
Set dbRecordset = CreateObject("ADODB.Recordset")
Set dbCommand = CreateObject("ADODB.Command")
Set OutputSheet = ThisWorkbook.Worksheets("Sheet2")
'Do a quick check to determine the correct connection string
'if one of these don't work, have a look here --> https://www.connectionstrings.com/excel/
If Left$(ThisWorkbook.FullName, 4) = "xlsm" Then
dbConnection.connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
ThisWorkbook.FullName & ";Extended Properties='Excel 12.0 Macro;HDR=YES';"
Else
dbConnection.connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
ThisWorkbook.FullName & ";Extended Properties='Excel 12.0;HDR=YES';"
End If
'Open the connection and query
dbConnection.Open
With dbCommand
.ActiveConnection = dbConnection
.CommandType = adCmdText
.CommandText = "Select * from [Sheet1$] where Column1 in (" & Conditions & ")" 'Update Sheet where applicable
Set dbRecordset = .Execute
End With
'Clear the Output Sheet
OutputSheet.Cells.Clear
'Add Headers to output
For Each dbField In dbRecordset.Fields
fieldCounter = fieldCounter + 1
OutputSheet.Cells(1, fieldCounter).Value2 = dbField.Name
Next
'Dump the found records
OutputSheet.Range("A2").CopyFromRecordset dbRecordset
If dbConnection.State = adStateOpen Then dbConnection.Close
End Sub
'Run from here
Public Sub ExampleRunner()
Dim t As Double
t = Timer
DisplayView "'value1','value2','value3'" 'Send it a quoted csv of values you are looking for
Debug.Print "Getting data took: " & Timer - t & " seconds"
End Sub
Cela prend environ 4 à 5 secondes sur ma machine pour récupérer quelques milliers d'enregistrements d'une taille totale de jeu de données de 100 000.
Merci beaucoup. Ça fonctionne! Mais maintenant, je pensais qu'il serait plus propre de remplacer également la liste de conditions par une requête SQL. Je pourrais simplement écrire une requête SQL imbriquée au lieu de construire cette liste "à la main" dans une boucle for. Mais quand je fais quelque chose comme ceci: Sélectionnez * De [wksA $] Où [Colonne1] dans (Sélectionnez [Colonne1] de [wksB $] où [Colonne1] <> '') J'obtiens une incompatibilité de type - même si le résultat de la requête imbriquée est une liste de chaînes. Peut-être savez-vous si c'est quelque chose lié à Excel ou simplement si je suis mauvais en SQL?
Ça a l'air bien, assurez-vous simplement que votre feuille et vos colonnes existent. Pour déboguer, essayez d'exécuter votre sélection interne en tant que jeu d'enregistrements séparé pour voir ce que vous obtenez. Vous devrez peut-être simplement effectuer un vidage dans une chaîne à la place. Comment est la vitesse?
J'ai exécuté la sélection interne et il ne renvoie qu'une seule colonne avec des valeurs de chaîne comme prévu. Accélérez-le à merveille, d'environ 3 à 10 secondes. Dans le pire des cas, avec une liste de conditions énorme, cela prend 30 secondes mais c'est toujours ultra rapide.
pour une meilleure vitesse, vous pouvez essayer dict.