Как Найти Непустые Ячейки в Excel Vba • Дублирование строк

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

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

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

12 ответов

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

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Три остальные константы имитируют комбинации клавиш при выделении в других направлениях xlUp вверх , xlToLeft влево и xlToRight вправо. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
А теперь нужно продублировать строки, в результате чего количество строк для каждого участника лотереи будут соответствовать количеству купленных им билетов. Например, если Барбара приобрела два билета, для нее создаются две строки. Ниже показана процедура, выполняющая вставку новых строк.

Удаляем в EXCEL пропуски в списке (формулы). Примеры и описание

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

Обратите внимание на использование оператора SetTheCell. Функция CellType получает аргумент-диапазон произвольного размера, но этот оператор указывает, что функция оперирует только левой верхней ячейкой диапазона (представленной переменной TheCell).

Задача

Убрать пустые ячейки из списка, сформировав формулами список в соседнем столбце. То есть под словом «убрать» будем понимать не удаление значения из исходного списка, а формирование еще одного списка, но уже без лишних символов. Чтобы действительно убрать значения из списка нужно использовать макросы — программу на VBA.

Для избавления от пустых ячеек запишем в ячейке B2 формулу массива и скопируем ее вниз (см. файл примера): =ЕСЛИОШИБКА(ДВССЫЛ(«A»&НАИМЕНЬШИЙ(ЕСЛИ(ЕПУСТО($A$2:$A$14);»»;СТРОКА($A$2:$A$14));СТРОКА(A1)));»»)

Получим в соседнем столбце B список со значениями из исходого, но уже без пропусков. Формула работает одинакового и для текстовых значений и для чисел. Алгоритм работы формулы следующий:

  • ЕСЛИ(ЕПУСТО($A$2:$A$14);»»;СТРОКА($A$2:$A$14)) – если ячейка не пуста, то эта часть формулы возвращает номер строки. То есть формируется массив номеров строк, НЕ содержащих пустоты На месте пустых ячеек в массиве будет символ «» (пустой текст), но можно его заменить в формуле на любую текстовую строку, например «ккк». Проверить результат можно выделив эту часть формулы и нажав клавишу F9 ;
  • Функция НАИМЕНЬШИЙ() сортирует массив строк по возрастанию. В сортированном списке сначала будут идти номера строк затем значения «», т.к. в EXCEL считается, что любое текстовое значение больше любого числа (значение пустой текст — текстовое значение);
  • Далее для функции ДВССЫЛ() формируются адреса ячеек с непустыми значениями. Например, ДВССЫЛ(«A»&2) возвращает значение из ячейки А2 . Для пустых ячеек будет формироваться ошибочные адреса ячеек, состоящие только из символа А. Это вызовет ошибку после применения функции ДВССЫЛ();
  • Функция ЕСЛИОШИБКА() вместо ошибки будет возвращать «». Этот символ не отображается в ячейке и ячейка выглядит пустой.

Изменим немного формулу: =ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ( ЕПУСТО($A$2:$A$14);»»;$A$2:$A$14);СТРОКА(A1));»»)

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

Как Найти Непустые Ячейки в Excel Vba • Дублирование строк

Список также можно сформировать в столбце С другой формулой массива :

СписокСпропусками в формуле — это динамический диапазон , который образован формулой:

Длину списка с пропусками можно вычислить с помощью формулы:

Для удаления и выделения пустых строк и ячеек традиционными средствами EXCEL, пользуйтесь идеями из статей Удаляем пустые строки в таблице и Выделение группы ячеек . О том, что EXCEL понимает под пустыми ячейками, читайте в статье Подсчет пустых ячеек .

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Другой трюк заключается в том, чтобы по-прежнему использовать UsedRange, но использовать его как абсолютный но ненадежный максимум, из которого вы определяете правильный максимум. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Спасибо за ваш отличный ответ! Это мне очень помогло. Я хотел бы перевести эту статью, чтобы поделиться с моими корейскими друзьями. Это будет размещено здесь. Ctrlaltdel Пожалуйста, дайте мне знать, если вы не возражаете. тогда я удалю это.
Как Найти Непустые Ячейки в Excel Vba • Дублирование строк

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

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

Копирование диапазона

Функция записи макросов Excel используется не столько для создания хорошего кода, сколько для поиска названий необходимых объектов, методов и свойств. Например, при записи операции копирования и вставки можно получить код:

Sub Макрос()
Range( » A1 » ).Select
Selection.Copy
Range( » B1 » ).Select
ActiveSheet.Paste
End Sub

Обратите внимание, что данная программа выделяет ячейки. Однако в VBA для работы с объектом не обязательно его выделять. Данную процедуру можно заменить значительно более простой — применить метод Сору, который использует аргумент, представляющий адрес места вставки копируемого диапазона.

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

Sub CopyRange2()
Workbooks( » File1.xlsx » ).Sheets( » Лист1 » ).Range( » A1 » ).Copy _
Workbooks( » File2.xlsx » ).Sheets( » Лист2 » ).Range( » A1 » )
End Sub

Еще одним подходом к решению этой задачи является использование для представления диапазонов объектных переменных:

Sub CopyRange3()
Dim Rngl As Range, Rng2 As Range
Set Rngl = Workbooks( » File1.xlsx » ).Sheets( » Лист1 » ).Range( » A1 » )
Set Rng2 = Workbooks( » File2.xlsx » ).Sheets( » Лист2 » ).Range( » A1 » )
Rngl.Copy Rng2 End Sub

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

Sub CopyRange4 ()
Range( » А1:С800 » ).Copy Range( » D1 » )
End Sub

Для перемещения диапазона ячеек вместо метода Сору используется метод Cut.

Если размер копируемого диапазона не известен используется свойство CurrentRegion, возвращающее объект Range, который соответствует прямоугольнику ячеек вокруг заданной ячейки:

Метод End имеет один аргумент, определяющий направление, в котором увеличивается выделение ячеек. Следующий оператор выделяет диапазон от активной ячейки до последней непустой ячейки внизу:

Три остальные константы имитируют комбинации клавиш при выделении в других направлениях: xlUp (вверх), xlToLeft (влево) и xlToRight (вправо).

В прилагаемом Excel-файле определено несколько распространенных типов выделения ячеек (см. рис. 1). Код любопытен тем, что является также примером создания контекстного меню.

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Функция COUNT пропускает пустые значения в D3, D4, D8 и D11 и подсчитывает количество ячеек, содержащих значения в столбце г. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Потому что это максимально возможное количество строк объекта Worksheet. Если вы записываете больше строк, чем 65536, то помещайте их на следующий лист книги — благо, что количество листов ограничено только оперативной памятью комьютера.

Excel ЧаВо часть III: Cells, Range, Rows и Columns

Убрать пустые ячейки из списка, сформировав формулами список в соседнем столбце. То есть под словом «убрать» будем понимать не удаление значения из исходного списка, а формирование еще одного списка, но уже без лишних символов. Чтобы действительно убрать значения из списка нужно использовать макросы — программу на VBA.

Понравилась статья? Поделиться с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: