Ошибка при поиске последней использованной ячейки в VBA

Что касается правильного способа поиска последней использованной ячейки, сначала нужно решить, что считается используемой, а затем выбрать подходящий метод. Я представляю себе как минимум три значения:

1. Используется = не пустое, т.е. Имеющее данные.

2. Used = "... in use, что означает раздел, содержащий данные или форматирование ". Согласно официальной документации, это критерий, используемый Excel во время сохранения. См. Также это. Если кто-то не знает об этом, критерий может привести к неожиданным результатам, но он также может быть намеренно использован (реже, конечно), например, для выделения или печати определенных регионов, которые в конечном итоге не могут иметь никаких данных. И, конечно же, желательно, чтобы критерий диапазона использовался при сохранении книги, чтобы не потерять часть одной работы.

3. Used = "... используется, то есть раздел, который содержит данные или форматирование " или условное форматирование. То же, что и 2., но также включая ячейки, которые являются объектом любого правила условного форматирования.

Как найти последнюю использованную ячейку зависит от того, что вы хотите (ваш критерий).

Для критерия 1 я предлагаю прочитать этот ответ. Обратите внимание, что UsedRange считается ненадежным. Я думаю, что это вводит в заблуждение (т. UsedRange "Несправедливо" для UsedRange), поскольку UsedRange просто не предназначен для сообщения последней ячейки, содержащей данные. Поэтому он не должен использоваться в этом случае, как указано в этом ответе. См. Также этот комментарий.

Для критерия 2, UsedRange является наиболее надежным вариантом, по сравнению с другими вариантами, также разработанными для этого использования. Это даже делает ненужным сохранять книгу, чтобы убедиться, что последняя ячейка обновлена. Ctrl + End перейдет в неправильную ячейку до сохранения ("Последняя ячейка не сбрасывается, пока вы не сохраните рабочий лист", с http://msdn.microsoft.com/en-us/library/aa139976% 28v = office.10% 29.aspx. Это старая ссылка, но в этом отношении действительна).

Для критерия 3 я не знаю никакого встроенного метода. Критерий 2 не учитывает Условное форматирование. Можно иметь форматированные ячейки на основе формул, которые не обнаруживаются с помощью UsedRange или Ctrl + End. На рисунке последняя ячейка - B3, так как форматирование было явно применено к ней. Ячейки B6: D7 имеют формат, полученный из правила условного форматирования, и это не обнаруживается даже с помощью UsedRange. Для этого потребуется некоторое программирование VBA.

Что касается вашего конкретного вопроса: в чем причина этого?

Ваш код использует первую ячейку вашего диапазона E4: E48 как батут, для End(xlDown) с помощью End(xlDown).

"Ошибочный" вывод будет получен, если в вашем диапазоне нет ненужных ячеек, кроме, возможно, первого. Затем вы прыгаете в темноте, то есть вниз по листу (обратите внимание на разницу между пустой и пустой строкой!).

Обратите внимание, что:

1. Если ваш диапазон содержит несмежные непустые ячейки, это также даст неверный результат.

2. Если есть только одна непустая ячейка, но она не первая, ваш код по-прежнему даст вам правильный результат.

Я создал эту однонаправленную функцию для определения последней строки, столбца и ячейки, будь то для данных, отформатированных (сгруппированных/комментариев/скрытых) ячеек или условного форматирования.

Sub LastCellMsg()
    Dim strResult As String
    Dim lngDataRow As Long
    Dim lngDataCol As Long
    Dim strDataCell As String
    Dim strDataFormatRow As String
    Dim lngDataFormatCol As Long
    Dim strDataFormatCell As String
    Dim oFormatCond As FormatCondition
    Dim lngTempRow As Long
    Dim lngTempCol As Long
    Dim lngCFRow As Long
    Dim lngCFCol As Long
    Dim strCFCell As String
    Dim lngOverallRow As Long
    Dim lngOverallCol As Long
    Dim strOverallCell As String

    With ActiveSheet

        If .ListObjects.Count > 0 Then
            MsgBox "Cannot return reliable results, as there is at least one table in the worksheet."
            Exit Sub
        End If

        strResult = "Workbook name: " & .Parent.Name & vbCrLf
        strResult = strResult & "Sheet name: " & .Name & vbCrLf

        'DATA:
        'last data row
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lngDataRow = .Cells.Find(What:="*", _
             After:=.Range("A1"), _
             Lookat:=xlPart, _
             LookIn:=xlFormulas, _
             SearchOrder:=xlByRows, _
             SearchDirection:=xlPrevious, _
             MatchCase:=False).Row
        Else
            lngDataRow = 1
        End If
        'strResult = strResult & "Last data row: " & lngDataRow & vbCrLf

        'last data column
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lngDataCol = .Cells.Find(What:="*", _
             After:=.Range("A1"), _
             Lookat:=xlPart, _
             LookIn:=xlFormulas, _
             SearchOrder:=xlByColumns, _
             SearchDirection:=xlPrevious, _
             MatchCase:=False).Column
        Else
            lngDataCol = 1
        End If
        'strResult = strResult & "Last data column: " & lngDataCol & vbCrLf

        'last data cell
        strDataCell = Replace(Cells(lngDataRow, lngDataCol).Address, "$", vbNullString)
        strResult = strResult & "Last data cell: " & strDataCell & vbCrLf

        'FORMATS:
        'last data/formatted/grouped/commented/hidden row
        strDataFormatRow = StrReverse(Split(StrReverse(.UsedRange.Address), "$")(0))
        'strResult = strResult & "Last data/formatted row: " & strDataFormatRow & vbCrLf

        'last data/formatted/grouped/commented/hidden column
        lngDataFormatCol = Range(StrReverse(Split(StrReverse(.UsedRange.Address), "$")(1)) & "1").Column
        'strResult = strResult & "Last data/formatted column: " & lngDataFormatCol & vbCrLf

        'last data/formatted/grouped/commented/hidden cell
        strDataFormatCell = Replace(Cells(strDataFormatRow, lngDataFormatCol).Address, "$", vbNullString)
        strResult = strResult & "Last data/formatted cell: " & strDataFormatCell & vbCrLf

        'CONDITIONAL FORMATS:
        For Each oFormatCond In .Cells.FormatConditions

            'last conditionally-formatted row
            lngTempRow = CLng(StrReverse(Split(StrReverse(oFormatCond.AppliesTo.Address), "$")(0)))
            If lngTempRow > lngCFRow Then lngCFRow = lngTempRow

            'last conditionally-formatted column
            lngTempCol = Range(StrReverse(Split(StrReverse(oFormatCond.AppliesTo.Address), "$")(1)) & "1").Column
            If lngTempCol > lngCFCol Then lngCFCol = lngTempCol
        Next
        'no results are returned for Conditional Format if there is no such
        If lngCFRow <> 0 Then
            'strResult = strResult & "Last cond-formatted row: " & lngCFRow & vbCrLf
            'strResult = strResult & "Last cond-formatted column: " & lngCFCol & vbCrLf

            'last conditionally-formatted cell
            strCFCell = Replace(Cells(lngCFRow, lngCFCol).Address, "$", vbNullString)
            strResult = strResult & "Last cond-formatted cell: " & strCFCell & vbCrLf
        End If

        'OVERALL:
        lngOverallRow = Application.WorksheetFunction.Max(lngDataRow, strDataFormatRow, lngCFRow)
        'strResult = strResult & "Last overall row: " & lngOverallRow & vbCrLf
        lngOverallCol = Application.WorksheetFunction.Max(lngDataCol, lngDataFormatCol, lngCFCol)
        'strResult = strResult & "Last overall column: " & lngOverallCol & vbCrLf
        strOverallCell = Replace(.Cells(lngOverallRow, lngOverallCol).Address, "$", vbNullString)
        strResult = strResult & "Last overall cell: " & strOverallCell & vbCrLf

        MsgBox strResult
        Debug.Print strResult

    End With

End Sub

Результаты выглядят следующим образом:

Для получения более подробных результатов некоторые строки в коде могут быть раскоментированы:

Существует одно ограничение - если в листе есть таблицы, результаты могут стать ненадежными, поэтому я решил не запускать код в этом случае:

If .ListObjects.Count > 0 Then
    MsgBox "Cannot return reliable results, as there is at least one table in the worksheet."
    Exit Sub
End If

Одно важное замечание, которое следует учитывать при использовании решения...

LastRow = ws.Cells.Find(What:="*", After:=ws.range("a1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

... заключается в том, чтобы ваша переменная LastRow имела тип Long:

Dim LastRow as Long

В противном случае вы получите ошибки OVERFLOW в определенных ситуациях в книгах .XLSX.

Это моя инкапсулированная функция, которую я перехожу к различным использованиям кода.

Private Function FindLastRow(ws As Worksheet) As Long
    ' --------------------------------------------------------------------------------
    ' Find the last used Row on a Worksheet
    ' --------------------------------------------------------------------------------
    If WorksheetFunction.CountA(ws.Cells) > 0 Then
        ' Search for any entry, by searching backwards by Rows.
        FindLastRow = ws.Cells.Find(What:="*", After:=ws.range("a1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End If
End Function

Я бы добавил к ответу, заданному Siddarth Rout, чтобы сказать, что вызов CountA можно пропустить, если Find вернет объект Range, а не номер строки, а затем проверит возвращаемый объект Range, чтобы увидеть, что это Nothing ( пустой лист).

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

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

Function GetLastCell(sh as Worksheet) As Range
    GetLastCell = sh.Cells(1,1).SpecialCells(xlLastCell)
End Function

Это по существу возвращает ту же ячейку, которую вы получаете с помощью Ctrl + End после выбора Cell A1.

Предупреждение: Excel отслеживает самую нижнюю правую ячейку, которая когда-либо использовалась на листе. Итак, если вы, например, вводите что-то в B3 и что-то еще в H8, а затем удалите содержимое H8, нажав Ctrl + End по-прежнему приведет вас к ячейке H8. Вышеуказанная функция будет иметь такое же поведение.

sub last_filled_cell()
msgbox range("a65536").end(xlup).row
end sub

"здесь a65536 - последняя ячейка в столбце a, этот код был протестирован на excel sti72003" 200

и если u использует "A1,048,576"

мой код предназначен только для новичков, чтобы понять концепции того, что конец (xlup) и другие связанные команды могут выполнять

Однако этот вопрос пытается найти последнюю строку с использованием VBA, я думаю, было бы неплохо включить формулу массива для функции листа, поскольку это часто посещается:

{=ADDRESS(MATCH(INDEX(D:D,MAX(IF(D:D<>"",ROW(D:D)-ROW(D1)+1)),1),D:D,0),COLUMN(D:D))}

Вам нужно ввести формулу без скобок, а затем нажать Shift + Ctrl + Enter, чтобы сделать форму формулой массива.

Это даст вам адрес последней использованной ячейки в столбце D.

Поскольку исходный вопрос касается проблем с поиском последней ячейки, в этом ответе я расскажу о различных способах получения неожиданных результатов; см. мой ответ на вопрос "Как найти последнюю строку, содержащую данные на листе Excel с макросом?" для моего решения этого.

Я начну с расширения ответа на вопрос sancho.s и комментария GlennFromIowa, добавив еще более подробную информацию:

[...] сначала нужно решить, что считается используемым. Я вижу по крайней мере 6 значений. У ячейки есть:

• 1) данные, т.е. Формула, возможно, приводящая к пустому значению;
• 2) значение, т.е. Непустую формулу или константу;
• 3) форматирование;
• 4) условное форматирование;
• 5) форма (включая комментарий), перекрывающая ячейку;
• 6) участие в таблице (объект списка).

Какую комбинацию вы хотите протестировать? Некоторые (например, Таблицы) могут быть более трудными для тестирования, а некоторые могут быть редкими (например, форма вне диапазона данных), но другие могут варьироваться в зависимости от ситуации (например, формулы с пустыми значениями).

Другие вещи, которые вы, возможно, захотите рассмотреть:

• A) Могут ли быть скрытые строки (например, автофильтр), пустые ячейки или пустые строки?
• B) Какая производительность приемлема?
• C) Может ли макрос VBA влиять на рабочую книгу или параметры приложения каким-либо образом?

Имея это в виду, давайте посмотрим, как обычные способы получения "последней ячейки" могут привести к неожиданным результатам:

• Код .End(xlDown) из вопроса будет наиболее легко разбиваться (например, с одной непустой ячейкой или когда между ними пробелы пробелы) по причинам, объясняемым в ответе по Siddharth Rout здесь (поиск "xlDown" одинаково ненадежным. ")
• Любое решение, основанное на Count Инг (CountA или Cells*.Count) или .CurrentRegion также перерыв в присутствии пустых ячеек или строк
• Решение, использующее .End(xlUp) для поиска назад от конца столбца, будет выглядеть так же, как CTRL + UP, для поиска данных (формулы, производящие пустое значение, считаются "данными") в видимых строках (поэтому использование его с включенным автофильтром может привести к неправильным результатам ️).

  Вы должны позаботиться о том, чтобы избежать стандартных подводных камней (подробности я еще раз обращусь к ответу на Siddharth Rout здесь, найдите раздел "Найти последнюю строку в столбце"), например, жесткое кодирование последней строки (Range("A65536").End(xlUp)) вместо того, чтобы полагаться на sht.Rows.Count.

• .SpecialCells(xlLastCell) эквивалентен CTRL + END, возвращая самую нижнюю и самую правую ячейку "используемого диапазона", поэтому все оговорки, которые применяются для использования "используемого диапазона", также применяются к этому методу. Кроме того, "используемый диапазон" сбрасывается только при сохранении рабочей книги и при доступе к worksheet.UsedRange xlLastCell, поэтому xlLastCell может производить устаревшие результаты с несохраненными модификациями (например, после того, как некоторые строки были удалены). См. Ближайший ответ dotNET.
• sht.UsedRange (подробно описанный в ответе от sancho.s здесь) рассматривает как данные, так и форматирование (хотя и не условное форматирование) и сбрасывает "используемый диапазон" рабочего листа, который может быть или не быть тем, что вы хотите.

  Обратите внимание, что распространенная ошибка ️ заключается в использовании .UsedRange.Rows.Count ️, который возвращает количество строк в используемом диапазоне, а не последнее число строк (они будут отличаться, если первые несколько строк пустые), подробности см. newguy answer Как найти последнюю строку, содержащую данные на листе Excel с макросом?

• .Find позволяет вам найти последнюю строку с любыми данными (включая формулы) или .Find значением в любом столбце. Вы можете выбрать, интересуетесь ли вы формулами или значениями, но уловка заключается в том, что он сбрасывает значения по умолчанию в диалоговом окне поиска Excel ️️️, что может быть очень запутанным для ваших пользователей. Его также нужно тщательно использовать, см. Ответ на Siddharth Rout здесь (раздел "Найти последнюю строку в листе")
• Более явные решения, которые проверяют отдельные Cells в цикле, как правило, медленнее, чем повторное использование функции Excel (хотя она все еще может быть выполнена), но позволяет точно указать, что вы хотите найти. Посмотрите мое решение на UsedRange массивов UsedRange и VBA, чтобы найти последнюю ячейку с данными в указанном столбце - она обрабатывает скрытые строки, фильтры, пробелы, не изменяет параметры поиска по умолчанию и довольно эффективна.

Какое бы решение вы ни выбрали, будьте осторожны

• использовать Long вместо Integer для хранения номеров строк (чтобы избежать Overflow с более чем 65k строк) и
• всегда указывать рабочий лист, с которым вы работаете (т.е. Dim ws As Worksheet... ws.Range(...) вместо Range(...))
• при использовании .Value (который является Variant), избегайте неявных отливок, таких как .Value <> "" поскольку они будут терпеть неудачу, если ячейка содержит значение ошибки.

Я искал способ имитировать CTRL + Shift + End, поэтому решение dotNET отлично, за исключением моего Excel 2010, мне нужно добавить set, если я хочу избежать ошибки

Function GetLastCell(sh As Worksheet) As Range
  Set GetLastCell = sh.Cells(1, 1).SpecialCells(xlLastCell)
End Function

и как проверить это для себя:

Sub test()
  Dim ws As Worksheet, r As Range
  Set ws = ActiveWorkbook.Sheets("Sheet1")
  Set r = GetLastCell(ws)
  MsgBox r.Column & "-" & r.Row
End Sub

Sub lastRow()

    Dim i As Long
        i = Cells(Rows.Count, 1).End(xlUp).Row
            MsgBox i

End Sub

sub LastRow()

'Paste & for better understanding of the working use F8 Key to run the code .

dim WS as worksheet
dim i as long

set ws = thisworkbook("SheetName")

ws.activate

ws.range("a1").select

ws.range("a1048576").select

activecell.end(xlup).select

i= activecell.row

msgbox "My Last Row Is " & i

End sub

В течение последних 3+ лет это функции, которые я использую для поиска последней строки и последнего столбца для определенного столбца (для строки) и строки (для столбца):

Последняя колонка:

Function lastCol(Optional wsName As String, Optional rowToCheck As Long = 1) As Long

    Dim ws  As Worksheet

    If wsName = vbNullString Then
        Set ws = ActiveSheet
    Else
        Set ws = Worksheets(wsName)
    End If

    lastCol = ws.Cells(rowToCheck, ws.Columns.Count).End(xlToLeft).Column

End Function

Последняя строка:

Function lastRow(Optional wsName As String, Optional columnToCheck As Long = 1) As Long

    Dim ws As Worksheet

    If wsName = vbNullString Then
        Set ws = ActiveSheet
    Else
        Set ws = Worksheets(wsName)
    End If

    lastRow = ws.Cells(ws.Rows.Count, columnToCheck).End(xlUp).Row

End Function

Для случая OP это способ получить последнюю строку в столбце E:

Debug.Print lastRow(columnToCheck:=Range("E4:E48").Column)