Variable Tableau : récupérer une plage multiple

Version imprimable

Bonjour,

J'essaye de charger une variable tableau à partir d'une plage multiple mais cela ne me prend que la 1ère plage.
Le code :
Code:

1 2 3 4 5 6 der = Range("a1000000").End(xlUp).Row - 3 Application.Union(Range("a4:b" & der + 3), Range("f4:f" & der + 3)).Copy plage = Application.Union(Range("a4:a" & der + 3), Range("e4:e" & der + 3)).Address ReDim tablox(der, 3) tablox = Range(plage).Value
La copie avec Union fonctionne, mais le chargement du tableau ne prend que la colonne A.

Que manque t-il ? Est-ce possible de charger dans une variable tableau une plage multiple en une opération ?

Merci

05/08/2015, 12h12
helas
Bonjour,
avec les plages directement
Code:

1 2 tablox = Range("a4:a" & der + 3, "e4:e" & der + 3).Value
a tester

1 pièce(s) jointe(s)

Citation:

Est-ce possible de charger dans une variable tableau une plage multiple en une opération ?

Bonjour,

A ma connaissance, ce n'est pas possible.
En revanche, on peut utiliser 2 astuces.

1) On monte toute la plage dans un Variant sans se préoccuper des colonnes qui ne nous intéressent pas. Ces dernières sont filtrées dans le code

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Sub PlageUnSeulTenant()
Dim Plage As Range
Dim var As Variant
Dim i&
Dim j&
Dim der&
'---
der = Range("a1000000").End(xlUp).Row - 3
Set Plage = Range("a4:e" & der + 3)
var = Plage 'om monte tout dans un Variant qui se comporte comme un tableau à 2 dimension
 
For i& = 1 To UBound(var, 1)
  For j& = 1 To UBound(var, 2)
    '--- on ne veut pas des colonnes C et D ---
    If j& <> 3 And j& <> 4 Then
    '------------------------------------------
      MsgBox var(i&, j&)
    End If
  Next j&
Next i&
End Sub

2) On fait de multiples sélections (comportant le même nombre de lignes) et on utilise les Areas de la plage obtenue

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
Sub PlageMultiplesTenants()
Dim Plage As Range
Dim T()
Dim i&
Dim j&
Dim k&
Dim nbCol&
Dim Col&
Dim der&
'---
der = Range("a1000000").End(xlUp).Row - 3
Set Plage = Application.Union(Range("a4:b" & der + 3), Range("e4:e" & der + 3))
 
    '°°° Essai pour une plage plus étendue °°°
Set Plage = Application.Union(Plage, Range("g4:h" & der + 3)) 'à flaguer dans votre réalité
    '°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°
 
'--- On compte le nombre de colonnes des Areas dans la plage ---
For k& = 1 To Plage.Areas.Count
  nbCol& = nbCol& + Plage.Areas(k&).Columns.Count
Next k&
 
'--- On dimensione le tableau de sortie avec le bon nombre de colonnes ---
ReDim T(1 To Plage.Rows.Count, 1 To nbCol&)
 
'--- Populer le tableau des éléments des Areas ---
Col& = 0
For k& = 1 To Plage.Areas.Count
  For j& = 1 To Plage.Areas(k&).Columns.Count
    For i& = 1 To UBound(T, 1)
      T(i&, j& + Col&) = Plage.Areas(k&)(i&, j&)
    Next i&
  Next j&
  Col& = Col& + Plage.Areas(k&).Columns.Count
Next k&
 
'--- Visualiser la population du tableau ---
For i& = 1 To UBound(T, 1)
  For j& = 1 To UBound(T, 2)
    MsgBox T(i&, j&)
  Next j&
Next i&
End Sub

Cela fonctionne chez moi avec Excel 2007.

05/08/2015, 18h44
Ellimac

Re : Variable Tableau : récupérer une plage multiple

Bonjour,

Merci pour cette réponse que je regarderai demain.

Cordialement

Camille

Bonsoir,

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
Sub FiltreColonnes()
  a = [A2:F10]          ' tableau a()
  b = FiltreArrayCol(a, Array(1, 2, 6)) ' on prend les colonnes 1, 2,  6
  [m1].Resize(UBound(b), UBound(b, 2)) = b
End Sub
 
Function FiltreArrayCol(tableau, ColResult)
  Dim b()
  ReDim b(LBound(tableau) To UBound(tableau), 1 To UBound(ColResult) + 1 - LBound(ColResult))
  For i = LBound(tableau, 1) To UBound(tableau, 1)
    For c = LBound(ColResult) To UBound(ColResult)
      col = ColResult(c)
      b(i, c + 1) = tableau(i, col)
    Next c
  Next i
  FiltreArrayCol = b
End Function

Boisgontier

08/08/2015, 16h41
Marc-L
Bonjour, bonjour !

Voici une méthode directe sans boucle pour affecter une plage discontinue dans une variable tableau :
Code:

1 2 3 With Range("A4:F" & Application.Max(Cells(Rows.Count, 1).End(xlUp).Row, 4)) VA = Application.Index(.Value, Evaluate("ROW(1:" & .Rows.Count & ")"), [{1,2,6}]) End With
_____________________________________________________________________________________________________

Merci de cliquer sur :plusser: pour chaque message ayant aidé puis sur :resolu: pour clore cette discussion …

_____________________________________________________________________________________________________
Je suis Charlie, Bardo, Sousse
08/08/2015, 18h32
PMO2017

Bonjour Marc-L,

Ta proposition m'est très intéressante.
Elle me laisse entrevoir beaucoup d'autres utilisations.
Par exemple le changement d'ordre des colonnes (entre autres).

Je te mets des points.
08/08/2015, 20h11
jijie

Salut Marc

Ben ça c'est fort!!

1 point également!
08/08/2015, 20h14
Marc-L

Merci ! Tu as raison PMO2017, c'est assez pratique pour certaines copies.

Je n'y pense pas assez souvent mais c'est encore une preuve qu'avant de commencer par du pur VBA
mieux vaut se pencher sur les fonctions de base d'Excel ! Pas toujours évident mais souvent le plus efficace …

Comme ici les fonctions INDEX, LIGNE (ROW en VBA combinée à la puissante fonction matricielle Evaluate)
et la simple écriture d'une matrice entre accolades.

Bonjour,

J'aime bien la technique de Marc aussi.

Une autre idée pour dépasser les 65536 lignes :

Code:

1
2
3
4
5
6
7
8
9
10
11
Sub test3()
    Dim shTmp As Worksheet, datas
    Application.ScreenUpdating = False
    Intersect([A1].CurrentRegion.EntireRow, Range("A:A,B:B,E:E")).Copy
    Set shTmp = Sheets.Add
    shTmp.[A1].PasteSpecial Paste:=xlPasteValues
    datas = shTmp.[A1].CurrentRegion
    Application.DisplayAlerts = False
    shTmp.Delete
    Application.DisplayAlerts = True
End Sub

Ca reste relativement rapide, 0.7s pour 100000 lignes.

eric

1 pièce(s) jointe(s)

Bonjour,

Autre solution avec tableaux emboités (simple et rapide 0,1 sec pour 65.000 lignes)

Code:

1
2
3
4
5
6
7
8
9
10
 
Sub Essai1()
  Dim a(1 To 3)
  a(1) = [B1:B65000]
  a(2) = [C1:C65000]
  a(3) = [F1:F65000]
  lig = 2
  col = 3
  MsgBox a(col)(lig, 1)
End Sub

Code:

1
2
3
4
5
6
7
8
9
10
 
Sub Essai()
  Dim a(1 To 3)
  a(1) = Application.Transpose([B1:B65000])
  a(2) = Application.Transpose([C1:C65000])
  a(3) = Application.Transpose([F1:F65000])
  lig = 2
  col = 3
  MsgBox a(col)(lig)
End Sub

Code:

1
2
3
4
5
6
7
8
9
10
11
12
 
Sub Essai3()
  Set Rng = Range("A1:A100,C1:C100,F1:F10,K1:K10")
  n = Rng.Areas.Count
  Dim a(): ReDim a(1 To n)
  For i = 1 To n
    a(i) = Rng.Areas(i).Value
  Next i
  lig = 2
  col = 4
  MsgBox a(col)(lig, 1)
End Sub

Code:

1
2
3
4
5
6
7
8
9
10
11
12
 
Sub essai4()
  c = Array(1, 3, 6, 11)
  n = UBound(c) + 1
  Dim a(): ReDim a(1 To n)
  For i = 1 To n
    a(i) = Cells(1, c(i - 1)).Resize(10).Value
  Next i
  lig = 2
  col = 4
  MsgBox a(col)(lig, 1)
End Sub

Boisgontier

15/08/2015, 17h17
patricktoulon

re

bonjour marc respect!!:plusser:
une question cependant:
etant un pure vbiste et vbs j'utilise a tord certainement !le moins possible des fonctions excel

dans ton code une question me tarode le mou

si le nombre de ligne est différent pour chaque colonnes ta fonction va elle prendre la plus grande ou le nombre par colonne ?

jacques je connaissais ce system il est bon de se le rapeller :plusser:
15/08/2015, 17h35
Marc-L

Salut Patrick et merci !

Comme on part d'un Range, le nombre de lignes est forcément le même !
Dans l'exemple du code, de A4 à Fx (x étant la dernière ligne), parmi ces six colonnes sont extraites la 1ère, la 2ème et la 6ème …
15/08/2015, 18h43
boisgontierjacques

Citation:

jacques je connaissais ce system il est bon de se le rapeller

Surtout que c'est la méthode la plus rapide (0,1 sec pour 65.000 lignes et 3 colonnes)

Remarque
Les colonnes peuvent avoir un nombre d'items différent.

Boisgontier
15/08/2015, 19h08
patricktoulon

re

Citation:

Envoyé par Marc-L

Salut Patrick et merci !

Comme on part d'un Range, le nombre de lignes est forcément le même !
Dans l'exemple du code, de A4 à Fx (x étant la dernière ligne), parmi ces six colonnes sont extraites la 1ère, la 2ème et la 6ème …

Merci marc mais j ai mal posé ma question
En fait si la F est plus courte en terme de ligne celle qui sont en-dessous dans les autres colonnes
Ne seront pas prises en compte?
15/08/2015, 20h19
Marc-L

Si la plage source est A1:F6, la colonne A a six lignes, la B a six lignes et la F a six lignes !

Même en cas de cellules vides, toutes les colonnes ont le même nombre d'éléments …

Bonsoir le forum, :lol:

Tu peux rempacer .Rows.Count par une variable.

Code:

1
2
3
4
5
6
7
8
With Sheets("Feuil1").Range("A1").CurrentRegion
        derlign = .Range("E" & .Rows.Count).End(xlUp).Row
        a = Application.Index(.Value, Evaluate("row(1:" & _
                                               .Rows.Count & ")"), Array(1, 5, 6, 7, 8))
 
        a = Application.Index(.Value, Evaluate("row(1:" & _
                                               derlign & ")"), Array(1, 5, 6, 7, 8))
End With

klin89

16/08/2015, 09h55
Marc-L

Pour la dernière ligne de la plage source, .Rows.Count est direct !

Remplacer par une variable est utile dans le cas par exemple où il ne faut extraire que les n premières lignes …