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

Когда я хочу найти последнее использованное значение ячейки, я использую:

Dim LastRow As Long

LastRow = Range("E4:E48").End(xlDown).Row

Debug.Print LastRow

Я получаю неправильный вывод, когда я помещаю один элемент в ячейку. Но когда я помещаю в ячейку несколько значений, результат правильный. В чем причина этого?

Ответы

Ответ 1

ПРИМЕЧАНИЕ. Я намерен сделать это "одной остановкой", где вы можете использовать способ Correct для поиска последней строки. Это также будет охватывать лучшие практики, которые следует соблюдать при поиске последней строки. И поэтому я буду продолжать его обновлять всякий раз, когда я сталкиваюсь с новым сценарием/информацией.

Ненадежные способы поиска последней строки

Некоторые из наиболее распространенных способов нахождения последней строки, которые являются очень ненадежными и, следовательно, никогда не должны использоваться.

UsedRange
xlDown
COUNTA

UsedRange должен НИКОГДА использоваться для поиска последней ячейки, у которой есть данные. Это очень ненадежно. Попробуйте этот эксперимент.

Введите что-то в ячейку A5. Теперь, когда вы вычисляете последнюю строку с помощью любого из приведенных ниже методов, она даст вам 5. Теперь окрасьте ячейку A10 в красный цвет. Если вы теперь используете любой из приведенных ниже кодов, вы все равно получите 5. Если вы используете Usedrange.Rows.Count, что вы получите? Это не будет 5.

Вот сценарий, показывающий, как работает UsedRange.

xlDown одинаково ненадежна.

Рассмотрим этот код

lastrow = Range("A1").End(xlDown).Row

Что произойдет, если будет только одна ячейка (A1), у которой есть данные? Вы попадете в последний ряд на листе! Это как выбрать ячейку A1, а затем нажать клавишу End, а затем нажать клавишу Down Arrow. Это также даст вам ненадежные результаты, если в диапазоне есть пустые ячейки.

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

И поэтому следует избегать использования UsedRange, xlDown и CountA, чтобы найти последнюю ячейку.

Найти последнюю строку в столбце

Чтобы найти последнюю строку в Col E, используйте этот

With Sheets("Sheet1")
    LastRow = .Range("E" & .Rows.Count).End(xlUp).Row
End With

Если вы заметили, что перед Rows.Count мы имеем .. Мы часто игнорировали это. См. ЭТО вопрос о возможной ошибке, которую вы можете получить. Я всегда советую использовать . до Rows.Count и Columns.Count. Этот вопрос является классическим сценарием, когда код завершится неудачно, потому что Rows.Count возвращает 65536 для Excel 2003 и более ранних версий и 1048576 для Excel 2007 и более поздних версий. Аналогично Columns.Count возвращает 256 и 16384 соответственно.

Вышеупомянутый факт, что Excel 2007+ имеет строки 1048576, также подчеркивает тот факт, что мы всегда должны объявлять переменную, которая будет удерживать значение строки как Long вместо Integer else, вы получите Overflow ошибка.

Найти последнюю строку в листе

Чтобы найти последнюю строку Effective на листе, используйте это. Обратите внимание на использование Application.WorksheetFunction.CountA(.Cells). Это необходимо, потому что, если в листе нет ячеек с данными, тогда .Find предоставит вам Run Time Error 91: Object Variable or With block variable not set

With Sheets("Sheet1")
    If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
        lastrow = .Cells.Find(What:="*", _
                      After:=.Range("A1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False).Row
    Else
        lastrow = 1
    End If
End With

Найти последнюю строку в таблице (ListObject)

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

Sub FindLastRowInExcelTableColAandB()
Dim lastRow As Long
Dim ws As Worksheet, tbl as ListObject
Set ws = Sheets("Sheet1")  'Modify as needed
'Assuming the name of the table is "Table1", modify as needed
Set tbl = ws.ListObjects("Table1")

With tbl.ListColumns(3).Range
    lastrow = .Find(What:="*", _
                After:=.Cells(1), _
                Lookat:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Row
End With

End Sub

Ответ 2

Примечание: этот ответ был мотивирован этим комментарием. Назначение UsedRange отличается от того, что упомянуто в ответе выше.

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

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

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

По критерию 1 предлагаю прочитать этот ответ. Обратите внимание, что 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).

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

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

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

Ответ 3

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

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

Ответ 4

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

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

Ответ 5

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

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

Ответ 6

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

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. Вышеуказанная функция будет иметь такое же поведение.

Ответ 7

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

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

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

1) данные, то есть формула, возможно, приводящая к пустому значению;

2) значение, т.е. Непустая формула или константа;

3) форматирование;

4) условное форматирование;

5) форма (включая комментарий), перекрывающая ячейку;

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

Другие вещи, которые вы могли бы рассмотреть:

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

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

Код .End(xlDown) из вопроса будет легче всего сломаться (например, с одной непустой ячейкой или когда между ними будут пустые ячейки) по причинам, объясненным здесь в ответе Сиддхарта Роута (поиск для "xlDown одинаково"). ненадежный. ") 👎
Любое решение, основанное на Count Инг (CountA или Cells*.Count) или .CurrentRegion также перерыв в присутствии пустых ячеек или строк 👎
Решение, использующее .End(xlUp) для поиска в обратном направлении от конца столбца, так же, как CTRL + UP, будет искать данные (формулы, создающие пустое значение, считаются "данными") в видимых строках (поэтому, используя его с включенным автофильтром) может привести к неверным результатам ⚠️).
Вы должны позаботиться о том, чтобы избежать стандартных ловушек (подробности, я снова обращусь к ответу Сиддхарта Раута здесь, ищите раздел "Найти последнюю строку в столбце"), такие как жесткое кодирование последней строки (Range("A65536").End(xlUp)) вместо того, чтобы полагаться на sht.Rows.Count.
.SpecialCells(xlLastCell) эквивалентен CTRL + END, возвращая самую нижнюю и самую правую ячейку "используемого диапазона", поэтому все предупреждения, которые относятся к зависимости от "используемого диапазона", также применимы к этому методу. Кроме того, "используемый диапазон" сбрасывается только при сохранении рабочей книги и при доступе к worksheet.UsedRange xlLastCell, поэтому xlLastCell может привести к устаревшим результатам⚠️ с несохраненными изменениями (например, после удаления некоторых строк). Смотрите соседний ответ по dotNET.
sht.UsedRange (подробно описанный в ответе sancho.s здесь) учитывает как данные, так и форматирование (хотя и не условное форматирование) и сбрасывает "используемый диапазон" листа, который может соответствовать, а может и не соответствовать желаемому.
Обратите внимание, что распространенная ошибка "использовать .UsedRange.Rows.Count, которая возвращает количество строк в используемом диапазоне, а не номер последней строки (они будут отличаться, если первые несколько строк будут пустыми), подробности см. Новый ответ на Как я могу найти последнюю строку, которая содержит данные в листе Excel с макросом?
.Find позволяет найти последнюю строку с любыми данными (включая формулы) или .Find значением в любом столбце. Вы можете выбрать, заинтересованы ли вы в формулах или значениях, но выгода заключается в том, что он сбрасывает значения по умолчанию в диалоговом окне "Найти в Excel" ️️⚠️, что может очень запутать ваших пользователей. Его также необходимо использовать осторожно, см. Ответ Сиддхарта Раута здесь (раздел "Найти последнюю строку в листе")
Более явные решения, которые проверяют отдельные Cells в цикле, обычно медленнее, чем повторное использование функции Excel (хотя все еще могут быть эффективными), но позволяют вам точно указать, что вы хотите найти. Посмотрите мое решение на UsedRange массивов UsedRange и VBA, чтобы найти последнюю ячейку с данными в данном столбце - он обрабатывает скрытые строки, фильтры, пропуски, не изменяет значения по умолчанию Find и довольно производительный.

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

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

Ответ 8

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

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

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

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

Ответ 9

Однако этот вопрос пытается найти последнюю строку с использованием 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.

Ответ 10

Я искал способ имитировать 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

Ответ 11

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

Ответ 12

В течение последних 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)