실제 사용된 범위 가져오기

실제 사용된 범위 가져오기

나는 버튼이 있는 엑셀 워크시트를 가지고 있습니다.

usedRange() 함수를 호출하면 반환되는 범위에 버튼 부분이 포함됩니다.

데이터가 포함된 실제 사용 범위를 얻을 수 있는 방법이 있습니까?

양식 컨트롤이나 ActiveX 컨트롤 모두 사용 범위에 영향을 주지 않아야 하는 버튼의 종류입니다.

excel이 사용된 범위를 잘 추적하지 못하는 것은 알려진 문제입니다.VBA를 통해 사용된 범위를 참조하면 값이 현재 사용된 범위로 재설정됩니다.따라서 다음 하위 절차를 실행해 보십시오.

Sub ResetUsedRng()
    Application.ActiveSheet.UsedRange 
End Sub 

당신에게 서식이 남아있을지도 모른다는 것에 실패하는 것.마지막 행 뒤에 있는 모든 셀을 지우거나 삭제합니다.

위와 관련하여 다음을 참조하십시오.

Excel 개발자 팁

마지막으로 사용한 셀을 찾는 다른 방법:

    Dim rLastCell As Range

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

검색 방향을 변경하여 처음 사용한 셀을 찾습니다.

Readify는 매우 완벽한 대답을 했습니다.하지만, 나는 추가하고 싶었습니다.End문, 다음을 사용할 수 있습니다.

열에서 빈 칸 앞에 마지막으로 사용한 셀 찾기:

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

열에서 마지막으로 사용한 셀 찾기:

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

행의 공백 앞에 있는 마지막 셀 찾기:

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

행에서 마지막으로 사용한 셀 찾기:

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

자세한 내용은 여기를 참조하십시오(및 이유 설명).xlCellTypeLastCell신뢰할 수 없음).

위의 Reafidy 솔루션을 기반으로 워크시트의 마지막 행과 색상을 반환하는 함수 쌍이 있습니다.

    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

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

이 함수는 실제 사용된 범위를 오른쪽 하단 한계로 되돌립니다.시트가 비어 있으면 "없음"을 반환합니다.

'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

다음 vba 코드를 사용하여 워크시트에 사용된 전체 행 범위를 확인한 다음 열의 선택된 범위를 줄입니다.

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

반대로 작동할 수도 마찬가지로,

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

이 기능은 사용 범위의 4가지 제한을 모두 제공합니다.

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

사용된 범위가 크고 불량한 수백만 행의 Excel 2013 상당히 느린 기계의 타이밍:

26ms 셀.xlPrevious 메서드 찾기(위와 같이)

0.4ms 시트.사용된 범위(그냥 호출)

0.14ms 이진 검색 카운트 + 0.4ms 검색 시작에 사용된 범위(12개의 CountA 호출)

따라서 Find xlPrevious는 문제가 될 경우 매우 느립니다.

CountA 이진 검색 접근법은 먼저 사용된 범위를 수행하는 것입니다.그런 다음 범위를 반으로 잘라 아래 반에 비어 있지 않은 셀이 있는지 확인하고 필요에 따라 다시 반으로 나눕니다.정답을 맞추는 것은 어렵습니다.

여기 또 하나 있어.빈 셀이 아닌 첫 번째 셀과 마지막 셀을 찾고 빌드 범위는 이들 셀과 동일합니다.또한 데이터가 직사각형이 아니며 A1에서 시작하지 않는 경우도 처리합니다.또한 병합된 셀도 처리합니다.에서 사용되는 매크로에서 실행할 때 건너뛰기를 찾습니다.워크시트의 셀입니다.

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

이는 데이터가 있는 모든 지역을 제공하는 다른 답변에 대한 다른 접근 방식입니다.Region는 빈 행과 열 및 워크시트의 가장자리로 둘러싸인 것입니다.기본적으로 모든 데이터 직사각형을 제공합니다.

Public Function ContentRange(ByVal ws As Worksheet) As Range

    'First, identify any cells with data, whose neighbourhood we will inspect
    ' to identify contiguous regions of content
    'For efficiency, restrict our search to only the UsedRange
    ' NB. This may be pointless if .SpecialCells does this internally already, it probably does...
    With ws.UsedRange 'includes data and cells that have been formatted
        Dim cellsWithContent As Range
        On Error Resume Next '.specialCells will error if nothing found, we can ignore it though
        Set cellsWithContent = .SpecialCells(xlCellTypeConstants)
        Set cellsWithContent = Union(cellsWithContent, .SpecialCells(xlCellTypeFormulas))
        On Error GoTo 0
    End With
    'Early exit; return Nothing if there is no Data
    If cellsWithContent Is Nothing Then Exit Function
    
    'Next, loop over all the content cells and group their currentRegions
    ' This allows us to include some blank cells which are interspersed amongst the data
    ' It is faster to loop over areas rather than cell by cell since we merge all the CurrentRegions either way
   
    Dim item As Range
    Dim usedRegions As Range
    For Each item In cellsWithContent.Areas
        'Debug.Print "adding: "; item.Address, item.CurrentRegion.Address
        If usedRegions Is Nothing Then
            Set usedRegions = item.CurrentRegion 'expands "item" to include any surrounding non-blank data
        Else
            Set usedRegions = Union(usedRegions, item.CurrentRegion)
        End If
    Next item
    'Debug.Print cellsWithContent.Address; "->"; usedRegions.Address
    Set ContentRange = usedRegions
End Function

다음과 같이 사용:

Debug.Print ContentRange(Sheet1).Address '$A$1:$F$22
Debug.Print ContentRange(Sheet2).Address '$A$1:$F$22,$N$5:$M$7

는 그결입니다.Range Areas시트에 데이터/데이터베이스가 포함된 영역을 나타내는 각 영역.

시트의 모든 셀을 클릭하고 Ctrl+T를 눌러 모든 영역을 병합하는 것과 동일한 기술입니다.잠재적인 데이터 테이블을 찾는 데 사용하고 있습니다.

언급URL : https://stackoverflow.com/questions/7423022/getting-the-actual-usedrange