UsedRange.Count comptant un problème

Résumé: Je prends une rangée de données d'une feuille et la collage dans une autre, mais la feuille serait un type d'utilisation quotidienne où les nouveldatatables entrent juste sous les anciennes données.

Problème: sur chaque nouvelle exécution, 7 est toujours ajouté à UsedRange.Count . Par exemple: sur une course, UsedRange.Count sera 7; la prochaine fois que je parcourra la fonction, le count sera 14.

Ce que je cherche: pourquoi est-ce le cas et existe-t-il un moyen d'aider UsedRange être plus précis

-J'ai inclus l'intégralité de la fonction pour les references.

 Function eftGrabber() Dim usedRows As Integer Dim i As Integer ChDir "\\..." Workbooks.Open Filename:= _ "\\...\eftGrabber.xlsm" usedRows = Sheets("EFT").UsedRange.Count Windows("Data").Activate Sheets("DataSheet").Range("A11").EntireRow.Copy Windows("eftGrabber").Activate Sheets("EFT").Range("A" & usedRows + 1).Select ActiveSheet.Paste i = usedRows Do 'THIS LOOP DELETES BLANKS AFTER POSTING NEW LINES Range("A" & i).Select If Range("A" & i) = "" Then ActiveCell.EntireRow.Delete End If i = i - 1 Loop Until i = 1 Windows("eftGrabber").Activate ActiveWorkbook.Save Windows("eftGrabber").Close End Function Fonction eftGrabber () Function eftGrabber() Dim usedRows As Integer Dim i As Integer ChDir "\\..." Workbooks.Open Filename:= _ "\\...\eftGrabber.xlsm" usedRows = Sheets("EFT").UsedRange.Count Windows("Data").Activate Sheets("DataSheet").Range("A11").EntireRow.Copy Windows("eftGrabber").Activate Sheets("EFT").Range("A" & usedRows + 1).Select ActiveSheet.Paste i = usedRows Do 'THIS LOOP DELETES BLANKS AFTER POSTING NEW LINES Range("A" & i).Select If Range("A" & i) = "" Then ActiveCell.EntireRow.Delete End If i = i - 1 Loop Until i = 1 Windows("eftGrabber").Activate ActiveWorkbook.Save Windows("eftGrabber").Close End Function Dim usedRows As Integer Function eftGrabber() Dim usedRows As Integer Dim i As Integer ChDir "\\..." Workbooks.Open Filename:= _ "\\...\eftGrabber.xlsm" usedRows = Sheets("EFT").UsedRange.Count Windows("Data").Activate Sheets("DataSheet").Range("A11").EntireRow.Copy Windows("eftGrabber").Activate Sheets("EFT").Range("A" & usedRows + 1).Select ActiveSheet.Paste i = usedRows Do 'THIS LOOP DELETES BLANKS AFTER POSTING NEW LINES Range("A" & i).Select If Range("A" & i) = "" Then ActiveCell.EntireRow.Delete End If i = i - 1 Loop Until i = 1 Windows("eftGrabber").Activate ActiveWorkbook.Save Windows("eftGrabber").Close End Function Dim i As Integer Function eftGrabber() Dim usedRows As Integer Dim i As Integer ChDir "\\..." Workbooks.Open Filename:= _ "\\...\eftGrabber.xlsm" usedRows = Sheets("EFT").UsedRange.Count Windows("Data").Activate Sheets("DataSheet").Range("A11").EntireRow.Copy Windows("eftGrabber").Activate Sheets("EFT").Range("A" & usedRows + 1).Select ActiveSheet.Paste i = usedRows Do 'THIS LOOP DELETES BLANKS AFTER POSTING NEW LINES Range("A" & i).Select If Range("A" & i) = "" Then ActiveCell.EntireRow.Delete End If i = i - 1 Loop Until i = 1 Windows("eftGrabber").Activate ActiveWorkbook.Save Windows("eftGrabber").Close End Function ChDir "\\ ..." Function eftGrabber() Dim usedRows As Integer Dim i As Integer ChDir "\\..." Workbooks.Open Filename:= _ "\\...\eftGrabber.xlsm" usedRows = Sheets("EFT").UsedRange.Count Windows("Data").Activate Sheets("DataSheet").Range("A11").EntireRow.Copy Windows("eftGrabber").Activate Sheets("EFT").Range("A" & usedRows + 1).Select ActiveSheet.Paste i = usedRows Do 'THIS LOOP DELETES BLANKS AFTER POSTING NEW LINES Range("A" & i).Select If Range("A" & i) = "" Then ActiveCell.EntireRow.Delete End If i = i - 1 Loop Until i = 1 Windows("eftGrabber").Activate ActiveWorkbook.Save Windows("eftGrabber").Close End Function Workbooks.Open Nom du file: = _ Function eftGrabber() Dim usedRows As Integer Dim i As Integer ChDir "\\..." Workbooks.Open Filename:= _ "\\...\eftGrabber.xlsm" usedRows = Sheets("EFT").UsedRange.Count Windows("Data").Activate Sheets("DataSheet").Range("A11").EntireRow.Copy Windows("eftGrabber").Activate Sheets("EFT").Range("A" & usedRows + 1).Select ActiveSheet.Paste i = usedRows Do 'THIS LOOP DELETES BLANKS AFTER POSTING NEW LINES Range("A" & i).Select If Range("A" & i) = "" Then ActiveCell.EntireRow.Delete End If i = i - 1 Loop Until i = 1 Windows("eftGrabber").Activate ActiveWorkbook.Save Windows("eftGrabber").Close End Function "\\ ... \ eftGrabber.xlsm" Function eftGrabber() Dim usedRows As Integer Dim i As Integer ChDir "\\..." Workbooks.Open Filename:= _ "\\...\eftGrabber.xlsm" usedRows = Sheets("EFT").UsedRange.Count Windows("Data").Activate Sheets("DataSheet").Range("A11").EntireRow.Copy Windows("eftGrabber").Activate Sheets("EFT").Range("A" & usedRows + 1).Select ActiveSheet.Paste i = usedRows Do 'THIS LOOP DELETES BLANKS AFTER POSTING NEW LINES Range("A" & i).Select If Range("A" & i) = "" Then ActiveCell.EntireRow.Delete End If i = i - 1 Loop Until i = 1 Windows("eftGrabber").Activate ActiveWorkbook.Save Windows("eftGrabber").Close End Function usedRows = Sheets ("EFT"). UsedRange.Count Function eftGrabber() Dim usedRows As Integer Dim i As Integer ChDir "\\..." Workbooks.Open Filename:= _ "\\...\eftGrabber.xlsm" usedRows = Sheets("EFT").UsedRange.Count Windows("Data").Activate Sheets("DataSheet").Range("A11").EntireRow.Copy Windows("eftGrabber").Activate Sheets("EFT").Range("A" & usedRows + 1).Select ActiveSheet.Paste i = usedRows Do 'THIS LOOP DELETES BLANKS AFTER POSTING NEW LINES Range("A" & i).Select If Range("A" & i) = "" Then ActiveCell.EntireRow.Delete End If i = i - 1 Loop Until i = 1 Windows("eftGrabber").Activate ActiveWorkbook.Save Windows("eftGrabber").Close End Function Windows ("Données"). Activer Function eftGrabber() Dim usedRows As Integer Dim i As Integer ChDir "\\..." Workbooks.Open Filename:= _ "\\...\eftGrabber.xlsm" usedRows = Sheets("EFT").UsedRange.Count Windows("Data").Activate Sheets("DataSheet").Range("A11").EntireRow.Copy Windows("eftGrabber").Activate Sheets("EFT").Range("A" & usedRows + 1).Select ActiveSheet.Paste i = usedRows Do 'THIS LOOP DELETES BLANKS AFTER POSTING NEW LINES Range("A" & i).Select If Range("A" & i) = "" Then ActiveCell.EntireRow.Delete End If i = i - 1 Loop Until i = 1 Windows("eftGrabber").Activate ActiveWorkbook.Save Windows("eftGrabber").Close End Function Sheets ("DataSheet"). Gamme ("A11"). EntireRow.Copy Function eftGrabber() Dim usedRows As Integer Dim i As Integer ChDir "\\..." Workbooks.Open Filename:= _ "\\...\eftGrabber.xlsm" usedRows = Sheets("EFT").UsedRange.Count Windows("Data").Activate Sheets("DataSheet").Range("A11").EntireRow.Copy Windows("eftGrabber").Activate Sheets("EFT").Range("A" & usedRows + 1).Select ActiveSheet.Paste i = usedRows Do 'THIS LOOP DELETES BLANKS AFTER POSTING NEW LINES Range("A" & i).Select If Range("A" & i) = "" Then ActiveCell.EntireRow.Delete End If i = i - 1 Loop Until i = 1 Windows("eftGrabber").Activate ActiveWorkbook.Save Windows("eftGrabber").Close End Function Windows ("eftGrabber"). Activer Function eftGrabber() Dim usedRows As Integer Dim i As Integer ChDir "\\..." Workbooks.Open Filename:= _ "\\...\eftGrabber.xlsm" usedRows = Sheets("EFT").UsedRange.Count Windows("Data").Activate Sheets("DataSheet").Range("A11").EntireRow.Copy Windows("eftGrabber").Activate Sheets("EFT").Range("A" & usedRows + 1).Select ActiveSheet.Paste i = usedRows Do 'THIS LOOP DELETES BLANKS AFTER POSTING NEW LINES Range("A" & i).Select If Range("A" & i) = "" Then ActiveCell.EntireRow.Delete End If i = i - 1 Loop Until i = 1 Windows("eftGrabber").Activate ActiveWorkbook.Save Windows("eftGrabber").Close End Function Sheets ("EFT"). Plage ("A" et utiliséRows + 1) .Sélectionnez Function eftGrabber() Dim usedRows As Integer Dim i As Integer ChDir "\\..." Workbooks.Open Filename:= _ "\\...\eftGrabber.xlsm" usedRows = Sheets("EFT").UsedRange.Count Windows("Data").Activate Sheets("DataSheet").Range("A11").EntireRow.Copy Windows("eftGrabber").Activate Sheets("EFT").Range("A" & usedRows + 1).Select ActiveSheet.Paste i = usedRows Do 'THIS LOOP DELETES BLANKS AFTER POSTING NEW LINES Range("A" & i).Select If Range("A" & i) = "" Then ActiveCell.EntireRow.Delete End If i = i - 1 Loop Until i = 1 Windows("eftGrabber").Activate ActiveWorkbook.Save Windows("eftGrabber").Close End Function ActiveSheet.Paste Function eftGrabber() Dim usedRows As Integer Dim i As Integer ChDir "\\..." Workbooks.Open Filename:= _ "\\...\eftGrabber.xlsm" usedRows = Sheets("EFT").UsedRange.Count Windows("Data").Activate Sheets("DataSheet").Range("A11").EntireRow.Copy Windows("eftGrabber").Activate Sheets("EFT").Range("A" & usedRows + 1).Select ActiveSheet.Paste i = usedRows Do 'THIS LOOP DELETES BLANKS AFTER POSTING NEW LINES Range("A" & i).Select If Range("A" & i) = "" Then ActiveCell.EntireRow.Delete End If i = i - 1 Loop Until i = 1 Windows("eftGrabber").Activate ActiveWorkbook.Save Windows("eftGrabber").Close End Function i = usedRows Function eftGrabber() Dim usedRows As Integer Dim i As Integer ChDir "\\..." Workbooks.Open Filename:= _ "\\...\eftGrabber.xlsm" usedRows = Sheets("EFT").UsedRange.Count Windows("Data").Activate Sheets("DataSheet").Range("A11").EntireRow.Copy Windows("eftGrabber").Activate Sheets("EFT").Range("A" & usedRows + 1).Select ActiveSheet.Paste i = usedRows Do 'THIS LOOP DELETES BLANKS AFTER POSTING NEW LINES Range("A" & i).Select If Range("A" & i) = "" Then ActiveCell.EntireRow.Delete End If i = i - 1 Loop Until i = 1 Windows("eftGrabber").Activate ActiveWorkbook.Save Windows("eftGrabber").Close End Function Do 'THIS LOOP DELETES BLANGE APRÈS POSTER DE NOUVELLES LIGNES Function eftGrabber() Dim usedRows As Integer Dim i As Integer ChDir "\\..." Workbooks.Open Filename:= _ "\\...\eftGrabber.xlsm" usedRows = Sheets("EFT").UsedRange.Count Windows("Data").Activate Sheets("DataSheet").Range("A11").EntireRow.Copy Windows("eftGrabber").Activate Sheets("EFT").Range("A" & usedRows + 1).Select ActiveSheet.Paste i = usedRows Do 'THIS LOOP DELETES BLANKS AFTER POSTING NEW LINES Range("A" & i).Select If Range("A" & i) = "" Then ActiveCell.EntireRow.Delete End If i = i - 1 Loop Until i = 1 Windows("eftGrabber").Activate ActiveWorkbook.Save Windows("eftGrabber").Close End Function Gamme ("A" & i) .Sélectionnez Function eftGrabber() Dim usedRows As Integer Dim i As Integer ChDir "\\..." Workbooks.Open Filename:= _ "\\...\eftGrabber.xlsm" usedRows = Sheets("EFT").UsedRange.Count Windows("Data").Activate Sheets("DataSheet").Range("A11").EntireRow.Copy Windows("eftGrabber").Activate Sheets("EFT").Range("A" & usedRows + 1).Select ActiveSheet.Paste i = usedRows Do 'THIS LOOP DELETES BLANKS AFTER POSTING NEW LINES Range("A" & i).Select If Range("A" & i) = "" Then ActiveCell.EntireRow.Delete End If i = i - 1 Loop Until i = 1 Windows("eftGrabber").Activate ActiveWorkbook.Save Windows("eftGrabber").Close End Function Si Range ("A" & i) = "" Ensuite Function eftGrabber() Dim usedRows As Integer Dim i As Integer ChDir "\\..." Workbooks.Open Filename:= _ "\\...\eftGrabber.xlsm" usedRows = Sheets("EFT").UsedRange.Count Windows("Data").Activate Sheets("DataSheet").Range("A11").EntireRow.Copy Windows("eftGrabber").Activate Sheets("EFT").Range("A" & usedRows + 1).Select ActiveSheet.Paste i = usedRows Do 'THIS LOOP DELETES BLANKS AFTER POSTING NEW LINES Range("A" & i).Select If Range("A" & i) = "" Then ActiveCell.EntireRow.Delete End If i = i - 1 Loop Until i = 1 Windows("eftGrabber").Activate ActiveWorkbook.Save Windows("eftGrabber").Close End Function ActiveCell.EntireRow.Delete Function eftGrabber() Dim usedRows As Integer Dim i As Integer ChDir "\\..." Workbooks.Open Filename:= _ "\\...\eftGrabber.xlsm" usedRows = Sheets("EFT").UsedRange.Count Windows("Data").Activate Sheets("DataSheet").Range("A11").EntireRow.Copy Windows("eftGrabber").Activate Sheets("EFT").Range("A" & usedRows + 1).Select ActiveSheet.Paste i = usedRows Do 'THIS LOOP DELETES BLANKS AFTER POSTING NEW LINES Range("A" & i).Select If Range("A" & i) = "" Then ActiveCell.EntireRow.Delete End If i = i - 1 Loop Until i = 1 Windows("eftGrabber").Activate ActiveWorkbook.Save Windows("eftGrabber").Close End Function i = i - 1 Function eftGrabber() Dim usedRows As Integer Dim i As Integer ChDir "\\..." Workbooks.Open Filename:= _ "\\...\eftGrabber.xlsm" usedRows = Sheets("EFT").UsedRange.Count Windows("Data").Activate Sheets("DataSheet").Range("A11").EntireRow.Copy Windows("eftGrabber").Activate Sheets("EFT").Range("A" & usedRows + 1).Select ActiveSheet.Paste i = usedRows Do 'THIS LOOP DELETES BLANKS AFTER POSTING NEW LINES Range("A" & i).Select If Range("A" & i) = "" Then ActiveCell.EntireRow.Delete End If i = i - 1 Loop Until i = 1 Windows("eftGrabber").Activate ActiveWorkbook.Save Windows("eftGrabber").Close End Function Loop jusqu'à i = 1 Function eftGrabber() Dim usedRows As Integer Dim i As Integer ChDir "\\..." Workbooks.Open Filename:= _ "\\...\eftGrabber.xlsm" usedRows = Sheets("EFT").UsedRange.Count Windows("Data").Activate Sheets("DataSheet").Range("A11").EntireRow.Copy Windows("eftGrabber").Activate Sheets("EFT").Range("A" & usedRows + 1).Select ActiveSheet.Paste i = usedRows Do 'THIS LOOP DELETES BLANKS AFTER POSTING NEW LINES Range("A" & i).Select If Range("A" & i) = "" Then ActiveCell.EntireRow.Delete End If i = i - 1 Loop Until i = 1 Windows("eftGrabber").Activate ActiveWorkbook.Save Windows("eftGrabber").Close End Function Windows ("eftGrabber"). Activer Function eftGrabber() Dim usedRows As Integer Dim i As Integer ChDir "\\..." Workbooks.Open Filename:= _ "\\...\eftGrabber.xlsm" usedRows = Sheets("EFT").UsedRange.Count Windows("Data").Activate Sheets("DataSheet").Range("A11").EntireRow.Copy Windows("eftGrabber").Activate Sheets("EFT").Range("A" & usedRows + 1).Select ActiveSheet.Paste i = usedRows Do 'THIS LOOP DELETES BLANKS AFTER POSTING NEW LINES Range("A" & i).Select If Range("A" & i) = "" Then ActiveCell.EntireRow.Delete End If i = i - 1 Loop Until i = 1 Windows("eftGrabber").Activate ActiveWorkbook.Save Windows("eftGrabber").Close End Function ActiveWorkbook.Save Function eftGrabber() Dim usedRows As Integer Dim i As Integer ChDir "\\..." Workbooks.Open Filename:= _ "\\...\eftGrabber.xlsm" usedRows = Sheets("EFT").UsedRange.Count Windows("Data").Activate Sheets("DataSheet").Range("A11").EntireRow.Copy Windows("eftGrabber").Activate Sheets("EFT").Range("A" & usedRows + 1).Select ActiveSheet.Paste i = usedRows Do 'THIS LOOP DELETES BLANKS AFTER POSTING NEW LINES Range("A" & i).Select If Range("A" & i) = "" Then ActiveCell.EntireRow.Delete End If i = i - 1 Loop Until i = 1 Windows("eftGrabber").Activate ActiveWorkbook.Save Windows("eftGrabber").Close End Function Windows ("eftGrabber"). Fermer Function eftGrabber() Dim usedRows As Integer Dim i As Integer ChDir "\\..." Workbooks.Open Filename:= _ "\\...\eftGrabber.xlsm" usedRows = Sheets("EFT").UsedRange.Count Windows("Data").Activate Sheets("DataSheet").Range("A11").EntireRow.Copy Windows("eftGrabber").Activate Sheets("EFT").Range("A" & usedRows + 1).Select ActiveSheet.Paste i = usedRows Do 'THIS LOOP DELETES BLANKS AFTER POSTING NEW LINES Range("A" & i).Select If Range("A" & i) = "" Then ActiveCell.EntireRow.Delete End If i = i - 1 Loop Until i = 1 Windows("eftGrabber").Activate ActiveWorkbook.Save Windows("eftGrabber").Close End Function

Permettez-moi de savoir si j'ai laissé de côté des détails importants. Merci d'avance!

Changer: usedRows = Sheets("EFT").UsedRange.Count

Pour: usedRows = Sheets("EFT").Range("A" & Sheets("EFT").Rows.Count).End(xlUp).Row

Où "A" peut être changé pour n'importe quelle rangée que vous souhaitez countr le nombre total de colonnes.

Il existe un danger pour l'utilisation d' UsedRange car cela influe sur de telles choses et sur des cellules formatées sans données et autres choses qui peuvent vous donner des résultats inattendus, comme si vous attendez que vos données commencent dans Range ("A1"), mais ça commence vraiment dans une autre gamme!

Je dirai cependant que si vous souhaitez vraiment utiliser UsedRange , votre code ci-dessus est toujours faux d'get les lignes. Utilisez ceci plutôt UsedRange.Rows.Count ou pour get la dernière cellule absolue de UsedRange, utilisez UsedRange.SpecialCells(xlCellTypeLastCell).Row

Cette ligne deux fait la magie

  usedCol = ThisWorkbook.ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column usedRow = ThisWorkbook.ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row usedCol = ThisWorkbook.ActiveSheet.UsedRange.SpecialCells (xlCellTypeLastCell) .Column  usedCol = ThisWorkbook.ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column usedRow = ThisWorkbook.ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

Pour plus d'informations, visitez le site de Microsoft

http://msdn.microsoft.com/en-us/library/office/ff196157.aspx

<<<< Ma solution: >>>>> J'ai fini par utiliser cette boucle simple pour find ma dernière ligne usée, mais les methods que @Scott Holtzman affichera plus haut fonctionneront également.

  i = 1 Do If Range("A" & i) = "" Then Exit Do Else i = i + 1 End If Loop Until Range("A" & i) = "" i = 1  i = 1 Do If Range("A" & i) = "" Then Exit Do Else i = i + 1 End If Loop Until Range("A" & i) = "" Si Range ("A" & i) = "" Ensuite  i = 1 Do If Range("A" & i) = "" Then Exit Do Else i = i + 1 End If Loop Until Range("A" & i) = "" Exit Do  i = 1 Do If Range("A" & i) = "" Then Exit Do Else i = i + 1 End If Loop Until Range("A" & i) = "" i = i + 1  i = 1 Do If Range("A" & i) = "" Then Exit Do Else i = i + 1 End If Loop Until Range("A" & i) = ""

Il s'agit d'une boucle de comptage vraiment simple, mais cela correspond bien à mes objectives, étant donné que chaque ligne doit être remplie dans ma feuille de calcul.

Merci pour la discussion …

.UsedRange.Rows.Count et .UsedRange.Columns.Count fonctionnent bien, à condition qu'il y ait quelque chose dans la cellule A1. Sinon, vous devez utiliser la solution SpecialCells.

J'espère que cela est utile.