excel-vba
Metodi per trovare l'ultima riga o colonna usata in un foglio di lavoro
Ricerca…
Osservazioni
Puoi trovare una buona spiegazione sul perché altri metodi sono scoraggiati / inaccurati qui: http://stackoverflow.com/a/11169920/4628637
Trova l'ultima cella non vuota in una colonna
In questo esempio, esamineremo un metodo per restituire l'ultima riga non vuota in una colonna per un set di dati.
Questo metodo funziona indipendentemente dalle regioni vuote all'interno del set di dati.
Tuttavia, è necessario prestare attenzione se sono coinvolte le celle unite , poiché il metodo End
verrà "arrestato" contro una regione unita, restituendo la prima cella della regione unita.
Inoltre, le celle non vuote nelle righe nascoste non saranno prese in considerazione.
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
Per affrontare le limitazioni sopra indicate, la linea:
LastRow = wS.Cells(wS.Rows.Count, "A").End(xlUp).Row
può essere sostituito con:
per l'ultima riga utilizzata di
"Sheet1"
:
LastRow = wS.UsedRange.Row - 1 + wS.UsedRange.Rows.Count
.per l'ultima cella non vuota della colonna
"A"
in"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
Trova l'ultima riga usando l'intervallo con nome
Nel caso in cui tu abbia un intervallo denominato nel tuo foglio e desideri ottenere in modo dinamico l'ultima riga di tale intervallo di nomi dinamici. Copre anche i casi in cui l'intervallo denominato non inizia dalla prima riga.
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
Aggiornare:
Una potenziale scappatoia è stata evidenziata da @Jeeped per un intervallo denominato con righe non contigue poiché genera risultati imprevisti. Per risolvere questo problema, il codice è stato modificato come di seguito.
Asunci: targes sheet = form
, named 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
Prendi la riga dell'ultima cella in un intervallo
'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
Trova l'ultima colonna non vuota nel foglio di lavoro
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
Puoi scegliere tra 2 opzioni, per quanto riguarda se vuoi sapere se non ci sono dati nel foglio di lavoro:
- NO: usa LastCol_1: puoi usarlo direttamente all'interno di
wS.Cells(...,LastCol_1(wS))
- SÌ: utilizzare LastCol_0: è necessario verificare se il risultato ottenuto dalla funzione è 0 o meno prima di utilizzarlo
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
Le proprietà dell'oggetto Err vengono automaticamente ripristinate a zero all'uscita dalla funzione.
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
Ultima cella in Range.CurrentRegion
Range.CurrentRegion
è un'area di intervallo rettangolare circondata da celle vuote. Le celle vuote con formule come =""
o '
non sono considerate vuote (anche con la funzione Excel di ISBLANK
).
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)
Trova l'ultima riga non vuota nel foglio di lavoro
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
Puoi scegliere tra 2 opzioni, per quanto riguarda se vuoi sapere se non ci sono dati nel foglio di lavoro:
- NO: usa LastRow_1: puoi usarlo direttamente all'interno di
wS.Cells(LastRow_1(wS),...)
- SÌ: utilizzare LastRow_0: è necessario verificare se il risultato ottenuto dalla funzione è 0 o meno prima di utilizzarlo
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
Trova l'ultima cella non vuota in una riga
In questo esempio, esamineremo un metodo per restituire l'ultima colonna non vuota in una riga.
Questo metodo funziona indipendentemente dalle regioni vuote all'interno del set di dati.
Tuttavia, è necessario prestare attenzione se sono coinvolte le celle unite , poiché il metodo End
verrà "arrestato" contro una regione unita, restituendo la prima cella della regione unita.
Inoltre, le celle non vuote nelle colonne nascoste non saranno prese in considerazione.
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
Trova l'ultima cella non vuota nel foglio di lavoro - Prestazioni (matrice)
- La prima funzione, utilizzando un array, è molto più veloce
- Se chiamato senza il parametro opzionale, verrà
.ThisWorkbook.ActiveSheet
predefinito in.ThisWorkbook.ActiveSheet
- Se l'intervallo è vuoto, restituirà
Cell( 1, 1 )
come valore predefinito, anzichéNothing
Velocità:
GetMaxCell (Array): Duration: 0.0000790063 seconds
GetMaxCell (Find ): Duration: 0.0002903480 seconds
.Misurato con 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