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

Question

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

Dim LastRow As Long

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

Debug.Print LastRow

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

Answer 1

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

---

Ненадежные способы поиска последнего ряда

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

1. UsedRange
2. xlDown
3. COUNTA

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

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

Вот сценарий, чтобы показать, как UsedRange работает.

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

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

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

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

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 иначе вы получите 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

Answer 2

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

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

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

3. Используется = "... используется", что означает раздел, содержащий данные или форматирование " или условное форматирование. То же, что и 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),

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

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

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

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

Answer 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

Answer 4

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Answer 5

Важно помнить одно важное замечание при использовании решения...

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

Answer 6

Я бы добавил к ответу, заданному Сиддартом Рутом, чтобы сказать, что вызов CountA можно пропустить с помощью функции Find, возвращающей объект Range вместо номера строки, а затем проверим возвращенный объект Range, чтобы убедиться, что это Nothing (пустой лист),

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

Answer 7

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

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

По сути, это возвращает ту же ячейку, которую вы получаете по Ctrl + End после выбора ячейки A1,

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

Answer 8

Вот мои два цента.

IMHO риск скрытой строки с исключенными данными слишком велик, чтобы позволить xlUpсчитаться универсальным ответом. Я согласен, что это просто и будет работать в большинстве случаев, но это может привести к занижению последней строки без предупреждения. Это могло привести к КАТАСТРОФИЧЕСКИМ результатам в какой-то момент для тех, кто прыгнул на Stack Overlow и искал "верный способ" зафиксировать это значение.

В Findметод безупречен, и я бы одобрил его как универсальный ответ. Однако недостаток измененияFind настройки могут раздражать, особенно если это часть UDF.

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

Function LastRowNumber(Optional rng As Range) As Long

If rng Is Nothing Then
    Set rng = ActiveSheet.UsedRange
Else
    Set rng = Intersect(rng.Parent.UsedRange, rng.EntireColumn)
    If rng Is Nothing Then
        LastRowNumber = 1
        Exit Function
    ElseIf isE = 0 Then
        LastRowNumber = 1
        Exit Function

    End If

End If

LastRowNumber = rng.Cells(rng.Rows.Count, 1).Row

Do While IsEmpty(Intersect(rng, _
    rng.Parent.Rows(LastRowNumber)))

    LastRowNumber = LastRowNumber - 1
Loop

End Function

Почему это хорошо:

• Достаточно просто, не много переменных.
• Позволяет использовать несколько столбцов.
• Не изменяет Find настройки
• Динамический, если используется как UDF с выбранным целым столбцом.

Почему это плохо:

• При очень больших наборах данных и огромном промежутке между используемым диапазоном и последней строкой в ​​указанных столбцах это будет выполняться медленнее, а в редких случаях ЗНАЧИТЕЛЬНО медленнее.

Однако я думаю, что универсальное решение, имеющее недостаток в том, что findнастройки или медленная работа - лучшее общее решение. Затем пользователь может повозиться со своими настройками, чтобы попытаться улучшить, зная, что происходит с его кодом. С помощьюxLUp не будут предупреждать о потенциальных рисках, и они могут продолжать неизвестно сколько времени, не зная, что их код работает некорректно.

Answer 9

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

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

и если вы используете его "a1,048,576"

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

Answer 10

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

Answer 11

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

Answer 12

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

Answer 13

За последние 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)

Answer 14

Последняя строка в обычном диапазоне или таблица (ListObject)

1. Для поиска последней строки необходимо использовать разные методы, если диапазон является обычным диапазоном или таблицей (объект списка).
2. Для поиска последней строки в таблицах необходимо указать дополнительные параметры (имя таблицы, положение столбца относительно первого столбца таблицы).

Я создал эту универсальную функцию для последней строки, независимо от типа диапазона. Просто дайте ему любую ссылку на ячейку, и он вернет последнюю строку. Нет проблем с необходимостью знать характеристики диапазона, особенно если ваши диапазоны иногда являются обычным диапазоном, а иногда и ListObject. Использование обычного метода диапазона для таблицы может привести к неверным результатам. Конечно, вы можете планировать заранее и каждый раз использовать правильный метод, но зачем беспокоиться, если вы можете использовать универсальную функцию?

       Sub RunMyLastRow()
Dim Result As Long
Result = MyLastRow(Worksheets(1).Range("A1"))
End Sub

          Function MyLastRow(RefrenceRange As Range) As Long
    Dim WS As Worksheet
    Dim TableName As String
    Dim ColNumber As Long
    Dim LastRow As Long
    Dim FirstColumnTable As Long
    Dim ColNumberTable As Long
    Set WS = RefrenceRange.Worksheet
    TableName = GetTableName(RefrenceRange)
    ColNumber = RefrenceRange.Column
    
    ''If the table (ListObject) does not start in column "A" we need to calculate the 
    ''first Column table and how many Columns from its beginning the Column is located.
    If TableName <> vbNullString Then
     FirstColumnTable = WS.ListObjects(TableName).ListColumns(1).Range.Column
     ColNumberTable = ColNumber - FirstColumnTable + 1
    End If 

    If TableName = vbNullString Then
    LastRow = WS.Cells(WS.Rows.Count, ColNumber).End(xlUp).Row
    Else
    LastRow = WS.ListObjects(TableName).ListColumns(ColNumberTable).Range.Find( _
               What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End If
    MyLastRow = LastRow
    End Function
    

           ''Get Table Name by Cell Range
    Function GetTableName(RefrenceRange As Range) As String
        If RefrenceRange.ListObject Is Nothing Then
            GetTableName = vbNullString
        Else
            GetTableName = RefrenceRange.ListObject.Name
        End If
    End Function

Answer 15

Последняя строка в обычном диапазоне или таблица (ListObject)

Это решение требует меньшего количества свойств диапазона для вычисления последней строки.

Для поиска последней строки требуется:

1. Зная заранее, является ли диапазон обычным диапазоном или таблицей (объектом списка). Использование обычного метода поиска по диапазону в таблице может дать неверные результаты.
2. Указание параметров, требующих большего количества строк кода (имя таблицы, положение столбца относительно первого столбца таблицы).

       Sub RunMyLastRow()
Dim Result As Long
Result = MyLastRow(Worksheets(1).Range("D4"))
End Sub

          Function MyLastRow(RefrenceRange As Range) As Long
    Dim WS As Worksheet
    Dim TableName As String
    Dim ColNumber As Long
    Dim LastRow As Long
    Dim FirstColumnTable As Long
    Dim ColNumberTable As Long
    Set WS = RefrenceRange.Worksheet
    TableName = GetTableName(RefrenceRange)
    ColNumber = RefrenceRange.Column
    
    ''If the table (ListObject) does not start in column "A" we need to calculate the 
    ''first Column table and how many Columns from its beginning the Column is located.
    If TableName <> vbNullString Then
     FirstColumnTable = WS.ListObjects(TableName).ListColumns(1).Range.Column
     ColNumberTable = ColNumber - FirstColumnTable + 1
    End If 

    If TableName = vbNullString Then
    LastRow = WS.Cells(WS.Rows.Count, ColNumber).End(xlUp).Row
    Else
    LastRow = WS.ListObjects(TableName).ListColumns(ColNumberTable).Range.Find( _
               What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End If
    MyLastRow = LastRow
    End Function
    
    ''Get Table Name by Cell Range
    Function GetTableName(RefrenceRange As Range) As String
        If RefrenceRange.ListObject Is Nothing Then
            GetTableName = vbNullString
        Else
            GetTableName = RefrenceRange.ListObject.Name
        End If
    End Function

Answer 16

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

Для поиска последней строки требуется:

1. Указание нескольких параметров (имя таблицы, столбец внутри таблицы относительно первого столбца, рабочий лист, диапазон).
2. Может потребоваться переключение между методами. например, если диапазон является таблицей (объект списка) или нет. Использование неправильного типа поиска приведет к неверным результатам.

Предлагаемое решение является более общим, требует только диапазона, с меньшей вероятностью опечаток и коротким (просто вызов MyLastRow

функция).

       Sub test()
Dim rng As Range
Dim Result As Long
Set rng = Worksheets(1).Range("D4")
Result = MyLastRow(rng)
End Sub

          Function MyLastRow(FirstRow As Range) As Long
    Dim WS As Worksheet
    Dim TableName As String
    Dim ColNumber As Long
    Dim LastRow As Long
    Dim FirstColumnTable As Long
    Dim ColNumberTable As Long
    Set WS = FirstRow.Worksheet
    TableName = GetTableName(FirstRow)
    ColNumber = FirstRow.Column
    
    ''If the table (ListObject) does not start in column "A" we need to calculate the 
    ''first Column table and how many Columns from its beginning the Column is located.
    If TableName <> vbNullString Then
     FirstColumnTable = WS.ListObjects(TableName).ListColumns(1).Range.Column
     ColNumberTable = ColNumber - FirstColumnTable + 1
    End If 

    If TableName = vbNullString Then
    LastRow = WS.Cells(WS.Rows.Count, ColNumber).End(xlUp).Row
    Else
    LastRow = WS.ListObjects(TableName).ListColumns(ColNumberTable).Range.Find( _
               What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End If
    MyLastRow = LastRow
    End Function
    
    ''Get Table Name by Cell Range
    Function GetTableName(CellRange As Range) As String
        If CellRange.ListObject Is Nothing Then
            GetTableName = vbNullString
        Else
            GetTableName = CellRange.ListObject.Name
        End If
    End Function