Obținerea intervalului efectiv utilizat (Programare, Excel, Vba)

thinkanotherone a intrebat.

Am o foaie de lucru Excel care are un buton.

Când apelez funcția usedRange(), intervalul pe care îl returnează include partea cu butonul.

Există vreo modalitate de a obține doar intervalul efectiv utilizat care conține date?

9 răspunsuri
Reafidy

Ce fel de buton, nici un Forms Control și nici un control ActiveX nu ar trebui să afecteze intervalul utilizat.

Este o problemă cunoscută faptul că excel nu ține evidența intervalului utilizat foarte bine. Orice referință la intervalul utilizat prin VBA va reseta valoarea la intervalul utilizat curent. Încercați deci să executați această subprocedură:

Sub ResetUsedRng()
    Application.ActiveSheet.UsedRange 
End Sub 

Dacă nu reușiți, este posibil să aveți o formatare care să vă atârne. Încercați să curățați/ștergeți toate celulele de după ultimul rând.

În ceea ce privește cele de mai sus, consultați și:

Excel Developer Tip

O altă metodă pentru a găsi ultima celulă utilizată:

    Dim rLastCell As Range

    Set rLastCell = ActiveSheet.Cells.Find(What:="*", After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)

Schimbați direcția de căutare pentru a găsi prima celulă utilizată.

Comentarii

  • Voi încerca această metodă. Am sfârșit prin a-mi scrie propria funcție de căutare. Practic, dacă găsesc o celulă goală, număr până la următorul rând 100. Dacă totalul este 0, presupun că este ultimul rând. –  > Por thinkanotherone.
  • Asigurați-vă că aveți ActiveSheet.AutoFilterMode=False înainte de a apela Find, , sau unele rânduri cu conținut real nu vor fi luate în considerare. –  > Por Andy.
  • Nu. Sheet.UsedRange nu o resetează în mod fiabil. Pur și simplu, uneori greșește și rămâne greșit. Acest lucru a părut să se îmbunătățească în legătură cu Excel 2007. –  > Por Tuntable.
  • … dar poate să meargă în continuare greșit, în special dacă există tabele pivot. –  > Por Tuntable.
  • cel puțin în Excel 2016, comanda .Find sare peste celulele fuzionate, atunci când este utilizată pe intervalul .Cells, ceea ce este regretabil, atunci când acestea se află la limita intervalului utilizat. –  > Por j-hap.
JMax

Readify a făcut un răspuns foarte complet. Cu toate acestea, am vrut să adaug End declarație, puteți folosi:

Găsește ultima celulă folosită, înainte de un spațiu gol într-o coloană:

Sub LastCellBeforeBlankInColumn()
Range("A1").End(xldown).Select
End Sub

Găsește ultima celulă folosită într-o coloană:

Sub LastCellInColumn()
Range("A" & Rows.Count).End(xlup).Select
End Sub

Găsește ultima celulă, înainte de un spațiu gol într-un rând:

Sub LastCellBeforeBlankInRow()
Range("A1").End(xlToRight).Select
End Sub

Găsește ultima celulă folosită într-un rând:

Sub LastCellInRow()
Range("IV1").End(xlToLeft).Select
End Sub

A se vedea aici pentru mai multe informații (și pentru a explica de ce xlCellTypeLastCell nu este foarte fiabilă).

Comentarii

  • +1 Informații excelente. V-aș sugera să folosiți Range(„A” & Rows.Count).End(xlup) în acest fel codul dvs. este realiable pentru mai multe versiuni de excel. –  > Por Reafidy.
davidlandy

Iată o pereche de funcții pentru a returna ultimul rând și ultimul col al unei foi de calcul, bazate pe soluția lui Reafidy de mai sus.

    Function LastRow(ws As Object) As Long

        Dim rLastCell As Object
        On Error GoTo ErrHan
        Set rLastCell = ws.Cells.Find("*", ws.Cells(1, 1), , , xlByRows, _
                                      xlPrevious)
        LastRow = rLastCell.Row

    ErrExit:
        Exit Function

    ErrHan:
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbExclamation, "LastRow()"
        Resume ErrExit

    End Function

    Function LastCol(ws As Object) As Long

        Dim rLastCell As Object
        On Error GoTo ErrHan
        Set rLastCell = ws.Cells.Find("*", ws.Cells(1, 1), , , xlByColumns, _
                                      xlPrevious)
        LastCol = rLastCell.Column

    ErrExit:
        Exit Function

    ErrHan:
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbExclamation, "LastRow()"
        Resume ErrExit

    End Function

Comentarii

  • Foarte frumos. Eu am folosit o căutare binară, dar aceasta este mai simplă și ar trebui să fie mult mai rapidă. Cu toate acestea, probabil că doriți să specificați și Lookin:=xlFormulas; O formulă ar putea returna „” –  > Por Tuntable.
  • De fapt, nu, „” se potrivește cu „*” și este diferit de isempty(). –  > Por Tuntable.
David F Mayer
Public Sub FindTrueUsedRange(RowLast As Long, ColLast As Long)
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    RowLast = 0
    ColLast = 0
    ActiveSheet.UsedRange.Select
    Cells(1, 1).Activate
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    On Error GoTo -1: On Error GoTo Quit
    Cells.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Activate
    On Error GoTo -1: On Error GoTo 0
    RowLast = Selection.Row
    Cells(1, 1).Activate
    Selection.End(xlToRight).Select
    Selection.End(xlToRight).Select
    Cells.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Activate
    ColLast = Selection.Column
Quit:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    On Error GoTo -1: On Error GoTo 0
End Sub

Bill Hudson

Această funcție returnează intervalul efectiv utilizat până la limita inferioară dreaptă. Ea returnează „Nimic” dacă foaia este goală.

'2020-01-26
Function fUsedRange() As Range
Dim lngLastRow As Long
Dim lngLastCol As Long
Dim rngLastCell As Range
    On Error Resume Next
    Set rngLastCell = ActiveSheet.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious)
    If rngLastCell Is Nothing Then  'look for data backwards in rows
        Set fUsedRange = Nothing
        Exit Function
    Else
        lngLastRow = rngLastCell.Row
    End If
    Set rngLastCell = ActiveSheet.Cells.Find("*", searchorder:=xlByColumns, searchdirection:=xlPrevious)
    If rngLastCell Is Nothing Then  'look for data backwards in columns
        Set fUsedRange = Nothing
        Exit Function
    Else
        lngLastCol = rngLastCell.Column
    End If
    Set fUsedRange = ActiveSheet.Range(Cells(1, 1), Cells(lngLastRow, lngLastCol))  'set up range
End Function

swhgraham

Folosesc următorul cod vba pentru a determina întregul interval de rânduri utilizate pentru foaia de calcul pentru a scurta apoi intervalul selectat al unei coloane:

    Set rUsedRowRange = Selection.Worksheet.UsedRange.Columns( _
    Selection.Column - Selection.Worksheet.UsedRange.Column + 1)

De asemenea, funcționează și în sens invers:

    Set rUsedColumnRange = Selection.Worksheet.UsedRange.Rows( _
    Selection.Row - Selection.Worksheet.UsedRange.Row + 1)

Aditya Gupta

Această funcție oferă toate cele 4 limite ale intervalului utilizat:

Function FindUsedRangeLimits()
    Set Sheet = ActiveSheet
    Sheet.UsedRange.Select

    ' Display the range's rows and columns.
    row_min = Sheet.UsedRange.Row
    row_max = row_min + Sheet.UsedRange.Rows.Count - 1
    col_min = Sheet.UsedRange.Column
    col_max = col_min + Sheet.UsedRange.Columns.Count - 1

    MsgBox "Rows " & row_min & " - " & row_max & vbCrLf & _
           "Columns: " & col_min & " - " & col_max
    LastCellBeforeBlankInColumn = True
End Function

Comentarii

  • Definiția UsedRange include și celulele formatate, chiar și atunci când acestea sunt goale. De cele mai multe ori am nevoie ca UsedRange să fie definit ca celule care nu sunt goale. –  > Por j-hap.
Tuntable

Timpi pe Excel 2013 mașină destul de lentă, cu un interval folosit mare și rău de milioane de rânduri:

26ms Cells.Find xlPrevious method (ca mai sus)

0.4ms Sheet.UsedRange (doar o cheamă)

0.14ms Căutare binară Counta + 0.4ms Used Range pentru a începe căutarea (12 apeluri CountA)

Așadar, Find xlPrevious este destul de lent, dacă acest lucru vă interesează.

Abordarea căutării binare CountA constă în a efectua mai întâi o căutare a intervalului utilizat. Apoi se taie intervalul în două și se verifică dacă există celule nevide în jumătatea inferioară, apoi se înjumătățește din nou, după caz. Este dificil de realizat corect.

j-hap

Iată încă una. Caută prima și ultima celulă care nu este goală și construiește un interval din acestea. Aceasta se ocupă, de asemenea, de cazurile în care datele dvs. nu sunt dreptunghiulare și nu încep în A1. În plus, se ocupă și de celulele fuzionate, pe care .Find le ignoră atunci când este executat dintr-o macro, utilizată pe .Cells într-o foaie de calcul.

Function getUsedRange(ByRef sheet As Worksheet) As Range
' finds used range by looking for non empty cells
' works around bug in .Find that skips merged cells
' by starting at with the UsedRange (that may be too big)

' credit to https://contexturesblog.com/archives/2012/03/01/select-actual-used-range-in-excel-sheet/
' for the .Find commands

Dim excelsUsedRange As Range
Dim lastRow As Long
Dim lastCol As Long
Dim lastCell As Range
Dim firstRow As Long
Dim firstCol As Long
Dim firstCell As Range

Set excelsUsedRange = ActiveSheet.UsedRange

lastRow = excelsUsedRange.Find(What:="*", _
                               LookIn:=xlValues, SearchOrder:=xlRows, _
                               SearchDirection:=xlPrevious).Row
lastCol = excelsUsedRange.Find(What:="*", _
                               LookIn:=xlValues, SearchOrder:=xlByColumns, _
                               SearchDirection:=xlPrevious).Column
Set lastCell = sheet.Cells(lastRow, lastCol)

firstRow = excelsUsedRange.Find(What:="*", After:=lastCell, _
                                LookIn:=xlValues, SearchOrder:=xlRows, _
                                SearchDirection:=xlNext).Row
firstCol = excelsUsedRange.Find(What:="*", After:=lastCell, _
                                LookIn:=xlValues, SearchOrder:=xlByColumns, _
                                SearchDirection:=xlNext).Row
Set firstCell = sheet.Cells(firstRow, firstCol)
Set getUsedRange = sheet.Range(firstCell, lastCell)
End Function

Tags:,