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

В этой статье будет проще всего объяснить каждый метод поиска последней строки и столбца в Excel.

1. Найдите последнюю непустую строку в столбце с помощью Range.End

Давайте сначала посмотрим на код. Я объясню это письмом.

Sub getLastUsedRow()

Dim last_row As Integer

last_row = Cells(Rows.Count, 1).End(xlUp).Row ‘This line gets the last row

Debug.Print last_row

End Sub

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

image

Как это работает?

Это похоже на переход к последней строке на листе, а затем нажатие сочетания клавиш CTRL + UP.

Ячейки (Rows.Count, 1): эта часть выбирает ячейку в столбце A. Rows.Count дает 1048576, что обычно является последней строкой в ​​листе Excel.

Cells(1048576, 1)
End (xlUp): End — это метод класса диапазона, который используется для навигации по листам до концов. xlUp — это переменная, указывающая направление.

Вместе эта команда выбирает последнюю строку с данными.

Cells(Rows.Count, 1).End(xlUp)
Row: row возвращает номер строки выбранной ячейки. Следовательно, мы получаем номер строки последней ячейки с данными в столбце A. В нашем примере это 8.

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

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

Если вы хотите выбрать последнюю ячейку в столбце A, просто удалите «.row» с конца и напишите .select.

Sub getLastUsedRow()

Cells(Rows.Count, 1).End(xlUp).Select ‘This line selects the last cell in a column

End Sub

Команда «.Select» выбирает активную ячейку.

Получить столбец адреса последней ячейки

Если вы хотите получить адрес последней ячейки в столбце A, просто удалите «.row» с конца и напишите .address.

Sub getLastUsedRow()

add=Cells(Rows.Count, 1).End(xlUp).address ‘This line selects the last cell in a column

Debug.print add

End Sub

Функция Range.Address * возвращает адрес активной ячейки.

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

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

Sub getLastUsedCol()

Dim last_col As Integer

last_col = Cells(4,Columns.Count).End(xlToLeft).Column ‘This line gets the last column

Debug.Print last_col

End Sub

image

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

Как это работает?

Что ж, механизм такой же, как поиск последней ячейки с данными в столбце.

Мы только что использовали ключевые слова, относящиеся к столбцам.

Выбор набора данных в Excel с помощью VBA

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

Используя это, мы можем легко выбрать таблицу или набор данных. После выбора набора данных или таблицы мы можем выполнить с ними несколько операций, таких как копирование и вставка, форматирование, удаление и т. Д.

Здесь у нас есть набор данных. Эти данные могут расширяться вниз. Фиксируется только начальная ячейка — B4. Последняя строка и столбец не фиксируются. Нам нужно динамически выделить всю таблицу с помощью vba.

image

Код VBA для выбора таблицы с пустыми ячейками

Sub select_table()

Dim last_row, last_col As Long

'Get last row

last_row = Cells(Rows.Count, 2).End(xlUp).Row

'Get last column

last_col = Cells(4, Columns.Count).End(xlToLeft).Column

'Select entire table

Range(Cells(4, 2), Cells(last_row, last_col)).Select

End Sub

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

image

Преимущества этого метода:

  1. Это просто. Мы буквально написали всего одну строку, чтобы получить последнюю строку с данными.

Это облегчает задачу.

  1. Быстрый. Меньше строк кода, меньше времени.

  2. Легко понять.

  3. Отлично работает, если у вас неуклюжая таблица данных с фиксированной начальной точкой.

Минусы метода Range.End:

  1. Отправную точку нужно знать.

  2. Вы можете получить только последнюю непустую ячейку в известной строке или столбце. Когда ваша отправная точка не зафиксирована, это будет бесполезно. Что вряд ли произойдет.

2. Найти последнюю строку с помощью функции Find ()

Давайте сначала посмотрим на код.

Sub last_row()

lastRow = ActiveSheet.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row



Debug.Print lastRow

End Sub

image

Как вы можете видеть на изображении, этот код точно возвращает последнюю строку.

Как это работает?

Здесь мы используем функцию поиска, чтобы найти любую ячейку, которая содержит что-либо, используя оператор подстановки «*». Звездочка используется для поиска чего угодно, текста или числа.

We set search order by rows (searchorder:=xlByRows). We also tell excel vba the direction of search as xlPrevious (searchdirection:=xlPrevious). It makes find function to search from end of the sheet, row wise.

Once it find a cell that contains anything, it stops. We use the Range.Row method to fetch last row from active cell.

Преимущества функции Find для получения последней ячейки с данными:

  1. Вам не нужно знать отправную точку. Это просто доставит вам последний ряд. . Он может быть общим и может использоваться для поиска последней ячейки с данными на любом листе без каких-либо изменений.

  2. Может использоваться для поиска любого последнего экземпляра определенного текста или числа на листе.

Минусы функции Find ():

  1. Это некрасиво. Слишком много доводов.

  2. Это медленно.

  3. Невозможно использовать для получения последнего непустого столбца. Технически можно. Но это становится слишком медленно.

3. Использование функции SpecialCells для получения последней строки

Функция SpecialCells с аргументом xlCellTypeLastCell возвращает последнюю использованную ячейку. Давайте сначала посмотрим код

Sub spl_last_row_()

lastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

Debug.Print lastRow

End Sub

image

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

Как это работает?

Это vba-эквивалент сочетания клавиш CTRL + End в Excel. Он выбирает последнюю использованную ячейку. Если записать макрос, нажав CTRL + End, вы получите этот код.

Sub Macro1()

'

' Macro1 Macro

'

'

ActiveCell.SpecialCells(xlLastCell).Select

End Sub

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

Примечание: * Как я сказал выше, этот метод вернет последнюю использованную ячейку, а не последнюю ячейку с данными. Если вы удалите данные в последней ячейке, приведенный выше код vba по-прежнему будет возвращать ту же ссылку на ячейки, поскольку это была «последняя использованная ячейка». Вам нужно сначала сохранить документ, чтобы получить последнюю ячейку с данными с помощью этого метода.

Статьи по теме:

link: / files-workbook-and-worksheets-in-vba-delete-sheet-without-confirm-prompts-using-vba-in-microsoft-excel [Удалять листы без запросов подтверждения с помощью VBA в Microsoft Excel]

link: / files-workbook-and-worksheets-in-vba-add-and-save-new-workbook-using-vba-in-microsoft-excel [Добавить и сохранить новую книгу с помощью VBA в Microsoft Excel 2016]

link: / menus-toolbars-status-bar-in-vba-display-a-message-on-the-statusbar-using-vba-in-microsoft-excel [Показать сообщение в строке состояния Excel VBA]

link: / general-themes-in-vba-turn-off-warning-messages-using-vba-in-microsoft-excel [Отключить предупреждающие сообщения с помощью VBA в Microsoft Excel 2016]

Популярные статьи:

link: / формулы-и-функции-введение-функции vlookup [Функция ВПР в Excel]

link: / tips-countif-in-microsoft-excel [СЧЁТЕСЛИ в Excel 2016]

link: / excel-formula-and-function-excel-sumif-function [Как использовать функцию СУММЕСЛИ в Excel]