Выделить последнюю строку excel vba
Обновлено: 05.07.2024
Как определить последнюю строку на листе Excel, включая некоторые пустые строки посередине?
С помощью этой функции:
функция возвращает 5, и мне нужно 13. Есть идеи, как это сделать?
вы действительно близки. Попробуйте использовать большой номер строки с End(xlUp)
лучший способ получить номер последней строки используется:
получить номер последнего столбца:
надеюсь, это поможет.
Я использую следующий:
Он найдет последнюю строку в определенном столбце. Если вы хотите последнюю использованную строку любой затем:
короче. Безопасный. Быстрый. Вернет последнюю непустую строку, даже если есть пустые строки поверх листа или где-либо еще. Работает также для пустого листа (Excel reports 1 использовал строку на пустом листе, поэтому выражение будет 1). Протестировано и работает на Excel 2002 и Excel 2010.
проблема заключается в "As string" в вашей функции. Замените его на "как двойной "или" как долго", чтобы он работал. Таким образом, он даже работает, если последняя строка больше, чем "большое число", предложенное в принятом ответе.
Так что это должно работать
помимо всех упомянутых, есть несколько других способов найти последнюю строку или столбец на листе или в указанном диапазоне.
Примечание: пожалуйста, используйте только один из вышеперечисленных методов, поскольку он оправдывает ваше заявление о проблеме.
Пожалуйста, обратите внимание на то, что метод Find не видит форматирование ячейки но только данные, поэтому ищите xlCellTypeLastCell, если важны только данные, а не форматирование. Кроме того,слился ячейки (которых следует избегать) могут дать вам неожиданные результаты поскольку он даст вам номер строки первой ячейки, а не последней ячейки в Объединенных ячейках.
ActiveSheet можно заменить на WorkSheets(1) или WorkSheets("name here")
Если лист содержит неиспользуемую область сверху, UsedRange.Rows.Count не является максимальной строкой.
это правильный номер строки max.
вот мой код, чтобы найти следующую пустую строку, например, в первой строке "A". Чтобы использовать его для любой другой строки, просто измените ячейки (i,2 или 3 или 4 и так далее)
Функция записи макросов Excel используется не столько для создания хорошего кода, сколько для поиска названий необходимых объектов, методов и свойств. Например, при записи операции копирования и вставки можно получить код:
Sub Макрос()
Range( " A1 " ).Select
Selection.Copy
Range( " B1 " ).Select
ActiveSheet.Paste
End Sub
Обратите внимание, что данная программа выделяет ячейки. Однако в VBA для работы с объектом не обязательно его выделять. Данную процедуру можно заменить значительно более простой — применить метод Сору, который использует аргумент, представляющий адрес места вставки копируемого диапазона.
Sub CopyRange()
Range( " А1 " ).Copy Range( " В1 " )
End Sub
Предполагается, что рабочий лист является активным и операция выполняется на активном рабочем листе. Чтобы скопировать диапазон на другой рабочий лист или в другую книгу, необходимо задать ссылку:
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, который соответствует прямоугольнику ячеек вокруг заданной ячейки:
Sub CopyCurrentRegion2()
Range( " A1 " ).CurrentRegion.Copy Sheets( " Лист2 " ).Range( " A1 " )
End Sub
Метод End имеет один аргумент, определяющий направление, в котором увеличивается выделение ячеек. Следующий оператор выделяет диапазон от активной ячейки до последней непустой ячейки внизу:
Range (ActiveCell, ActiveCell.End(xlDown)).Select
Три остальные константы имитируют комбинации клавиш при выделении в других направлениях: xlUp (вверх), xlToLeft (влево) и xlToRight (вправо).
В прилагаемом Excel-файле определено несколько распространенных типов выделения ячеек (см. рис. 1). Код любопытен тем, что является также примером создания контекстного меню.
Запрос значения ячейки
Следующая процедура запрашивает значение у пользователя и вставляет его в ячейку А1:
Sub GetValuel()
Range( " A1 " ).Value = InputBox( " Введите значение " )
End Sub
Однако при выполнении этой процедуры возникает проблема. Если пользователь щелкнет на кнопке Отмена в окне ввода данных, то процедура удалит данные, которые находились в текущей ячейке. Модифицированная версия процедуры адекватно реагирует на щелчок на кнопке Отмена и не выполняет при этом никаких действий:
Sub GetValue2()
Dim UserEntry As Variant
UserEntry = InputBox( " Введите значение " )
If UserEntry <> " " Then Range( " A1 " ).Value = UserEntry
End Sub
Во многих случаях следует проверить правильность данных, введенных пользователем. Например, необходимо обеспечить введение только чисел в диапазоне от 1 до 12 (рис. 2). Это можно сделать при помощи процедуры GetValue3(), код которой приведен в Модуле1 приложенного Excel-файла. Некорректные данные игнорируются, и окно запроса значения отображается снова. Этот цикл будет повторяться, пока пользователь не введет правильное значение или не щелкнет на кнопке Отмена.
Рис. 2. Проверка данных, введенных пользователем
Ввод значения в следующую пустую ячейку
Если требуется ввести значение в следующую пустую ячейку столбца или строки, используйте код (рис. 3):
Sub GetData()
Dim NextRow As Long
Dim Entry1 As String, Entry2 As String
Do
' Определение следующей пустой строки
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
' Запрос данных
Entry1 = InputBox( " Введите имя " )
If Entry1 = " " Then Exit Sub
Entry2 = InputBox( " Введите сумму " )
If Entry2 = " " Then Exit Sub
' Запись данных
Cells(NextRow, 1) = Entry1
Cells(NextRow, 2) = Entry2
Loop
End Sub
Рис. 3. Макрос вставляет данные в следующую пустую строку рабочего листа
Это бесконечный цикл. Для выхода из него (щелкните на кнопке Cancel) использовались операторы Exit Sub. Обратите внимание строку, в который определяется значение переменной NextRow. Если вам трудно ее понять, проанализируйте содержимое ячейки: перейдите в последнюю ячейку столбца А и нажмите <End> и <↑>. После этого будет выделена последняя непустая ячейка в столбце А. Свойство Row возвращает номер этой строки; чтобы получить расположенную под ней строку (следующую пустую строку), к этому номеру прибавляется 1.
Приостановка работы макроса для определения диапазона пользователем
В некоторых ситуациях макрос должен взаимодействовать с пользователем. Например, можно создать макрос, который приостанавливается, когда пользователь указывает диапазон ячеек. Для этого воспользуйтесь функцией Excel InputBox. Не путайте метод Excel InputBox с функцией VBA InputBox. Несмотря на идентичность названий, это далеко не одно и то же.
Процедура, представленная ниже, демонстрирует, как приостановить макрос и разрешить пользователю выбрать ячейку. Затем автоматически формула вставляется в каждую ячейку выделенного диапазона.
Sub GetUserRange()
Dim UserRange As Range
Prompt = " Выберите диапазон для случайных чисел. "
Title = " Выбор диапазона "
' Отображение поля ввода
On Error Resume Next
Set UserRange = Application.InputBox( _
Prompt:=Prompt, _
Title:=Title, _
Default:=ActiveCell.Address, _
Type:=8) ' Выделение диапазона
On Error GoTo 0
' Отменено ли отображение поля ввода?
If UserRange Is Nothing Then
MsgBox " Отменено. "
Else
UserRange.Formula = " =RAND() "
End If
End Sub
Окно ввода данных показано на рис. 4. Важный момент в этой процедуре – определение аргумента Туре равным 8 (в этом случае InputBox вернет диапазон; подробнее см. Application.InputBox Method).
Рис. 4. Использование окна ввода данных с целью приостановки выполнения макроса
Обязательно проверьте, включено ли обновление экрана при использовании метода InputBox для выделения диапазона. Если обновление экрана отключено, вы не сможете выделить рабочий лист. Чтобы проконтролировать обновление экрана, в процессе выполнения макроса используйте свойство ScreenUpdating объекта Application.
Подсчет выделенных ячеек
Если активный лист содержит диапазон data, то следующий оператор присваивает количество ячеек в диапазоне data переменной с названием CellCount:
CellCount = Range( " data " ).Count
Вы можете также определить, сколько строк или столбцов содержится в диапазоне. Следующее выражение вычисляет количество столбцов в выделенном диапазоне:
Следующий оператор пересчитывает количество строк в диапазоне с названием data и присваивает это количество переменной RowCount.
RowCount = Range( " data " ).Rows.Count
Просмотр выделенного диапазона
Вы можете столкнуться с трудностями при создании макроса, который оценивает каждую ячейку в диапазоне и выполняет операцию, определенную заданному критерию. Если выделен целый столбец или строка, то работа макроса может занять много времени. Процедура ColorNegative устанавливает красный цвет для ячеек, которые содержат отрицательные значения. Цвет фона для других ячеек не определяется. Код процедуры можно найти в Модуле4 приложенного Excel-файла.
Усовершенствованная процедура ColorNegative2, создает объектную переменную WorkRange типа Range, которая представляет собой пересечение выделенного диапазона и диапазона рабочего листа (рис. 5). Если выделить столбец F (1048576 ячеек), то его пересечение с рабочим диапазоном В2:I16) даст область F2:F16, которая намного меньше исходного выделенного диапазона. Время, затрачиваемое на обработку 15 ячеек, намного меньше времени, уходящего на обработку миллиона ячеек.
Рис. 5. В результате пересечения используемого диапазона и выделенного диапазона рабочего листа уменьшается количество обрабатываемых ячеек
И всё же процедура ColorNegative2 недостаточно эффективна, поскольку обрабатывает все ячейки в диапазоне. Поэтому предлагается процедура ColorNegative3. В ней используется метод SpecialCells, с помощью которого генерируются два поднабора выделенной области: один поднабор (ConstantCells) включает ячейки, которые содержат исключительно числовые константы; второй поднабор (FormulaCells) включает ячейки, содержащие числовые формулы. Обработка ячеек в этих поднаборах осуществляется с помощью двух конструкций For Each-Next. Благодаря тому, что исключается обработка пустых и нетекстовых ячеек, скорость выполнения макроса существенно увеличивается.
Sub ColorNegative3()
' Окрашивание ячеек с отрицательными значениями в красный цвет
Dim FormulaCells As Range, ConstantCells As Range
Dim cell As Range
If TypeName(Selection) <> " Range " Then Exit Sub
Application.ScreenUpdating = False
' Создание поднаборов исходной выделенной области
On Error Resume Next
Set FormulaCells = Selection.SpecialCells(xlFormulas, xlNumbers)
Set ConstantCells = Selection.SpecialCells(xlConstants, xlNumbers)
On Error GoTo 0
' Обработка ячеек с формулами
If Not FormulaCells Is Nothing Then
For Each cell In FormulaCells
If cell.Value < 0 Then
cell.Interior.Color = RGB(255, 0, 0)
Else
cell.Interior.Color = xlNone
End If
Next cell
End If
' Обработка ячеек с константами
If Not ConstantCells Is Nothing Then
For Each cell In ConstantCells
If cell.Value < 0 Then
cell.Interior.Color = RGB(255, 0, 0)
Else
cell.Interior.Color = xlNone
End If
Next cell
End If
End Sub
Оператор On Error необходим, поскольку метод SpecialCells генерирует ошибку, если не находит в диапазоне ячеек указанного типа.
Удаление всех пустых строк
Следующая процедура удаляет все пустые строки в активном рабочем листе. Она достаточно эффективна, так как не проверяет все без исключения строки, а просматривает только строки в так называемом «используемом диапазоне», определяемом с помощью свойства UsedRange объекта Worksheet.
Первый шаг — определить последнюю используемую строку и присвоить этот номер строки переменной LastRow. Это не так просто, как можно ожидать, поскольку текущий диапазон необязательно начинается со строки 1. Следовательно, значение LastRow вычисляется таким образом: к найденному количеству строк используемого диапазона прибавляется номер первой строки текущего диапазона и вычитается 1.
В процедуре применена функция Excel СЧЁТЗ, определяющая, является ли строка пустой. Если данная функция для конкретной строки возвращает 0, то эта строка пустая. Обратите внимание, что процедура просматривает строки снизу вверх и использует отрицательное значение шага в цикле For-Next. Это необходимо, поскольку при удалении все последующие строки перемещаются «вверх» в рабочем листе. Если бы в цикле просмотр выполнялся сверху вниз, то значение счетчика цикла после удаления строки оказалось бы неправильным.
Дублирование строк
Пример, рассматриваемый в этом разделе, демонстрирует использование возможностей VBA для создания дубликатов строк. На рис. 6 показан пример рабочего листа, используемого организаторами лотереи. В столбце А вводится имя. В столбце В содержится количество лотерейных билетов, приобретенных одним покупателем. В столбце С находится случайное число сгенерированное с помощью функции СЛЧИС. Победитель определяется путем сортировки данных в третьем столбце (выигрыш соответствует наибольшему случайному числу).
Рис. 6. Дублирование строк на основе значений в столбце В
А теперь нужно продублировать строки, в результате чего количество строк для каждого участника лотереи будут соответствовать количеству купленных им билетов. Например, если Барбара приобрела два билета, для нее создаются две строки. Ниже показана процедура, выполняющая вставку новых строк.
Объектная переменная cell была инициализирована ячейкой В2, первой ячейкой, в которой находится числовая величина. Вставка новых строк осуществляется в цикле, а их копирование происходит с помощью метода FillDown. Значение переменной cell увеличивается на единицу, после чего выбирается следующий участник лотереи, Цикл выполняется до тех пор, пока не встретится пустая ячейка. На рис. 7 показан рабочий лист после выполнения этой процедуры.
Рис. 7. В соответствии со значением в столбце В добавлены новые строки
Определение диапазона, находящегося в другом диапазоне
Функция InRange имеет два аргумента, оба — объекты Range. Функция возвращает значение True (Истина), если первый диапазон содержится во втором.
Возможно, функция InRange кажется сложнее, чем того требует ситуация, поскольку в коде должна быть реализована проверка принадлежности двух диапазонов одной и той же книге и рабочему листу. Обратите внимание, что в процедуре используется свойство Parent, которое возвращает объект-контейнер заданного объекта. Например, следующее выражение возвращает название листа для объекта rng1:
Следующее выражение возвращает название рабочей книги rng1:
Функция VBA Union возвращает объект Range, который представляет собой объединение двух объектов типа Range. Объединение содержит все ячейки, относящиеся к исходным диапазонам. Если адрес объединения двух диапазонов совпадает с адресом второго диапазона, первый диапазон входит в состав второго диапазона.
Определение типа данных ячейки
В состав Excel входит ряд встроенных функций, которые могут помочь определить тип данных, содержащихся в ячейке. Это функции ЕНЕТЕКСТ, ЕЛОГИЧ и ЕОШИБКА. Кроме того, VBA поддерживает функции IsEmpty, IsDate и IsNumeric.
Ниже описана функция CellType, которая принимает аргумент-диапазон и возвращает строку, описывающую тип данных левой верхней ячейки этого диапазона (рис. 8). Такую функцию можно использовать в формуле рабочего листа или вызвать из другой процедуры VBA.
Рис. 8. Функция CellType, возвращающая тип данных ячейки
Function CellType(Rng)
' Возвращает тип ячейки, находящейся в левом верхнем углу диапазона
Dim TheCell As Range
Set TheCell = Rng.Range( " A1 " )
Select Case True
Case IsEmpty(TheCell)
CellType = " Пустая "
Case TheCell.NumberFormat = " @ "
CellType = " Текст "
Case Application.IsText(TheCell)
CellType = " Текст "
Case Application.IsLogical(TheCell)
CellType = " Логический "
Case Application.IsErr(TheCell)
CellType = " Ошибка "
Case IsDate(TheCell)
CellType = " Дата "
Case InStr(1, TheCell.Text, " : " ) <> 0
CellType = " Время "
Case IsNumeric(TheCell)
CellType = " Число "
End Select
End Function
Обратите внимание на использование оператора SetTheCell. Функция CellType получает аргумент-диапазон произвольного размера, но этот оператор указывает, что функция оперирует только левой верхней ячейкой диапазона (представленной переменной TheCell).
Очень часто при внесении данных на лист Excel возникает вопрос определения последней заполненной или первой пустой ячейки. Чтобы впоследствии с этой первой пустой ячейки начать заносить данные. В этой теме я опишу несколько способов определения последней заполненной ячейки.
В качестве переменной, которой мы будем присваивать номер последней заполненной строки, у нас во всех примерах будет lLastRow. Объявлять мы её будем как Long . Для экономии памяти можно было бы использовать и тип Integer, но т.к. строк на листе может быть больше 32767(это максимальное допустимое значение переменных типа Integer ) нам понадобиться именно Long , во избежание ошибки. Подробнее про типы переменных можно прочитать в статье Что такое переменная и как правильно её объявить
Одинаковые переменные для всех примеров
Dim lLastRow As Long 'а для lLastCol можно применить тип Integer, 'т.к. столбцов в Excel пока меньше 32767 Dim lLastCol As Long
Способ 1:Определение последней заполненной строки через свойство End
определяя таким способом нам надо знать что:
1 - это номер столбца, последнюю заполненную ячейку в котором мы определяем. В данном случае это столбце №1 или А.
Это самый распространенный метод определения последней строки. Используя его мы можем определить последнюю ячейку только в одном конкретном столбце. Но в большинстве случаев этого достаточно.
Правда, следует знать одну вещь: если у вас заполнены все строки в просматриваемом столбце(или будет заполнена самая последняя ячейка столбца) - то результат будет неверный(ну или не совсем такой, какой ожидали увидеть вы)
Определение последнего столбца через свойство End
lLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
1 - это номер строки, последнюю заполненную ячейку в которой мы определяем.
Способ 2:Данный метод лишен недостатков, присущих второму и третьему способам. Однако есть другой, в определенных ситуациях даже полезный: при таком методе определения игнорируются строки, скрытые фильтром, группировкой или командой Скрыть (Hide) . Т.е. если последняя строка таблицы будет скрыта, то данный метод вернет номер последней видимой заполненной строки, а не последней реально заполненной.
Определение последней заполненной строки через SpecialCells
Определение последнего столбца через SpecialCells
Данный метод не требует указания номера столбца и возвращает максимальную последнюю ячейку(строку - Row либо столбец - Column). Но используя данный метод следует помнить, что не всегда можно получить реальную последнюю заполненную ячейку, т.е. именно ячейку со значением. Если вы где-то ниже занесете данные и сразу удалите их из таблицы, а затем примените такой метод, то lLastRow будет равна значению строки, из которой вы только что удалили значения. Другими словами требует обязательного обновления данных, а этого можно добиться только сохранив и закрыв документ и открыв его снова. Так же, если какая-либо ячейка содержит форматирование(например, заливку), но не содержит никаких значений, то она тоже будет считаться заполненной.
Плюс данный метод определения последней ячейки не будет работать на защищенном листе(Рецензирование -Защитить лист).
Я этот метод использую только для определения в только что созданном документе, в котором только добавляю строки.
Способ 3:
Определение последней строки через UsedRange
Определение последнего столбца через UsedRange
Если хотите получить первую пустую ячейку на листе придется вспомнить математику. Т.к. последнюю заполненную мы определили, то первая пустая - следующая за ней. Т.е. к результату необходимо прибавить 1.
Способ 4:
Определение последней строки и столбца, а так же адрес ячейки методом Find
Этот метод, пожалуй, самый оптимальный в случае, если надо определить последнюю строку/столбец на листе без учета форматов и формул - только по отображаемому значению в ячейке. Например, если на листе большая таблица и последние строки заполнены формулами, возвращающими пустую ячейку(=""), предыдущие варианты вернут строку/столбец ячейки с последней формулой, в то время как данный метод вернет адрес ячейки только в случае, если в ячейке реально отображается какое-то значение. Такой подход часто используется для того, чтобы определить границы данных для последующего анализа заполненных данных, чтобы не захватывать пустые ячейки и не тратить время на их проверку.
Однако данный метод не будет учитывать в просмотре скрытые строки и столбцы. Это следует учитывать при его применении.
небольшой практический код , который поможет вам понять, как использовать полученную переменную:
Sub Get_Last_Cell() Dim lLastRow As Long Dim lLastCol As Long lLastRow = Cells(Rows.Count, 1).End(xlUp).Row MsgBox "Заполненные ячейки в столбце А: " & Range("A1:A" & lLastRow).Address lLastCol = Cells.SpecialCells(xlLastCell).Column MsgBox "Заполненные ячейки в первой строке: " & Range(Cells(1, 1), Cells(1, lLastCol)).Address MsgBox "Адрес последней ячейки диапазона на листе: " & Cells.SpecialCells(xlLastCell).Address End Sub
А такой код выделит диапазон ячеек в столбцах с А по С, определяя последнюю ячейку по столбцу A этого же листа:
Sub Copy_To_Last_Cell() Range("A1:C" & Cells(Rows.Count, 1).End(xlUp).Row).Select End Sub
А вот такой код скопирует ячейку B1 в первую пустую ячейку столбца A этого же листа:
Sub Copy_To_Last_Cell() Range("B1").Copy Cells(Rows.Count, 1).End(xlUp).Offset(1) End Sub
Следующим кодом используем инструмент автозаполнение(протягивание) столбца В на основании значения в ячейке B2 и определяя последнюю ячейку для заполнения на основании столбца А
Sub AutoFill_B() Dim lLastRow As Long lLastRow = Cells(Rows.Count, 1).End(xlUp).Row Range("B2").AutoFill Destination:=Range("B2:B" & lLastRow) End Sub
Важно знать: необходимо помнить, что если ячейка содержит формулу, пусть и возвращающую значение "", Excel не считает её пустой(к слову совершенно справедливо) и включает в просмотр при поиске последней ячейки.
Итог: узнаете, как найти последнюю строку, столбец или ячейку в таблице с использованием трех различных методов на VBA. Используемый метод зависит от макета данных и от того, содержит ли лист пустые ячейки.
Уровень мастерства: средний
Видео: 3 части серии как найти последнюю ячейку с VBA
Видео лучше всего просматривать в полноэкранном режиме HD
Загрузите файл, содержащий код:
Find Last Cell VBA Примеры.xlsm (79.6 KB)
Выбор правильного метода в основном зависит от того, как выглядят ваши данные.
В этой статье я объясняю три различных метода VBA объекта Range, которые мы можем использовать для поиска последней ячейки на листе. Каждый из этих методов имеет плюсы и минусы, а некоторые выглядят страшнее других. 🙂
Но понимание того, как работает каждый метод, поможет вам узнать, когда их использовать и почему.
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
У Range.Find много аргументов, но пусть это вас не пугает. Когда вы знаете, что они делают, вы можете использовать Range.Find для многих вещей в VBA.
Пример кода Range.Find
Ниже приведен код для поиска последней непустой строки.
Метод Range.Find. Пояснения
Метод Find ищет первую непустую ячейку («*»). Звездочка представляет собой символ подстановки, который ищет любой текст или числа в ячейке.
Начиная с ячейки A1, он перемещается назад (xlPrevious) и фактически начинает поиск в самой последней ячейке на листе. Затем он перемещается справа налево (xlByRows) и проходит по каждой строке, пока не найдет непустую ячейку. При обнаружении непустого он останавливается и возвращает номер строки.
Вот подробное объяснение каждого аргумента.
Да, я знаю, что это много, но надеюсь, у вас будет лучшее понимание того, как использовать эти аргументы, чтобы найти что-нибудь на листе.
Плюсы Range.Find
- Find ищет во всем диапазоне последнюю непустую строку или столбец. Он НЕ ограничен одной строкой или столбцом.
- Последняя строка в наборе данных может содержать пробелы, и Range.Find все равно найдет последнюю строку.
- Аргументы могут использоваться для поиска в разных направлениях и для определенных значений, а не только пустых ячеек.
Минусы Range.Find
- Это ужасно. Метод содержит 9 аргументов. Хотя требуется только один из этих аргументов (Что), вы должны привыкнуть использовать хотя бы первые 7 аргументов. В противном случае метод Range.Find по умолчанию будет использовать ваши последние использованные настройки в окне поиска. Это важно. Если вы не укажете необязательные аргументы для LookAt, LookIn и SearchOrder, тогда метод Find будет использовать те параметры, которые вы использовали последними в окне поиска Excel.
- Нахождение последней ячейки требует двух утверждений. Один, чтобы найти последний ряд и один, чтобы найти последний столбец. Затем вы должны объединить их, чтобы найти последнюю ячейку.
Macro Recorder выручит!
Вы можете использовать макро рекордер, чтобы быстро создать код со всеми аргументами.
- Запустить макро рекордер
- Нажмите Ctrl + F
- Затем нажмите кнопку «Найти далее»
Код для метода Find со всеми аргументами будет сгенерирован устройством записи макросов.
Используйте пользовательскую функцию для метода Find
У меня также есть аналогичная функция в примере рабочей книги. Моя функция просто имеет дополнительные аргументы для ссылки на лист и диапазон для поиска.
Вот справочные статьи для Range.Find
Метод SpecialCells делает то же самое, что и нажатие сочетания клавиш Ctrl + End, и выбирает последнюю использованную ячейку на листе.
Пример кода SpecialCells (xlCellTypeLastCell)
На самом деле это самый простой способ найти последнюю использованную ячейку. Однако этот метод находит последнюю использованную ячейку, которая может отличаться от последней непустой ячейки.
Часто вы будете нажимать Ctrl + End на клавиатуре и попадете в какую-нибудь ячейку вниз в конце листа, который определенно не используется. Это может произойти по ряду причин. Одной из распространенных причин является то, что свойства форматирования для этой ячейки были изменены. Простое изменение размера шрифта или цвета заливки ячейки помечает ее как использованную ячейку.
Плюсы Range.SpecialCells
- Вы можете использовать этот метод, чтобы найти «используемые» строки и столбцы в конце листа и удалить их. Сравнение результата Range.SpecialCells с результатом Range.Find для непробелов может позволить вам быстро определить, существуют ли какие-либо неиспользуемые строки или столбцы на листе.
- Удаление неиспользуемых строк / столбцов может уменьшить размер файла и увеличить полосу прокрутки.
Минусы Range.SpecialCells
- Excel только сбрасывает последнюю ячейку при сохранении книги. Поэтому, если пользователь или макрос удаляет содержимое некоторых ячеек, этот метод не найдет истинную последнюю ячейку, пока файл не будет сохранен.
- Он находит последнюю использованную ячейку, а НЕ последнюю непустую ячейку.
Другие методы поиска последней ячейки
Что ж, это должно охватывать основы поиска последней использованной или непустой ячейки на листе. Если ваш лист содержит объекты (таблицы, диаграммы, сводные таблицы, слайсеры и т. Д.), Вам может потребоваться использовать другие методы для поиска последней ячейки. Я объясню эти методы в отдельном посте.
У меня также есть статья о том, как найти ПЕРВУЮ ячейку в листе.
Пожалуйста, оставьте комментарий ниже, если у вас есть какие-либо вопросы, или вы все еще не можете найти последнюю ячейку. Я буду рад помочь!
Читайте также: