XL 2010 Remplacer les cellules vides selon la colonne

Caroline ;-)) · 14 Septembre 2018

Bonjour le Forum !

Je suis confrontée au problème suivant : j’ai une grosse base de données (bdd) avec de multiples cellules vides or je ne dois pas en avoir. En effet, j’utilise de multiples macros au fur et à mesure de mon traitement dont certaines nécessitent des bdd sans cellule vide (sinon, cela fausse le résultat !)

J’ai déjà réalisé une recherche sur le forum et ai trouvé des discussions qui semblent similaires, mais qui sont bien plus compliquées que mon cas.

Est-il possible, par une macro, de remplacer toutes les cellules vides, à choisir en fonction de la lettre de la colonne par :
- #NA ou d’autres lettres
- Un chiffre, en général un zéro

Par exemple : dans ma bdd, pour
- La colonne B : remplacer toutes les cellules vides par le chiffre 0
- La colonne F : remplacer toutes les cellules vides par le texte : #NA
- Les colonnes de G à J : remplacer toutes les cellules vides par le chiffre 0
- ou toute la bdd : remplacer toutes les cellules vides par le texte : #NA

Je joins un extrait de ma bdd anonymisée, qui pèse d’ordinaire dans les 300 Mega.

Merci de votre aide et bonne journée !

vgendron · 14 Septembre 2018

Hello

une idée
mais ca semble déjà long avec ton fichier exemple..

VB:

Sub RemplirVides()
Dim Tablo() As Variant

With ActiveSheet
    Tablo = .UsedRange.Value 'on place toute la BBD dans un tablo VBA
   
    For j = LBound(Tablo, 2) To UBound(Tablo, 2) 'pour chaque colonne
        Select Case j
            Case 2, 7, 8, 9, 10 'si on est en colonne B, G, H, I ou J
                Remplacant = 0
            Case Else 'sinon
                Remplacant = "#N/A"
            End Select
           
            For i = LBound(Tablo, 1) To UBound(Tablo, 1) 'sur chaque ligne de la colonne
                If Tablo(i, j) = "" Then Tablo(i, j) = Remplacant
            Next i
    Next j
    .UsedRange = Tablo 'on remet le tablo dans la feuille
End With
End Sub

sinon, pourquoi ne pas passer par la fonction "Remplacer" d'excel?

Caroline ;-)) · 17 Septembre 2018

Bonjour vgendron,

Merci pour ta proposition.
La fonction "remplacer" d'Excel ne fonctionne pas car mes bases sont trop grandes et cela bloque tout.
Même si effectivement avec la base que j'ai soumise au forum (qui est infiniment petite en comparaison de mes véritables bases) ctrl+H marche

base soumise au forum : 17 850 ko
mes bases habituelles : 300 000 Ko (soit près de 17 fois plus grosses)

Aurais-tu d'autres idées ?
A+

Amilo · 17 Septembre 2018

Bonjour le forum, Caroline, vgendron,
Avec Power Query, il est très facile d'obtenir tel résultat avec même un très gros fichier.
Cordialement

Caroline ;-)) · 18 Septembre 2018

Bonjour Amilo,
Je vais regarder de plus près Power Query.
Apparemment, ce n'est pas la même chose que Power Pivot car avec ce dernier, j'ai eu qq problèmes...
Power Pivot n'est pas encore très fiable.

Merci pour l'info.
A+

zebanx · 19 Septembre 2018

Bonjour Caroline, Vgendron, Amilo, le forum

Pour simple remarque d'utilisateur d'excel 2003, le fichier en #1 se raccourci sérieusement en taille sous excel 2003 quand j'enregistre dans un autre fichier en copiant la BDD en copie valeur puis copie formule.*
Ca se regarde vu la taille des bases pour voir s'il n'y a pas moyen d'alléger la BDD au départ (et si PowerQuery ne permet pas de finaliser la demande..).

@+

* L'extraction porte en 2003 sur 320 lignes et remplissage colonne A à AT.
Le fichier sous E2003 fait 2.84MO reduit à 187KO par cette manipulation.

Caroline ;-)) · 19 Septembre 2018

Bonjour zebanx,

Je ne peux pas enregistrer mon fichier (.xlsm) en .xls (2003)
car toutes les données au-delà de 65 536 lignes seront effacées puisque le format 2003 peut contenir au plus 256 colonnes et 65 536 lignes.
En outre, j'ai besoin d'avoir le format xlsm pour faire fonctionner mes macros.

En tout cas, merci d'avoir cherché un moyen de m'aider.
A+

zebanx · 19 Septembre 2018

Re-@Caroline

Je ne sais pas combien de feuilles contient votre fichier mais essayez au moins de faire l'essai d'enregistrer comme présenté en #6 l'une de vos grosses feuilles de bases de données (copie valeurs et copie formule).
Par rapport à votre fichier, le résultat est quand même très spectaculaire.*
Et les copies de modules sont rapides entre fichier.

Enfin bon, j'espère que Power Query saura vous aider car travailler sur un fichier de 300GO, c'est...

Je n'ai pas toutes les informations sur votre "usine à gaz" (j'en ai aussi donc me permets d'utiliser ce terme) mais ça prend peu de temps à tester.

++
zebanx

* pour rappel
fichier 1 : passe de 17MO à 187 ko en xls (2003) et à 70ko en xlsm (2007). Ci-joint le fichier.

Caroline ;-)) · 19 Septembre 2018

Ok zebanx, je verrai ça à l'occasion car quand on voit ce type de message d'erreur,
on n'a pas trop envie
A+

zebanx · 19 Septembre 2018

@caroline.
Faire un copier coller (valeur puis formule) des cellules de la feuille sélectionnée de votre BDD sur une feuille vierge et enregistrer ce fichier en xlsm ne doit vous poser aucun message de ce type puisque votre version comporte un nombre de colonnes / lignes qui ne posera pas de problème...à l'occasion
Bonne fin de journée.

Amilo · 19 Septembre 2018

Bonjour le forum, zebanx,
@zebanx, dans votre message #8, vous vouliez certainement indiquer 300 Mo au lieu de 300 G0 !
Dans son message #1, Caroline mentionne 300 Mo
Je mets ci-dessous un lien évoquant notamment de la taille maxi de Power Query pour information.
Cela vous donnera une idée de la puissance de Power query selon le système de l'ordinateur.
https://fr.slideshare.net/MSDEVMTL/...ratiques-daffaires-power-query-et-power-pivot

Cordialement

zebanx · 19 Septembre 2018

Amilo à dit:
Bonjour le forum, zebanx,
@zebanx, dans votre message #8, vous vouliez certainement indiquer 300 Mo au lieu de 300 G0 !
Dans son message #1, Caroline mentionne 300 Mo
Je mets ci-dessous un lien évoquant notamment de la taille maxi de Power Query pour information.
Cela vous donnera une idée de la puissance de Power query selon le système de l'ordinateur.
https://fr.slideshare.net/MSDEVMTL/...ratiques-daffaires-power-query-et-power-pivot

Cordialement

Merci pour cette remarque @Amillo.
Ah oui.... Ca reste de toute manière difficile à traiter à cette taille (enfin pour moi du moins!).
à+

XL 2010 Remplacer les cellules vides selon la colonne

Caroline ;-))

XLDnaute Junior

vgendron

XLDnaute Barbatruc

Caroline ;-))

XLDnaute Junior

Amilo

XLDnaute Accro

Caroline ;-))

XLDnaute Junior

zebanx

XLDnaute Accro

Caroline ;-))

XLDnaute Junior

zebanx

XLDnaute Accro

Pièces jointes

Caroline ;-))

XLDnaute Junior

zebanx

XLDnaute Accro

Amilo

XLDnaute Accro

zebanx

XLDnaute Accro

Discussions similaires