Supprimer ligne si #REF ou valeur en cellule (R, i) = 0
Bonjour
J'ai cherché sur le forum et vu plusieurs possiblités mais à chaque fois EXCEL relève des erreurs.
J'ai cette macro pour copier une partie d'une feuille vers une autre (faisant suite à un tableau existant). Je voudrais que, sitôt la copie faite, les ligne avec des valeurs vides en colonne R ou avec erreur #REF soient supprimer.
Voici mon code de copie :
Sub Afficher_Taux()
Dim DLig As Long
Sheets("Taux d'occupation").Visible = True
Worksheets("Taux d'occupation").Range("A2:E200").Clear
' Récupérer la dernière ligne du tableau
DLig = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:A" & DLig & ",B2:B" & DLig & ",D2:D" & DLig & ",M2:M" & DLig).Copy
'Ouvre le fichier ou l'on colle les données
With Sheets("Taux d'occupation").Range("A2").End(xlUp)(2)
.PasteSpecial Paste:=xlPasteValues, Transpose:=False
End With
Sheets("Archives").Activate
DLig = Range("A" & Rows.Count).End(xlUp).Row
Range("A68:A" & DLig & ",B68:B" & DLig & ",D68:D" & DLig & ",M68:M" & DLig & ",BE68:BE" & DLig).Copy
'Ouvre le fichier ou l'on colle les données
With Sheets("Taux d'occupation").Range("A69").End(xlUp)(2)
.PasteSpecial Paste:=xlPasteValues, Transpose:=False
End With
Sheets("Taux d'occupation").Select
Columns("A:A").Select
Selection.Font.Bold = False
Selection.Font.Bold = True
Columns("B:B").Select
Selection.Font.Bold = False
Selection.Font.Bold = True
ActiveWindow.SmallScroll Down:=-12
Columns("C:E").Select
Selection.NumberFormat = "dd/mm/yy;@"
With Selection
.HorizontalAlignment = xlLeft
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub
Pouvez vous m'aider ?
Merci d'avance
Bonjour,
"Je voudrais que, sitôt la copie faite, les ligne avec des valeurs vides en colonne R ou avec erreur #REF soient supprimer."
Si je comprends bien, vous voulez copier les colonnes A, B, D, M et BE de la feuille "Archives" et coller les valeurs à partir de la ligne 68 des colonnes ABCD et à partir de la ligne 69 pour la colonne E de la feuille "Taux d'occupation", Mais qu'il y a -t-il dans la colonne R de la feuille "Taux d'occupation"?
Cdlt
Bonjour
Les colonnes F à Q contiennent le nombre de jour d'occupation de chaque mois (janvier à décembre de l'année en cours) et R le total de jour d'occupation de l'année
Nom | Prénom | Date de naissance | Date d'arrivée | Date de départ | janv-2021 | févr-2021 | mars-2021 | avr-2021 | mai-2021 | juin-2021 | juil-2021 | août-2021 | sept-2021 | oct-2021 | nov-2021 | déc-2021 | Total |
AZERTY | QSDF | 18/07/04 | 17/03/20 | 31 | 28 | 31 | 30 | 31 | 30 | 181 |
On parle de quelle feuille là?
Si vous pouviez déposer un petit fichier (quelques lignes suffisent et sans données confidentielles) on gagnerait du temps.
Voici une version épurée
ça comme mal, je lis:
Worksheets("Taux d'occupation").Range("A2:E200").Clear
' Récupérer la dernière ligne du tableau
DLig = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:A" & DLig & ",B2:B" & DLig & ",D2:D" & DLig & ",M2:M" & DLig).Copy
Vous commencez par un effacement de la plage "A à E", puis vous faites une copie des plages A2:B & DerLig qui viennent d'être effacées, bizarre non !
ça commence mal, je lis:
Worksheets("Taux d'occupation").Range("A2:E200").Clear
' Récupérer la dernière ligne du tableau
DLig = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:A" & DLig & ",B2:B" & DLig & ",D2:D" & DLig & ",M2:M" & DLig).Copy
en premier lieu, vous effacez la plage "A2:E200" de la feuille "Taux d'occupation" puis, vous faites une copie de la plage A et B qui viennent d'être effacées. Là, je ne comprends plus!
J'efface le tableau avant de faire la copie comme actualisation
En fait ce qui m'a induit en erreur, c'est que la feuille active doit être la feuille "ARCHIVES", mais comme ce n'est pas spécifié dans le code , j'ai lancé la macro alors que la feuille active était "Taux d'occupation" et évidemment ça ne collait pas du tout.
Ceci étant dit, je regarde pour votre problème.
Voilà, en espérant avoir bien compris.
Sub Afficher_Taux()
Dim DLig As Long
Dim f1 As Worksheet, f2 As Worksheet
Application.ScreenUpdating = False
Set f1 = Sheets("Taux d'occupation")
Set f2 = Sheets("Archives")
f1.Visible = True
'réécriture des formules
DLig = f1.Range("A" & Rows.Count).End(xlUp).Row
f1.Range("A2:E" & DLig).Clear
f1.Range("F2:F" & DLig).FormulaR1C1 = "=IF(MONTH(TODAY())<MONTH(R1C),"""",IF(ISBLANK(Tableau8[[#This Row],[Nom]]),"""",MAX(0,MIN(EOMONTH(R1C,0),RC5)-MAX(EOMONTH(R1C,-1),RC4-1))))"
f1.Range("R2:R" & DLig).FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
' Récupérer la dernière ligne du tableau
DLig = f2.Range("A" & Rows.Count).End(xlUp).Row
f2.Range("A2:A" & DLig & ",B2:B" & DLig & ",D2:D" & DLig & ",M2:M" & DLig).Copy
'Ouvre le fichier ou l'on colle les données
With f1.Range("A2").End(xlUp)(2)
.PasteSpecial Paste:=xlPasteValues, Transpose:=False
End With
DLig = f2.Range("A" & Rows.Count).End(xlUp).Row
f2.Range("A68:A" & DLig & ",B68:B" & DLig & ",D68:D" & DLig & ",M68:M" & DLig & ",BE68:BE" & DLig).Copy
'Ouvre le fichier ou l'on colle les données
With f1.Range("A69").End(xlUp)(2)
.PasteSpecial Paste:=xlPasteValues, Transpose:=False
End With
For i = DLig To 2 Step -1
On Error Resume Next
If f1.Cells(i, "R") = 0 Or f1.Cells(i, "R") = "" Then
Range(f1.Cells(i, "A"), f1.Cells(i, "R")).Delete
End If
On Error GoTo 0
Next i
With f1
.Columns("A:B").Font.Bold = True
.Columns("C:E").NumberFormat = "dd/mm/yy;@"
.Columns("C:E").HorizontalAlignment = xlLeft
End With
f1.Select
Set f1 = Nothing
Set f2 = Nothing
End Sub
VCdlt
Bonjour
Il y a un bug, cette formule ne prends pas les lignes masquées de la feuille archives et copie plusieurs fois certaines. Elle perd aussi certaine date de départ
Faudrait me donner des exemples de ce qui ne va pas. Nous sommes bien d'accord que dans la feuille "Taux d'occupation" toutes les lignes dont la cellules en colonne R est vide ou égale à 0, ou égale #REF doivent être supprimées.
Je n'est pas touché à cette partie (date de départ, en colonne E de "Archives") et si je reprends votre code, la colonne E n'est pas copiée, est-ce normal ou bien est-ce un oubli ?
Autre question, pourquoi la copie de la ligne suivante commence t-elle à la ligne 68 ?
f2.Range("A68:A" & DLig & ",B68:B" & DLig & ",D68:D" & DLig & ",M68:M" & DLig & ",BE68:BE" & DLig).Copy
je n'ai pas connaissance de tous les éléments, donc c'est un peu normal qu'on rencontre quelques petits soucis.
Y a pas de soucis.
N'étant pas callé mais autodidacte, cette macro est artisanale et aidée par beaucoup de personnes de ce forum, d'où les différences de codage.
Je vais donc reprendre l'idée originale :
1 - Sur le tableau "Effectif", copie des colonnes A, B, D puis coller sur le tableau "Taux d'occupation"
2 - Sur le tableau "Archives", copie des colonnes A, B, D et BE puis coller à la suite sur le tableau "Taux d'occupation"
3 - Enfin, supprimer les lignes si l'année en BE est inférieure à cette année, puis supprimer les cellules avec erreur.
La finalité est d'avoir le taux d'occupation de chaque personne sur l'année en cours pour les bilans annuels
J'avais coller le tableau effectif en amont du tableau archives pour éviter l'étape 1 -
Bonjour,
Je crois que dans un premier temps il faut disposer d'un fichier propre, parce que là c'est un peu la pagaille.
Si je regarde le contenu de la feuille "Archives" et que j'enlève le filtre sur la colonne BE, je vois plein de "#REF" qui apparaissent, tout ça parce que cela fait référence, via une formule, à la feuille "Effectif" et qu'il n'y a pas de corrélation entre-elles et, toujours dans les colonnes "dates" par endroit, des dates sont rentrées en dur, donc déjà là ça ne va pas, ou bien c'est une formule, soit des données saisies manuellement.
Vu les manipulations qu'il faut faire par la suite, il vaut mieux que les dates soient saisies manuellement.
Donc, première des choses à faire de votre côté, c'est de retravailler votre fichier pour qu'il soit conforme afin de le rendre exploitable, sinon ça ne marchera jamais.
Cdlt
ce n'est pas possible de saisir manuellement, c'est un déplacement de donnée par plusieurs personnes.