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

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

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

12 ответов

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

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

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

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

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

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

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

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

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

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

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

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

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

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

@ Жан-Франсуа Корбетт: Возможно, неправильный выбор слова мной? Я имею в виду настоящую Последнюю Строку. Actual последняя строка не может быть таким же , как Last Row , что мы могли бы получить с помощью UsedRange

Сиддхарт Рут, не могли бы вы уточнить и объяснить свой последний комментарий? Почему вы можете получить два разных значения для двух описанных вами методов?

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

Обратите внимание, что .Find, к сожалению, портит настройки пользователя в диалоговом окне «Найти», т. Е. В Excel есть только 1 набор настроек для диалога, и вы используете .ind заменяет их. Другой трюк заключается в том, чтобы по-прежнему использовать UsedRange, но использовать его как абсолютный (но ненадежный) максимум, из которого вы определяете правильный максимум.

@CarlColijn: я бы не назвал это безобразием. Excel просто remembers последние настройки. Даже когда вы вручную выполняете Find , он запоминает последний параметр, который на самом деле является благом, если кто-то знает этот «факт»

Спасибо за ваш отличный ответ! Это мне очень помогло. Я хотел бы перевести эту статью, чтобы поделиться с моими корейскими друзьями. Это будет размещено здесь. Ctrlaltdel Пожалуйста, дайте мне знать, если вы не возражаете. тогда я удалю это.

@SiddharthRout Хороший подход! Если бы последняя использованная ячейка содержала только комментарий и ничего больше, нам понадобились бы два оператора Find (), чтобы найти его? Может ли метод Find () найти данные или комментарии?

@ Gary’sStudent: Нет. Вышеупомянутый. .Find не будет находить комментарии. Для комментариев вы можете использовать SpecialCells с xlCellTypeComments

Я нашел интересную ошибку в Excel2013. Когда ActiveCell находится в таблице, результатом является последняя строка таблицы. Чтобы решить эту проблему, я назначаю ActiveCell временную переменную диапазона, затем Range(«A1»).Select , затем найдите, затем временный range.select. Уродливо, но эффективно.

Кажется, он работает для столбца A, но если я настраиваю диапазон на C1, он всегда возвращает 1 как последнюю строку (не из-за оператора Else). Так что не уверен, что здесь не так

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

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

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

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

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

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

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

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

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

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

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

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

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

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

@franklin — я только что заметил входящее сообщение с вашим исправлением, которое было отклонено рецензентами. Я исправил эту ошибку. Я уже использовал эту функцию один раз, когда мне было нужно, и я буду использовать ее снова, так что, действительно, огромное спасибо, мой друг!

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

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

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

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

[expert_bq id=»1570″]В первую очередь рассмотрим все доступные варианты поиска номера последней заполненной строки для таблиц, расположенных в верхнем левом углу рабочего листа. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] @phan: Введите что-нибудь в ячейку A5. Теперь, когда вы вычислите последнюю строку любым из методов, приведенных выше, вы получите 5. Теперь закрасьте ячейку A10 красным. Если вы сейчас используете любой из вышеприведенного кода, вы все равно получите 5. Если вы используете Usedrange.Rows.Count что вы получите? Это не будет 5. Usedrange крайне ненадежен, чтобы найти последний ряд.

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

Вы можете получить доступ ко всем таблицам на вашем листе с помощью коллекции ListObjects. На приведенном ниже листе у нас есть две таблицы, и мы хотели бы добавить столбец с чередованием к обеим таблицам сразу и изменить шрифт раздела данных обеих таблиц на полужирный, используя VBA.