Vba определение последней колонки

VBA Excel. Номер последней заполненной строки

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

Переменную, которой присваивается номер последней строки, следует объявлять как Long или Variant, например: Dim PosStr As Long . В современных версиях Excel количество строк на рабочем листе превышает максимальное значение типа данных Integer.

Таблица в верхнем левом углу

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

Пример таблицы с набором данных в Excel

Вариант 1
Основная формула для поиска последней строки в такой таблице, не требующая соблюдения каких-либо условий:
PosStr = Cells(1, 1).CurrentRegion.Rows.Count

Вариант 2
Ниже таблицы не должно быть никаких записей, в том числе ранее удаленных:
PosStr = ActiveSheet.UsedRange.Rows.Count

Вариант 3
В первом столбце таблицы не должно быть пропусков, а также в таблице должно быть не менее двух заполненных строк, включая строку заголовков:
PosStr = Cells(1, 1).End(xlDown).Row

Вариант 4
В первой колонке рабочего листа внутри таблицы не должно быть пропусков, а ниже таблицы в первой колонке не должно быть других заполненных ячеек:
PosStr = WorksheetFunction.CountA(Range(«A:A»))

Вариант 5
Ниже таблицы не должно быть никаких записей:
PosStr = Cells.SpecialCells(xlLastCell).Row

Последняя строка любой таблицы

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

Вариант 1
Основная формула для поиска последней строки в любой таблице, не требующая соблюдения каких-либо условий:
PosStr = Cells(a, b).CurrentRegion.Cells(Cells(a, b).CurrentRegion.Cells.Count).Row

Вариант 2
Дополнительная формула с условием, что в первом столбце таблицы нет пустых ячеек:
PosStr = Cells(a, b).End(xlDown).Row

Если у вас на рабочем листе Excel есть записи вне таблиц, следите за тем, чтобы таблицы были окружены пустыми ячейками или пустыми ячейками и границами листа. Тогда не будет случайно внесенных заметок, примыкающих к таблицам, которые могут отрицательно повлиять на точность вычисления номера последней строки из кода VBA.

Источник

Excel VBA-поиск последнего столбца с данными

Я нашел этот метод для поиска последних данных, содержащих строку в листе:

существует ли аналогичный метод для поиска последних данных, содержащих столбец в листе?

5 ответов

много способов сделать это. Самое надежное-найти.

Если вы хотите найти последний столбец, используемый в определенной строке, вы можете использовать:

используя используемый ряд (более менее надежный):

использование используемого диапазона не будет работать, если у вас нет данных в столбце A. см. здесь еще одну проблему с используемым диапазоном:

посмотреть здесь по поводу сброса используемого диапазона.

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

попробуйте использовать код после того, как вы активируете лист:

если вы используете Cells.SpecialCells(xlCellTypeLastCell).Row только проблема будет в том, что xlCellTypeLastCell информация не будет обновляться, если не выполнить действие «сохранить файл». Но используйте UsedRange всегда будет обновлять информацию в режиме реального времени.

Я думаю, мы можем изменить UsedRange код @Readify это ответ выше, чтобы получить последний используемый столбец, даже если начальные столбцы пусты или нет.

это lColumn = ws.UsedRange.Columns.Count изменен на

этой lColumn = ws.UsedRange.Column + ws.UsedRange.Columns.Count — 1 даст надежные результаты всегда

над линией выходы 9 в окне immediate.

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

Источник

Последнее значение в столбце Excel

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

Уровень мастерства: средний

Видео: 3 части серии как найти последнюю ячейку с VBA

Видео лучше всего просматривать в полноэкранном режиме HD

Загрузите файл, содержащий код:

Find Last Cell VBA Примеры.xlsm (79.6 KB)

Поиск последней ячейки — все о данных

Поиск последней используемой строки, столбца или ячейки — это одна из самых распространенных задач при написании макросов и приложений VBA. Как и все в Excel и VBA, есть много различных способов.

Выбор правильного метода в основном зависит от того, как выглядят ваши данные.

В этой статье я объясняю три различных метода VBA объекта Range, которые мы можем использовать для поиска последней ячейки на листе. Каждый из этих методов имеет плюсы и минусы, а некоторые выглядят страшнее других. 🙂

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

#1 – The Range.End() Method

Range.End() очень похож на сочетание клавиш Ctrl+Arrow. В VBA можно использовать этот метод, чтобы найти последнюю не пустую ячейку в одной строке или столбце.

Диапазон.Пример кода End VBA

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

Оператор Rows.Count возвращает количество всех строк на рабочем листе. Поэтому мы в основном указываем последнюю ячейку в столбце A листа (ячейка A1048567) и поднимаемся до тех пор, пока не найдем первую непустую ячейку.

Это работает так же с поиском последнего столбца. Он начинается с последнего столбца в строке, затем идет влево, пока в столбце не будет найдена последняя непустая ячейка. Columns.Count возвращает общее количество столбцов на листе. Итак, мы начинаем с последнего столбца и идем налево.

Аргумент для метода End указывает, в каком направлении идти. Возможные варианты: xlDown, xlUp, xlToLeft, xlToRight.

Плюсы Range.End

• Range.End прост в использовании и понимании, так как он работает так же, как сочетания клавиш Ctrl+Arrow.
• Может использоваться для поиска первой пустой ячейки или последней непустой ячейки в одной строке или столбце.

Минусы Range.End

• Range.End работает только с одной строкой или столбцом. При наличии диапазона данных, содержащего пробелы в последней строке или столбце, может быть трудно определить, в какой строке или столбце выполнять метод.
• Если вы хотите найти последнюю используемую ячейку, то вы должны оценить по крайней мере два оператора. Один, чтобы найти последнюю строку и один, чтобы найти последний столбец. Затем их можно объединить для ссылки на последнюю ячейку.

Вот справочные статьи для Range.End

#2 – The Range.Find() Method

Метод Range.Find — я предпочитаю этот способ, чтобы найти последнюю строку, столбец или ячейку. Он самый универсальный, но и самый страшный.

У Range.Find много аргументов, но пусть это вас не пугает. Когда вы знаете, что они делают, вы можете использовать Range.Find для многих вещей в VBA.

Range.Find — это в основном способ программирования меню «Find» в Excel. Он делает то же самое, и большинство аргументов Range.Find являются опциями в меню Find.

Пример кода Range.Find

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

Метод Range.Find. Пояснения

Метод Find ищет первую непустую ячейку («*»). Звездочка представляет собой символ подстановки, который ищет любой текст или числа в ячейке.

Начиная с ячейки A1, он перемещается назад (xlPrevious) и фактически начинает поиск в самой последней ячейке на листе. Затем он перемещается справа налево (xlByRows) и проходит по каждой строке, пока не найдет непустую ячейку. При обнаружении непустого он останавливается и возвращает номер строки.

Вот подробное объяснение каждого аргумента.

• What:=”*” — звездочка — это символ подстановки, который находит любой текст или число в ячейке. Это в основном то же самое, что и поиск непустой ячейки.
• After:=Range(“A1”)— начать поиск после ячейки А1, первой ячейки на листе. Это означает, что A1 не будет искать. Поиск начнется после A1, и следующая ячейка, которую он ищет, зависит от SearchOrder и SearchDirection. Этот аргумент можно изменить, чтобы он начинался в другой ячейке, просто помните, что поиск фактически начинается в ячейке после указанной.
• LookAt: = xlPart — это будет смотреть на любую часть текста внутри ячейки. Другой вариант — xlWhole, который будет пытаться соответствовать всему содержимому ячейки.
• LookIn: = xlFormulas — Это заставляет Find искать в формулах, и это важный аргумент. Другой вариант — xlValues, который будет искать только значения. Если у вас есть формулы, которые возвращают пробелы (= IF (A2> 5, «Ok», «»), то вы можете считать это непустой ячейкой. При указании LookIn в качестве xlFormulas эта формула будет считаться непустой, даже если возвращаемое значение пустое.
• SearchOrder: = xlByRows — это говорит Find, чтобы искать через каждую целую строку прежде, чем перейти к следующей. Направление поиска слева направо или справа налево зависит от аргумента SearchDirection. Другой вариант здесь — xlByColumns, который используется при поиске последнего столбца.
• SearchDirection: = xlPrevious — указывает направление поиска. xlPrevious означает, что он будет искать справа налево или снизу вверх. Другой вариант — xlNext, который перемещается в противоположном направлении.
• MatchCase: = False — это говорит Find, чтобы не рассматривать заглавные или строчные буквы. Если установить значение True, это поможет. Этот аргумент не является необходимым для этого сценария.

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

Плюсы Range.Find

• Find ищет во всем диапазоне последнюю непустую строку или столбец. Он НЕ ограничен одной строкой или столбцом.
• Последняя строка в наборе данных может содержать пробелы, и Range.Find все равно найдет последнюю строку.
• Аргументы могут использоваться для поиска в разных направлениях и для определенных значений, а не только пустых ячеек.

Минусы Range.Find

• Это ужасно. Метод содержит 9 аргументов. Хотя требуется только один из этих аргументов (Что), вы должны привыкнуть использовать хотя бы первые 7 аргументов. В противном случае метод Range.Find по умолчанию будет использовать ваши последние использованные настройки в окне поиска. Это важно. Если вы не укажете необязательные аргументы для LookAt, LookIn и SearchOrder, тогда метод Find будет использовать те параметры, которые вы использовали последними в окне поиска Excel.
• Нахождение последней ячейки требует двух утверждений. Один, чтобы найти последний ряд и один, чтобы найти последний столбец. Затем вы должны объединить их, чтобы найти последнюю ячейку.

Macro Recorder выручит!

Range.Find — все еще мой любимый метод для нахождения последней ячейки из-за ее универсальности. Но нужно много напечатать и запомнить. К счастью, вам это не нужно.

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

1. Запустить макро рекордер
2. Нажмите Ctrl + F
3. Затем нажмите кнопку «Найти далее»

Код для метода Find со всеми аргументами будет сгенерирован устройством записи макросов.

Используйте пользовательскую функцию для метода Find

Вы также можете использовать пользовательскую функцию (UDF) для метода поиска. Последняя функция Ron de Bruin — прекрасный пример. Вы можете скопировать эту функцию в любой проект или модуль кода VBA и использовать ее для возврата последней строки, столбца или ячейки.

У меня также есть аналогичная функция в примере рабочей книги. Моя функция просто имеет дополнительные аргументы для ссылки на лист и диапазон для поиска.

Вот справочные статьи для Range.Find

• MSDN Help Article for Range.Find Method

#3 – Range.SpecialCells (xlCellTypeLastCell)

Метод SpecialCells делает то же самое, что и нажатие сочетания клавиш Ctrl + End, и выбирает последнюю использованную ячейку на листе.

Пример кода SpecialCells (xlCellTypeLastCell)

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

Часто вы будете нажимать Ctrl + End на клавиатуре и попадете в какую-нибудь ячейку вниз в конце листа, который определенно не используется. Это может произойти по ряду причин. Одной из распространенных причин является то, что свойства форматирования для этой ячейки были изменены. Простое изменение размера шрифта или цвета заливки ячейки помечает ее как использованную ячейку.

Плюсы Range.SpecialCells

• Вы можете использовать этот метод, чтобы найти «используемые» строки и столбцы в конце листа и удалить их. Сравнение результата Range.SpecialCells с результатом Range.Find для непробелов может позволить вам быстро определить, существуют ли какие-либо неиспользуемые строки или столбцы на листе.
• Удаление неиспользуемых строк / столбцов может уменьшить размер файла и увеличить полосу прокрутки.

Минусы Range.SpecialCells

• Excel только сбрасывает последнюю ячейку при сохранении книги. Поэтому, если пользователь или макрос удаляет содержимое некоторых ячеек, этот метод не найдет истинную последнюю ячейку, пока файл не будет сохранен.
• Он находит последнюю использованную ячейку, а НЕ последнюю непустую ячейку.

Другие методы поиска последней ячейки

Что ж, это должно охватывать основы поиска последней использованной или непустой ячейки на листе. Если ваш лист содержит объекты (таблицы, диаграммы, сводные таблицы, слайсеры и т. Д.), Вам может потребоваться использовать другие методы для поиска последней ячейки. Я объясню эти методы в отдельном посте.

У меня также есть статья о том, как найти ПЕРВУЮ ячейку в листе.

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

Источник