Besoin de déterminer LastRow sur la ligne entière

Je ne suis pas un programmeur, mais j'ai réussi à regrouper de superbes quantités de code qui fonctionnent sur 4 projets assez volumineux (Yay pour moi!) J'ai essayé de nombreuses façons de find la Dernière ligne. Certains fonctionnent pour moi, certains ne le font pas. Je peux find quelques-uns qui me donnent la dernière ligne "réelle" indépendamment des ébauches dans la colonne A (c'est ce dont j'ai besoin). Pourtant, je ne peux pas, pour ma vie, comprendre comment intégrer ce code avec la façon dont je passe les valeurs de mon tableau d'un classur à l'autre. Tout le code fonctionne "As Is", mais je dois find une meilleure façon de searchr toute la ligne (actuellement les colonnes A: O) pour la dernière ligne, puis de copyr datatables. La colonne A peut-être vide à certains moments et pour éviter que le code ne soit écrasé, que "Last Row" doit vérifier toute la ligne. Je force actuellement une cellule cachée (A7) avec un "." comme un espace réservé forcé. Tout conseil serait génial.

Option Explicit Public Sub SaveToLog15() Dim rng As Range, aCell As Range Dim MyAr() As Variant Dim n As Long, i As Long Dim LastRow As Long Dim NextCell As Range Dim Sheet2 As Worksheet Set Sheet2 = ActiveSheet Application.ScreenUpdating = False With Sheet2 ' rng are the cells you want to read into the array. ' Cell A7 (".") is a needed "Forced Place Holder" for last row _ determination ' A7 will go away once "better" LastRow can be added to this code Set rng = Worksheets("Main").Range("A7,D22,D19,D20,J22:J24,E23,D21,J25:J27,D62,D63,G51") ' counts number of cells in MyAr n = rng.Cells.Count ' Redimensions array for above range ReDim MyAr(1 To n) ' Sets start cell at 1 or "A" n = 1 ' Loops through cells to add data to the array For Each aCell In rng.Cells MyAr(n) = aCell.Value n = n + 1 Next aCell End With On Error Resume Next ' Opens "Test Log.xls" Workbooks.Open FileName:= _ "S:\Test Folder\Test Log.xls" ' SUBROUTINE 1 "Disable Sheet Protection and Show All" REMOVED ' Finds last row on Tab "Tracking" based on Column "A" ' Last row determination DOES NOT go to next row if first _ Column is blank ' Use A7 "." to always force Data to Col A '********************************************************************** 'THIS WORKS FINE BUT DOES NOT RECOGNIZE THE POSSIBLE BLANK IN COL A. With Worksheets("Incoming Data") Set NextCell = Worksheets("Incoming Data").Cells _ (Worksheets("Incoming Data").Rows.Count, "A").End(xlUp).Offset(1, 0) End With ' I need this code replaced by the following code or integrated into ' this code snippet. I am lost on how to make that happen. '*********************************************************************** '*********************************************************************** 'THIS CODE FINDS THE "ACTUAL" LAST ROW AND THIS IS WHAT I'D LIKE TO USE ' I need to figure how to integrate this code block with the above ' Or maybe redo the whole thing. LastRow = Cells.Find(What:="*", After:=[A1], _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row MsgBox ("The Last Row Is: " & LastRow) ' I am not using this code in the program. It's just there to show ' what I need to use because it works. I need to make this code work 'WITH the above block. '*********************************************************************** ' Sets the size of the new array and copys MyAr to it NextCell.Resize(1, UBound(MyAr)).Value = (MyAr) ' SUBROUTINE 2 "Add borders to cells in range" REMOVED ' SUBROUTINE 3 "Re-enable Sheet Protection" REMOVED ActiveWorkbook.Save 'ActiveWindow.Close Application.ScreenUpdating = True MsgBox "Your Data has been saved to the Log File: " & vbCrLf & vbCrLf _ & "'Test Log.xls'", vbInformation, "Log Save Confirmation" End Sub 

• Comment comparer et supprimer les ID non utilisés dans excel
• Alternative HTML5 + JS en rlocation de l'application Silverlight
• Enregistrer le text en tant que numéro dans Excel
• Comment find si la gamme contient des cellules fusionnées dans VBA
• Pourquoi suis-je en train d'être exceptionnel "IOException: la taille de l'input ZIP est trop grande" lorsque vous essayez d'ouvrir un file Excel à l'aide de POI d'Apache?

 Sub RealLast() Dim m As Long m = 0 For i = 1 To 4 candidate = Cells(Rows.Count, i).End(xlUp).Row If candidate > m Then m = candidate Next i MsgBox m End Sub 

 Dim lLastRow As Long lLastRow = LastUsedRow(shName) Private Function LastUsedRow(sh As Worksheet) As Long LastUsedRow = sh.Cells.Find(What:="*", After:=sh.Cells.Cells(1), _ LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, MatchCase:=False).Row End Function 

 LastRow = Cells.Find(What:="*", After:=[A1], _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row MsgBox ("The Last Row Is: " & LastRow) 

 LastRow = cells.specialcells(xllastcell).row MsgBox ("The Last Row Is: " & LastRow) 

  ' Used to determine LastRow, LastColumn, LastCell, NextCell Dim LastRow As Long Dim LastColumn As Integer Dim LastCell As Range, NextCell As Range With Worksheets("Tracking") ' Find LastRow. Works Best. 1st and last cells can be empty If WorksheetFunction.CountA(Cells) > 0 Then 'Search for any entry, by searching backwards by Rows. LastRow = Cells.Find(What:="*", After:=[A1], _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row 'Search for any entry, by searching backwards by Columns. LastColumn = Cells.Find(What:="*", After:=[A1], _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column 'MsgBox "Last Cell" & vbCrLf & vbCrLf & Cells(LastRow, LastColumn).Address 'MsgBox "The Last Row is: " & vbCrLf & vbCrLf & LastRow 'MsgBox "The Last Column is: " & vbCrLf & vbCrLf & LastColumn End If ' Number of columns based on actual size of log range NOT MyAr(n) Set NextCell = Worksheets("Tracking").Cells(LastRow + 1, (LastColumn - 10)) End With