excel-vba => Metoder för att hitta den senast använda raden eller kolumnen i ett kalkylblad

Anmärkningar

Du kan hitta en bra förklaring till varför andra metoder är avskräckta / felaktiga här: http://stackoverflow.com/a/11169920/4628637

Hitta den sista icke-tomma cellen i en kolumn

I det här exemplet kommer vi att titta på en metod för att returnera den sista icke-tomma raden i en kolumn för en datamängd.

Den här metoden fungerar oavsett tomma regioner i datauppsättningen.

Emellertid bör försiktighet användas om sammanslagna celler är involverade , eftersom End metoden "stoppas" mot en sammanslagd region, vilket returnerar den första cellen i det sammanslagna området.

Dessutom kommer icke-tomma celler i dolda rader inte att beaktas.

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

För att hantera de begränsningar som anges ovan, raden:
LastRow = wS.Cells(wS.Rows.Count, "A").End(xlUp).Row

kan ersättas med:

för senast använda raden "Sheet1" :
LastRow = wS.UsedRange.Row - 1 + wS.UsedRange.Rows.Count .

för den sista icke-tomma cellen i kolumn "A" i "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

Hitta sista rad med namngivna intervall

Om du har ett namngivet intervall i arket, och du vill dynamiskt få den sista raden i det dynamiska namngivna intervallet. Täcker också fall där det namngivna intervallet inte startar från den första raden.

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

Uppdatering:
Ett potentiellt kryphål påpekades av @Jeeped för ett namngivet intervall med icke sammanhängande rader eftersom det genererar oväntat resultat. För att hantera det problemet revideras koden enligt nedan.
Antaganden: targes ark = form , namngivet intervall = 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

Få raden med den sista cellen inom ett intervall

'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

Hitta den sista icke-tomma kolumnen i kalkylbladet

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

Du kan välja mellan två alternativ, om du vill veta om det inte finns data i kalkylbladet:

NEJ: Använd LastCol_1: Du kan använda den direkt inom wS.Cells(...,LastCol_1(wS))
JA: Använd LastCol_0: Du måste testa om resultatet du får från funktionen är 0 eller inte innan du använder det

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

Err-objektets egenskaper återställs automatiskt till noll vid avslutande av funktionen.

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

Sista cell inom Range.CurrentRegion

Range.CurrentRegion är ett rektangulärt intervallområde omgiven av tomma celler. Tomma celler med formler som ="" eller ' anses inte tomma (inte ens av ISBLANK Excel-funktionen).

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)

Hitta den sista icke-tomma raden i kalkylbladet

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

Du kan välja mellan två alternativ, om du vill veta om det inte finns data i kalkylbladet:

NEJ: Använd LastRow_1: Du kan använda den direkt inom wS.Cells(LastRow_1(wS),...)
JA: Använd LastRow_0: Du måste testa om resultatet du får från funktionen är 0 eller inte innan du använder det

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

Hitta den sista icke-tomma cellen på rad

I det här exemplet kommer vi att titta på en metod för att returnera den sista icke-tomma kolumnen i rad.

Den här metoden fungerar oavsett tomma regioner i datauppsättningen.

Dessutom kommer icke-tomma celler i dolda kolumner inte att beaktas.

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

Hitta den sista icke-tomma cellen i kalkylbladet - Performance (Array)

Den första funktionen, med hjälp av en matris, är mycket snabbare
Om den anropas utan den valfria parametern kommer den att .ThisWorkbook.ActiveSheet
Om intervallet är tomt returnerar Cell( 1, 1 ) som standard istället för Nothing

Hastighet:

GetMaxCell (Array): Duration: 0.0000790063 seconds
GetMaxCell (Find ): Duration: 0.0002903480 seconds

. Mätad med 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

MicroTimer :

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

Modified text is an extract of the original Stack Overflow Documentation

Licensierat under CC BY-SA 3.0

Inte anslutet till Stack Overflow

excel-vba
Metoder för att hitta den senast använda raden eller kolumnen i ett kalkylblad

Sök…