excel-vba
Méthodes de recherche de la dernière ligne ou colonne utilisée dans une feuille de calcul
Recherche…
Remarques
Vous pouvez trouver une bonne explication sur pourquoi les autres méthodes sont découragées / inexactes ici: http://stackoverflow.com/a/11169920/4628637
Trouver la dernière cellule non vide dans une colonne
Dans cet exemple, nous examinerons une méthode de retour de la dernière ligne non vide dans une colonne pour un ensemble de données.
Cette méthode fonctionnera indépendamment des régions vides dans l'ensemble de données.
Toutefois, il faut être prudent si des cellules fusionnées sont impliquées , car la méthode End sera "arrêtée" contre une région fusionnée, en renvoyant la première cellule de la région fusionnée.
De plus, les cellules non vides dans les lignes masquées ne seront pas prises en compte.
Sub FindingLastRow()
Dim wS As Worksheet, LastRow As Long
Set wS = ThisWorkbook.Worksheets("Sheet1")
'Here we look in Column A
LastRow = wS.Cells(wS.Rows.Count, "A").End(xlUp).Row
Debug.Print LastRow
End Sub
Pour répondre aux limitations indiquées ci-dessus, la ligne:
LastRow = wS.Cells(wS.Rows.Count, "A").End(xlUp).Row
peut être remplacé par:
pour la dernière ligne utilisée de
"Sheet1":
LastRow = wS.UsedRange.Row - 1 + wS.UsedRange.Rows.Count.pour la dernière cellule non vide de la colonne
"A"dans"Sheet1":Dim i As Long For i = LastRow To 1 Step -1 If Not (IsEmpty(Cells(i, 1))) Then Exit For Next i LastRow = i
Rechercher la dernière ligne à l'aide de la plage nommée
Si vous avez une plage nommée dans votre feuille et que vous souhaitez obtenir de manière dynamique la dernière ligne de cette plage nommée dynamique. Couvre également les cas où la plage nommée ne commence pas à partir de la première ligne.
Sub FindingLastRow()
Dim sht As Worksheet
Dim LastRow As Long
Dim FirstRow As Long
Set sht = ThisWorkbook.Worksheets("form")
'Using Named Range "MyNameRange"
FirstRow = sht.Range("MyNameRange").Row
' in case "MyNameRange" doesn't start at Row 1
LastRow = sht.Range("MyNameRange").Rows.count + FirstRow - 1
End Sub
Mettre à jour:
Une lacune potentielle a été signalée par @Jeeped pour une plage nommée avec des lignes non contiguës car elle génère un résultat inattendu. Pour résoudre ce problème, le code est révisé comme ci-dessous.
Asumptions: targes sheet = form , nommée range = MyNameRange
Sub FindingLastRow()
Dim rw As Range, rwMax As Long
For Each rw In Sheets("form").Range("MyNameRange").Rows
If rw.Row > rwMax Then rwMax = rw.Row
Next
MsgBox "Last row of 'MyNameRange' under Sheets 'form': " & rwMax
End Sub
Récupère la ligne de la dernière cellule dans une plage
'if only one area (not multiple areas):
With Range("A3:D20")
Debug.Print .Cells(.Cells.CountLarge).Row
Debug.Print .Item(.Cells.CountLarge).Row 'using .item is also possible
End With 'Debug prints: 20
'with multiple areas (also works if only one area):
Dim rngArea As Range, LastRow As Long
With Range("A3:D20, E5:I50, H20:R35")
For Each rngArea In .Areas
If rngArea(rngArea.Cells.CountLarge).Row > LastRow Then
LastRow = rngArea(rngArea.Cells.CountLarge).Row
End If
Next
Debug.Print LastRow 'Debug prints: 50
End With
Trouver la dernière colonne non vide dans la feuille de calcul
Private Sub Get_Last_Used_Row_Index()
Dim wS As Worksheet
Set wS = ThisWorkbook.Sheets("Sheet1")
Debug.Print LastCol_1(wS)
Debug.Print LastCol_0(wS)
End Sub
Vous pouvez choisir entre 2 options, si vous voulez savoir s'il n'y a pas de données dans la feuille de travail:
- NO: Utilisez LastCol_1: vous pouvez l'utiliser directement dans
wS.Cells(...,LastCol_1(wS)) - OUI: Utiliser LastCol_0: Vous devez tester si le résultat obtenu de la fonction est 0 ou non avant de l'utiliser
Public Function LastCol_1(wS As Worksheet) As Double
With wS
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
LastCol_1 = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
Else
LastCol_1 = 1
End If
End With
End Function
Les propriétés de l'objet Err sont automatiquement réinitialisées à la sortie de la fonction.
Public Function LastCol_0(wS As Worksheet) As Double
On Error Resume Next
LastCol_0 = wS.Cells.Find(What:="*", _
After:=ws.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
End Function
Dernière cellule dans Range.CurrentRegion
Range.CurrentRegion est une zone de plage rectangulaire entourée de cellules vides. Les cellules vides avec des formules telles que ="" ou ' ne sont pas considérées comme vides (même par la fonction ISBLANK Excel).
Dim rng As Range, lastCell As Range
Set rng = Range("C3").CurrentRegion ' or Set rng = Sheet1.UsedRange.CurrentRegion
Set lastCell = rng(rng.Rows.Count, rng.Columns.Count)
Trouver la dernière ligne non vide dans la feuille de calcul
Private Sub Get_Last_Used_Row_Index()
Dim wS As Worksheet
Set wS = ThisWorkbook.Sheets("Sheet1")
Debug.Print LastRow_1(wS)
Debug.Print LastRow_0(wS)
End Sub
Vous pouvez choisir entre 2 options, si vous voulez savoir s'il n'y a pas de données dans la feuille de travail:
- NO: Utilisez LastRow_1: vous pouvez l'utiliser directement dans
wS.Cells(LastRow_1(wS),...) - OUI: Utilisez LastRow_0: vous devez tester si le résultat obtenu de la fonction est 0 ou non avant de l'utiliser
Public Function LastRow_1(wS As Worksheet) As Double
With wS
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
LastRow_1 = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Else
LastRow_1 = 1
End If
End With
End Function
Public Function LastRow_0(wS As Worksheet) As Double
On Error Resume Next
LastRow_0 = wS.Cells.Find(What:="*", _
After:=ws.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
End Function
Trouver la dernière cellule non vide dans une ligne
Dans cet exemple, nous allons examiner une méthode pour retourner la dernière colonne non vide dans une ligne.
Cette méthode fonctionnera indépendamment des régions vides dans l'ensemble de données.
Toutefois, il faut être prudent si des cellules fusionnées sont impliquées , car la méthode End sera "arrêtée" contre une région fusionnée, en renvoyant la première cellule de la région fusionnée.
De plus, les cellules non vides dans les colonnes masquées ne seront pas prises en compte.
Sub FindingLastCol()
Dim wS As Worksheet, LastCol As Long
Set wS = ThisWorkbook.Worksheets("Sheet1")
'Here we look in Row 1
LastCol = wS.Cells(1, wS.Columns.Count).End(xlToLeft).Column
Debug.Print LastCol
End Sub
Trouver la dernière cellule non vide dans la feuille de calcul - Performances (tableau)
- La première fonction, utilisant un tableau, est beaucoup plus rapide
- Si elle est appelée sans le paramètre facultatif, elle passera par défaut à
.ThisWorkbook.ActiveSheet - Si la plage est vide, la
Cell( 1, 1 )remplacée par défaut, au lieu deNothing
La vitesse:
GetMaxCell (Array): Duration: 0.0000790063 seconds
GetMaxCell (Find ): Duration: 0.0002903480 seconds.Mesuré avec MicroTimer
Public Function GetLastCell(Optional ByVal ws As Worksheet = Nothing) As Range
Dim uRng As Range, uArr As Variant, r As Long, c As Long
Dim ubR As Long, ubC As Long, lRow As Long
If ws Is Nothing Then Set ws = Application.ThisWorkbook.ActiveSheet
Set uRng = ws.UsedRange
uArr = uRng
If IsEmpty(uArr) Then
Set GetLastCell = ws.Cells(1, 1): Exit Function
End If
If Not IsArray(uArr) Then
Set GetLastCell = ws.Cells(uRng.Row, uRng.Column): Exit Function
End If
ubR = UBound(uArr, 1): ubC = UBound(uArr, 2)
For r = ubR To 1 Step -1 '----------------------------------------------- last row
For c = ubC To 1 Step -1
If Not IsError(uArr(r, c)) Then
If Len(Trim$(uArr(r, c))) > 0 Then
lRow = r: Exit For
End If
End If
Next
If lRow > 0 Then Exit For
Next
If lRow = 0 Then lRow = ubR
For c = ubC To 1 Step -1 '----------------------------------------------- last col
For r = lRow To 1 Step -1
If Not IsError(uArr(r, c)) Then
If Len(Trim$(uArr(r, c))) > 0 Then
Set GetLastCell = ws.Cells(lRow + uRng.Row - 1, c + uRng.Column - 1)
Exit Function
End If
End If
Next
Next
End Function
'Returns last cell (max row & max col) using Find
Public Function GetMaxCell2(Optional ByRef rng As Range = Nothing) As Range 'Using Find
Const NONEMPTY As String = "*"
Dim lRow As Range, lCol As Range
If rng Is Nothing Then Set rng = Application.ThisWorkbook.ActiveSheet.UsedRange
If WorksheetFunction.CountA(rng) = 0 Then
Set GetMaxCell2 = rng.Parent.Cells(1, 1)
Else
With rng
Set lRow = .Cells.Find(What:=NONEMPTY, LookIn:=xlFormulas, _
After:=.Cells(1, 1), _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows)
If Not lRow Is Nothing Then
Set lCol = .Cells.Find(What:=NONEMPTY, LookIn:=xlFormulas, _
After:=.Cells(1, 1), _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns)
Set GetMaxCell2 = .Parent.Cells(lRow.Row, lCol.Column)
End If
End With
End If
End Function
.
Private Declare PtrSafe Function getFrequency Lib "Kernel32" Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare PtrSafe Function getTickCount Lib "Kernel32" Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long
Function MicroTimer() As Double
Dim cyTicks1 As Currency
Static cyFrequency As Currency
MicroTimer = 0
If cyFrequency = 0 Then getFrequency cyFrequency 'Get frequency
getTickCount cyTicks1 'Get ticks
If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency 'Returns Seconds
End Function