VBA Excel Array

Version imprimable

01/11/2016, 09h40
ecornut

VBA Excel Array

Bonjour - j'ai un tableau ( 10 colonne ) pour la lire et la mémoriser avec array aucun problème, pour copier les colonnes 1, 3 et 5 par exemple, j'ai un black out.. J'arrive à recopier la totalité des 10 colonnes .. je devrai ensuite supprimer les colonnes dont je n'ai pas besoin .. mais je pense qu il y a beaucoup plus simple, Merci pour votre aide ( Manu )

Bonjour

oui en effet il y a beaucoup plus simple
voici un exemple qui copy ta plage A1:J10 sans les colonne 1,3,5
et les colle a partir de la ligne 15 su le même sheets
Code:

1 2 3 4 5 6 Sub Macro1() Range("A:A,C:C,E:E").EntireColumn.Hidden = True Range("A1:J10").SpecialCells(xlCellTypeVisible).Copy Cells(15, 1) Range("A:A,C:C,E:E").EntireColumn.Hidden = False End Sub
tu devrais t'en sortir avec cela c'est pas trop compliqué
espérant que cela soit moins flou pour toi
modification pour cette méthode suite a mon ereur d'interprétation signaler par le vieux sanglier préhistorique
Code:

1 2 3 4 5 6 Sub Macro1() range ("A:J").EntireColumn.Hidden = True Range("A:A,C:C,E:E").EntireColumn.Hidden = false Range("A1:J10").SpecialCells(xlCellTypeVisible).Copy Cells(15, 1) range ("A:J").EntireColumn.Hidden = false End Sub
mille excuse au demandeur

Bonjour, coucou Patrick ;) ,

autre solution à adapter :

Code:

1
2
3
4
5
6
7
8
Sub toto()
 
    With Feuil1.UsedRange
        VA = Application.Index(.Value, Evaluate("ROW(1:" & .Rows.Count & ")"), [{1,3,5}]) ' Code donné par Marc-L :1, 3 et 5 sont les colonnes choisies
    End With 'on peut aussi choisir l'ordre des colonnes comme [{5,3,1}] par exemple
    Feuil2.Range("A1").Resize(UBound(VA, 1), UBound(VA, 2)) = VA
 
End Sub

01/11/2016, 11h13
patricktoulon

re

bien RYU mais c'est l'inverse que l'on veut les colonnes 1,3,5 doivent sauter :ptdr:

tu t'es enfin permis d'utiliser app.index hein ca y est t'a compris :mouarf:

+1 car le code est propre
01/11/2016, 11h16
RyuAutodidacte

oui index c'est top et bien pratique dans des cas ;)

Edit : astucieux l'idée de cacher les colonnes je t'ai mis :plusser:

Citation:
Envoyé par patricktoulon
Bonjour

oui en effet il y a beaucoup plus simple
voici un exemple qui copy ta plage A1:J10 sans les colonne 1,3,5
et les colle a partir de la ligne 15 su le même sheets
Code:

1 2 3 4 5 Sub Macro1() Range("A:A,C:C,E:E").EntireColumn.Hidden = True Range("A1:J10").SpecialCells(xlCellTypeVisible).Copy Cells(15, 1) Range("A:A,C:C,E:E").EntireColumn.Hidden = False End Sub
tu devrais t'en sortir avec cela c'est pas trop compliqué
espérant que cela soit moins flou pour toi
Merci, c est une solution .. mon exemple était "simple" - je veux utiliser une fonction array pour une BD beaucoup plus volumineuse j ai d ailleurs utilisé le code suivant :
Code:

1 2 3 4 5 6 7 8 9 10 For i = 1 To 5 ActiveCell.Value = vBD(i, 1) ActiveCell.Offset(0, 1).Select ActiveCell.Value = vBD(i, 9) ActiveCell.Offset(0, 1).Select ActiveCell.Value = vBD(i, 3) ActiveCell.Offset(1, -2).Select Next i

re
ce que veux le demandeur

Code:

1
2
3
4
5
6
7
8
Sub toto()
 dim tableau 
    With Feuil1.UsedRange
        tableau = Application.Index(.Value, Evaluate("ROW(1:" & .Rows.Count & ")"), [{2,4,6,7,8,9,10}]) ' Code donné par Marc-L :1, 3 et 5 sont les colonnes choisies
    End With 'on peut aussi choisir l'ordre des colonnes comme [{5,3,1}] par exemple
    Feuil2.Range("A15").Resize(UBound(tableau, 1), UBound(tableau, 2)) = tableau
 
End Sub

@econut
les deux propositions sont parfaitement aptes a traiter des tableaux de grande taille il suffit d'adapter la plage

attention a usedrange quand même!!! des cellules non concernées par ton tableaux peuvent être prises en compte adapte plutôt ta plage textuellement

Code:

1
2
3
4
5
6
7
8
Sub toto()
 
    With Feuil1.range("A1:J"& cells(rows.count,1).end(xlup).row)
        VA = Application.Index(.Value, Evaluate("ROW(1:" & .Rows.Count & ")"), [{2,4,6,7,8,9,10}]) ' Code donné par Marc-L :1, 3 et 5 sont les colonnes choisies
    End With 'on peut aussi choisir l'ordre des colonnes comme [{5,3,1}] par exemple
    Feuil2.Range("A15").Resize(UBound(VA, 1), UBound(VA, 2)) = VA
 
End Sub

01/11/2016, 11h30
RyuAutodidacte

he oui @ecornut, simple à utiliser, tu peux même effacer les données de la même feuille pour les copier par la suite dans cette même feuille puisque les données sont mémorisées dans le tableau
Et comme dit plus haut solution à adapter

re
non ceci est tout bonnement inacceptable
Code:

1 2 3 4 5 6 7 8 9 10 For i = 1 To 5 ActiveCell.Value = vBD(i, 1) ActiveCell.Offset(0, 1).Select ActiveCell.Value = vBD(i, 9) ActiveCell.Offset(0, 1).Select ActiveCell.Value = vBD(i, 3) ActiveCell.Offset(1, -2).Select Next i
d'autant plus que tu parle de tableau volumineux 1 select par cellule visitée wahouh!!! si tu a du temps a perdre OK :mouarf:

re
@Ryu

Citation:

Edit : astucieux l'idée de cacher les colonnes je t'ai mis :plusser:

je m'adapte au niveau du demandeur

01/11/2016, 11h44
RyuAutodidacte

En effet, c'est la solution la plus simple quand on débute ;)

Citation:

Envoyé par patricktoulon

re
ce que veux le demandeur

Code:

1
2
3
4
5
6
7
8
Sub toto()
 dim tableau 
    With Feuil1.UsedRange
        tableau = Application.Index(.Value, Evaluate("ROW(1:" & .Rows.Count & ")"), [{2,4,6,7,8,9,10}]) ' Code donné par Marc-L :1, 3 et 5 sont les colonnes choisies
    End With 'on peut aussi choisir l'ordre des colonnes comme [{5,3,1}] par exemple
    Feuil2.Range("A15").Resize(UBound(tableau, 1), UBound(tableau, 2)) = tableau
 
End Sub

Bonjour à tous,
Code:

1 2 3 With Feuil1.UsedRange VA = Application.Index(.Value, Evaluate("ROW(1:" & .Rows.Count & ")"), [{1,3,5}]) ' Code donné par Marc-L :1, 3 et 5 sont les colonnes choisies End With 'on peut aussi choisir l'ordre des colonnes comme [{5,3,1}] par exemple
Concis, souple et intéressant.
A condition d'être sûr de ne jamais dépasser 65536 lignes (même dans 3 ans si les données s'accumulent)
eric

01/11/2016, 12h34
jurassic pork

hello,
heu il est quand même fort le Patrick. De la demande initiale :

Citation:

Envoyé par ecornut

Bonjour - j'ai un tableau ( 10 colonne ) pour la lire et la mémoriser avec array aucun problème, pour copier les colonnes 1, 3 et 5 par exemple, j'ai un black out.

Il est arrivé à faire croire à tout le monde que c'était l'inverse qui était demandé (même au demandeur) :P :

Citation:

Envoyé par patricktoulon

bien RYU mais c'est l'inverse que l'on veut les colonnes 1,3,5 doivent sauter.

Où alors c'est moi qui ne comprend plus le français :aie:

Ami calmant, J.P
01/11/2016, 12h42
patricktoulon

re

:ptdr::ptdr:
tu a raison hihihihihi!!

je traite 2 sujet similaire en même temps l'autre c'est l'inverse

:ptdr::ptdr:je ferais un bon Gourou moi hahahaha

Citation:

Envoyé par eriiic

Concis, souple et intéressant.
A condition d'être sûr de ne jamais dépasser 65536 lignes (même dans 3 ans si les données s'accumulent)
eric

Bien vu, je n'ai jamais été confronté à ce problème. Etant donné que je suis en train d'apprendre j'aimerai bien une explication de cette limitation stp.

je ne suis pas sur de ce que vont penser les cadors mais pour pallier à cette limitation et garder le coté souple du choix des colonnes et quelque soit le nombre de de lignes

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sub Titi()
    HTab = Feuil1.Cells(Rows.Count, 1).End(xlUp).Row 'à modifier selon le critère de la colonne maitre en nombre de lignes
    Xlig = Int(HTab / 65536) + 1
 
Application.ScreenUpdating = False
    For i = 1 To Xlig
 
    If i = Xlig Then M = 65536 - (HTab Mod 65536)
 
        With Feuil1.Range("A" & 1 + (i - 1) * 65536 - M & ":" & "J" & 65536 + (i - 1) * 65536 - M)
            VA = Application.Index(.Value, Evaluate("ROW(1:" & .Rows.Count & ")"), [{1,3,1}]) ' Code donné par Marc-L :1, 3 et 5 sont les colonnes choisies
        End With 'on peut aussi choisir l'ordre des colonnes comme [{5,3,1}] par exemple
        Feuil2.Range("A" & 1 + (i - 1) * 65536 - M).Resize(UBound(VA, 1), UBound(VA, 2)) = VA
 
    Next
Application.ScreenUpdating = True
End Sub

On reste que sur une boucle comme cela. je sais qu'il existe plusieurs façon de faire s'en est une de plus. A vous de juger de son efficacité

Voilà

Edit : Modification du code pour que tous les contextes soit pris :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sub Titi()
    HTab = Feuil1.Cells(Rows.Count, 1).End(xlUp).Row
    Xlig = Int(HTab / 65536) + 1
 
Application.ScreenUpdating = False
    For i = 1 To Xlig
 
    If Xlig = 1 Then M = 0 Else If Xlig = i Then M = 65536 - (HTab Mod 65536)
 
        With Feuil1.Range("A" & 1 + (i - 1) * 65536 - M & ":" & "J" & 65536 + (i - 1) * 65536 - M)
            VA = Application.Index(.Value, Evaluate("ROW(1:" & .Rows.Count & ")"), [{1,3,1}]) ' Code donné par Marc-L :1, 3 et 5 sont les colonnes choisies
        End With 'on peut aussi choisir l'ordre des colonnes comme [{5,3,1}] par exemple
        Feuil2.Range("A" & 1 + (i - 1) * 65536 - M).Resize(UBound(VA, 1), UBound(VA, 2)) = VA
 
    Next
Application.ScreenUpdating = True
End Sub

bon je pense que l'on peut faire encore une petite amélioration, à voir …

Edit 2 : je me suis créé des fausses données avec ça :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
Sub FaussesDonnees()
Application.ScreenUpdating = False
    ReDim VA(1 To 500000, 1 To 10)
        For i = 1 To UBound(VA, 1)
            For j = 1 To UBound(VA, 2)
                x = x + 1
                VA(i, j) = x
            Next
        Next
    Feuil1.Range("A1").Resize(UBound(VA, 1), UBound(VA, 2)) = VA
Application.ScreenUpdating = True
End Sub

01/11/2016, 16h53
eriiic

C'est une limitation de Index, tout comme Transpose.
Sans doute n'ont-ils pas pu/voulu faire évoluer à l'arrivée de 2007 pour une raison qu'on ignore (limiter les incompatibilités descendantes ?)
01/11/2016, 16h57
RyuAutodidacte

Merci pour l'explication, surtout que pour transpose je ne le savais pas non plus
merci :ccool:
01/11/2016, 17h29
joe.levrai

oui, cette limitation du Transpose sur 2^16 grrr

j'avais déjà du rallonger une proposition du double de sa longueur, juste pour gérer ça

http://www.developpez.net/forums/d15...s/#post8259216

EDIT : https://newtonexcelbach.wordpress.co...2013-and-2016/
intéressant pour bien comprendre les effets de bords possibles sur les versions récentes
01/11/2016, 17h39
RyuAutodidacte

en effet je vois qu'on a utilisé quasi le même principe à peu de chose près :D :ccool:
01/11/2016, 18h03
patricktoulon

re

perso je comprends pas votre dernier débats
un tableau (variable variant) peut contenir autant de ligne que ce que vous propose Excel en terme de ligne dans un sheets

parti de la vous m'avez perdu ;)
01/11/2016, 18h12
eriiic

Un tableau oui, mais pas la fonction Index qui est utilisée. Limitée à 65536 lignes.
PS : dans ce cas c'est peut-être plus simple de revenir à ta proposition avec colonnes masquées
01/11/2016, 20h19
Marc-L

Bonjour,

pour le Transpose, en dehors d'une classique boucle avec des variables tableau, on peut dépasser la limite via un objet Windows …

Mais ces diverses limitations internes d'Excel confirment bien qu'il n'a pas la vocation de gérer des bases de données !
Mieux vaut alors se tourner vers Access ou autre SGBD face à une de ces limitations, sans compter la fiabilité, la sécurité, …

_________________________________________________________________________________________________________
Je suis Paris, Nice, Bruxelles, Charlie, …

Bonsoir,
La petite amélioration dont je parlais, comme cela c'est assez malléable et ça fait aussi bien une petite plage qu'une grande plage
en outre on pallie à la limite sur Index des 65536 lignes comme l'a indiqué eriic
et bien sur à n'utilisé que dans le contexte d'excel car comme la si bien signalé Marc-L (que je salue au passage) excel n'est pas une SGBD …
J'ai tenu à faire ce code car il peut être très pratique dans bien des situations donc à prendre ou à laisser
Voilà

le code maintenant :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Sub IndexPlage() 'Base du code principal Marc-L 'Ajout/modification RyuAutodidacte 03/11/2016 HTab = Feuil1.Cells(Rows.Count, 1).End(xlUp).Row ' Xlig = Int(HTab / 65536) + 1 Application.ScreenUpdating = False For i = 1 To Xlig If Xlig = 1 Then M = 0: M2 = 65536 - (HTab Mod 65536) Else If Xlig = i Then M = 65536 - (HTab Mod 65536) With Feuil1.Range("A" & 1 + (i - 1) * 65536 - M & ":" & "J" & 65536 + (i - 1) * 65536 - M - M2) ' colonne (ici "A" et "J") à adapter selon critères 'Attention code basé sur la 1è ligne donc prendre en compte le décalage si il y a VA = Application.Index(.Value, Evaluate("ROW(1:" & .Rows.Count & ")"), [{1,3,1}]) ' Code donné par Marc-L :1, 3 et 5 sont les colonnes choisies End With 'on peut aussi choisir l'ordre des colonnes comme [{5,3,1}] par exemple Feuil2.Range("A" & 1 + (i - 1) * 65536 - M).Resize(UBound(VA, 1), UBound(VA, 2)) = VA Next Application.ScreenUpdating = True End Sub
et là je viens de penser que l'on pourrait rajouter dans les paramètres le décalage … pfff :D, je verrai cela plus tard