Получение фактического используемого диапазона

Question

У меня есть лист Excel, в котором есть кнопка.

Когда я вызываю функцию usedRange(), диапазон, который она возвращает, включает в себя часть кнопки.

В любом случае я могу просто получить фактический использованный диапазон, который содержит данные?

Answer 1

Какие кнопки, ни элемент управления Forms, ни элемент управления 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)

Измените направление поиска, чтобы найти первую использованную ячейку.

Answer 2

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 не очень надежный).

Answer 3

Вот пара функций, которые возвращают последнюю строку и столбец таблицы на основе решения 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

Answer 4

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

Answer 5

Эта функция возвращает фактический используемый диапазон до нижнего правого предела. Он возвращает "Ничего", если лист пуст.

'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

Answer 6

Я использую следующий код 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)

Answer 7

Это другой подход к другим ответам, который даст вам все регионы с данными -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объект, содержащий 1 или болееAreas, каждый из которых будет представлять область на листе, содержащую данные/формулу.

Это тот же метод, когда вы щелкаете все ячейки на листе и нажимаете Ctrl+T, объединяя все эти области. Я использую его для поиска потенциальных таблиц данных

Answer 8

Эта функция дает все 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

Answer 9

Тайминги в Excel 2013 довольно медленная машина с большим плохим используемым диапазоном миллионов строк:

26 мс Cells.Find метод xlPrevious (как указано выше)

0,4 мс Sheet.UsedRange (просто назовите это)

0,14 мс Бинарный поиск Counta + 0,4 мс Используемый диапазон для начала поиска (12 вызовов CountA)

Таким образом, Find xlPrevious работает довольно медленно, если это вызывает беспокойство.

Подход бинарного поиска CountA заключается в том, чтобы сначала определить используемый диапазон. Затем разрежьте диапазон пополам и посмотрите, есть ли в нижней половине непустые ячейки, а затем снова уменьшите вдвое по мере необходимости. Это сложно сделать правильно.

Answer 10

Вот еще один. Он ищет первую и последнюю непустую ячейку, и сборки варьируются от них. Это также обрабатывает случаи, когда ваши данные не прямоугольные и не начинаются с A1. Кроме того, он также обрабатывает объединенные ячейки, которые .Find пропускает при выполнении из макроса, используемого в .Cells на листе.

      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