XL 2010 Remplacer les cellules vides selon la colonne

[Caroline ;-))]

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]

Hello

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

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 ;-))]

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]

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 ;-))]

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]

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 ;-))]

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]

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 ;-))]

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]

@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]

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]

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!).
à+