Progress28.ru

IT Новости
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Excel vba последняя заполненная ячейка

Excel vba последняя заполненная ячейка

Вариант II.
Для поиска последней заполненной ячейки можно воспользоваться свойством UsedRange об’екта Worksheet

Пример определения номера строки и столбца последней заполненной ячейки.
iRow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count — 1
iClm = ActiveSheet.UsedRange.Column + ActiveSheet.UsedRange.Columns.Count — 1With ActiveSheet.UsedRange
iRow = .Row + .Rows.Count — 1
iClm = .Column + .Columns.Count — 1
End WithSet iList = ActiveSheet
iRow = iList.UsedRange.Row + iList.UsedRange.Rows.Count — 1
iClm = iList.UsedRange.Column + iList.UsedRange.Columns.Count — 1Set >iRow = iDiapazon.Row + iDiapazon.Rows.Count — 1
iClm = iDiapazon.Column + iDiapazon.Columns.Count — 1Комментарий :
Так как свойство UsedRange принадлежит об’екту Worksheet, то использование ссылки на этот об’ект обязательно.

Примечание :
Все вышеприведённые примеры определяют последнюю ячейку в активном рабочем листе. Естественно, что мы можем ссылаться и на другие рабочие листы , используя при этом их имя, номер (индекс) или имя в среде VBA ()

Вариант III.
Для определения количества заполненных ячеек в смежном диапазоне можно воспользоваться свойством CurrentRegion об’екта Range

Пример определения количества строк и столбцов в смежном с ячейкой диапазоне, а также адрес этого диапазона.
iRow = Columns(«A»).CurrentRegion.Rows.Count
iClm = Rows(1).CurrentRegion.Columns.Count
iAddress = Range(«A1»).CurrentRegion.AddressПримечание :
Особенностью свойства CurrentRegion является то, что он возвращает весь диапазон, но только состоящий из смежных ячеек.

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

Предупреждение :
Если рабочий лист защищён, то использование этого варианта приведёт к возникновению ошибки, которую можно избежать, если воспользоваться этим советом.

Вариант IV.
Для определения номера строки и столбца последней заполненной ячейки можно использовать функцию ПОЛУЧИТЬ.ДОКУМЕНТ (макроязык Excel4.0)

Пример определения номера строки и столбца последней заполненной ячейки в активном рабочем листе.
iRow = ExecuteExcel4Macro(«GET.DOCUMENT(10)»)
iClm = ExecuteExcel4Macro(«GET.DOCUMENT(12)»)
Пример определения номера строки и столбца последней заполненной ячейки в активной рабочей книге и конкретном рабочем листе.
iRow = ExecuteExcel4Macro(«GET.DOCUMENT(10,»»Лист1″»)»)
iClm = ExecuteExcel4Macro(«GET.DOCUMENT(12,»»Лист1″»)»)
Пример определения номера строки и столбца последней заполненной ячейки в конкретной рабочей книге и листе.
iRow = ExecuteExcel4Macro(«GET.DOCUMENT(10,»»[ОткрытаяКнига.xls]Лист1″»)»)
iClm = ExecuteExcel4Macro(«GET.DOCUMENT(12,»»[ОткрытаяКнига.xls]Лист1″»)»)
Вариант V.
Для определения номера последней заполненной ячейки в конкретной строке или столбце, а также для определения последней заполненной ячейки можно использовать метод Find

Пример определения номера последней заполненной ячейки в конкретной строке и столбце.
On Error Resume Next

iRow = Columns(«C»).Find(What:=»*», LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
iClm = Rows(10).Find(What:=»*», LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column

Пример определения номера строки и столбца последней заполненной ячейки.
On Error Resume Next

iRow = Cells.Find(What:=»*», LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
iClm = Cells.Find(What:=»*», LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).ColumnПримечание :
Вышеупомянутый синтаксис может вызвать ошибку, если указанный диапазон не содержит данных. Для того, чтобы этого избежать, во всех примерах использован «режим отложенной ошибки» On Error Resume Next
Однако, можно использовать и другой синтаксис, например :Set iLastCell = Cells.Find(What:=»*», LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByRows)

If Not iLastCell Is Nothing Then
iRow = iLastCell.Row
iClm = iLastCell.Column
End IfSet iLastCell = Cells.Find(What:=»*», LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByRows)

If TypeName(iLastCell) <> «Nothing» Then
iRow = iLastCell.Row
iClm = iLastCell.Column
End IfКомментарий :
важно Этот вариант будет корректно работать только при условии, что ячейки не содержат формул, которые возвращают пустую строку «» или апостроф ‘

Вариант VI.
Для определения номера последней заполненной ячейки в конкретной строке или столбце можно воспользоваться свойством End об’екта Range и специальными константами xlToRight , xlDown

Пример определения номера последней заполненной ячейки в конкретной строке и столбце.
iRow = Columns(3).End(xlDown).Row
iRow = Columns(«C»).End(xlDown).Row
iClm = Rows(10).End(xlToRight).ColumnКомментарий :
важно Этот вариант будет корректно работать только при условии, что данные в строке, или столбце начинаются с самой первой ячейки и не содержат пустых ячеек.

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

Пример определения номера последней заполненной ячейки в конкретной строке и столбце.
iRow = Cells(65536, 3).End(xlUp).Row
iRow = Cells(65536, «C»).End(xlUp).Row
iClm = Cells(10, 256).End(xlToLeft).Column

iRow = Cells(Rows.Count, 3).End(xlUp).Row
iRow = Cells(Rows.Count, «C»).End(xlUp).Row
iClm = Cells(10, Columns.Count).End(xlToLeft).ColumnВ зависимости от Вашего кода можно применять различные подварианты, например :iRow = Columns(3).Rows(65536).End(xlUp).Row
iRow = Columns(«C»).Rows(65536).End(xlUp).RowКомментарий :
важно Этот вариант будет работать при любых условиях, так как маловероятно, что последней заполненной ячейкой окажется именно последняя ячейка в столбце, однако и эту вероятность можно учесть :
Const iMaxRow = 65536 ‘ 97, 2000

Читать еще:  Формула текст в excel

With Worksheets(1).Cells(iMaxRow, 1)
If IsEmpty(.Value) = True Then
iRow = .End(xlUp).Row
Else
iRow = iMaxRow
End If
End With
Вариант VIII.
Некоторые люди используют для определения последней заполненной строки в определённом столбце стандартную функцию рабочего листа СЧЁТЗ

Пример определения номера последней заполненной ячейки в конкретном столбце.
iRow = Application.CountA(Columns(3))
iRow = Application.CountA(Columns(«C»))
iRow = WorksheetFunction.CountA(Columns(3))
iRow = Excel.Application.CountA(Columns(«C»))
iRow = Excel.WorksheetFunction.CountA(Columns(3))
iRow = Application.WorksheetFunction.CountA(Columns(«C»))Комментарий :
важно Так как функция СЧЁТЗ считает количество непустых ячеек, то этот вариант будет корректно работать только при условии, что данные в столбце начинаются с самой первой ячейки и не содержат пустых ячеек.

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

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

Дополнение :
Для того чтобы Ваш макрос стал более понятным, лучше использовать имена переменных, несущих смысловую нагрузку и поэтому именовать номер строки последней ячейки не iRow , а iRowLast , а номер столбца не iClm , а iClmLast .

Тогда цикл по строкам будет выглядеть примерно так :

Как определить последнюю ячейку на листе через VBA?

Очень часто при внесении данных на лист Excel возникает вопрос определения последней заполненной или первой пустой ячейки. Чтобы впоследствии с этой первой пустой ячейки начать заносить данные. В этой теме я опишу несколько способов определения последней заполненной ячейки.

В качестве переменной, которой мы будем присваивать номер последней заполненной строки, у нас во всех примерах будет lLastRow. Объявлять мы её будем как Long . Для экономии памяти можно было бы использовать и тип Integer, но т.к. строк на листе может быть больше 32767(это максимальное допустимое значение переменных типа Integer ) нам понадобиться именно Long , во избежание ошибки. Подробнее про типы переменных можно прочитать в статье Что такое переменная и как правильно её объявить

Одинаковые переменные для всех примеров

Dim lLastRow As Long ‘а для lLastCol можно применить тип Integer, ‘т.к. столбцов в Excel пока меньше 32767 Dim lLastCol As Long

определяя таким способом нам надо знать что:
1 — это номер столбца, последнюю заполненную ячейку в котором мы определяем. В данном случае это столбце №1 или А.
Это самый распространенный метод определения последней строки. Используя его мы можем определить последнюю ячейку только в одном конкретном столбце. Но в большинстве случаев этого достаточно.

Правда, следует знать одну вещь: если у вас заполнены все строки в просматриваемом столбце(или будет заполнена самая последняя ячейка столбца) — то результат будет неверный(ну или не совсем такой, какой ожидали увидеть вы)
Определение последнего столбца через свойство End

lLastCol = Cells(1, Columns.Count).End(xlToLeft).Column

1 — это номер строки, последнюю заполненную ячейку в которой мы определяем.

Данный метод лишен недостатков, присущих второму и третьему способам. Однако есть другой, в определенных ситуациях даже полезный: при таком методе определения игнорируются строки, скрытые фильтром, группировкой или командой Скрыть (Hide) . Т.е. если последняя строка таблицы будет скрыта, то данный метод вернет номер последней видимой заполненной строки, а не последней реально заполненной.

Определение последнего столбца через SpecialCells

Данный метод не требует указания номера столбца и возвращает максимальную последнюю ячейку(строку — Row либо столбец — Column). Но используя данный метод следует помнить, что не всегда можно получить реальную последнюю заполненную ячейку, т.е. именно ячейку со значением. Если вы где-то ниже занесете данные и сразу удалите их из таблицы, а затем примените такой метод, то lLastRow будет равна значению строки, из которой вы только что удалили значения. Другими словами требует обязательного обновления данных, а этого можно добиться только сохранив и закрыв документ и открыв его снова. Так же, если какая-либо ячейка содержит форматирование(например, заливку), но не содержит никаких значений, то она тоже будет считаться заполненной.
Плюс данный метод определения последней ячейки не будет работать на защищенном листе(Рецензирование -Защитить лист).

Я этот метод использую только для определения в только что созданном документе, в котором только добавляю строки.

Способ 3:
Определение последней строки через UsedRange

lLastRow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count — 1

Определение последнего столбца через UsedRange

lLastCol = ActiveSheet.UsedRange.Column + ActiveSheet.UsedRange.Columns.Count — 1

  • ActiveSheet.UsedRange.Row — этой строкой мы определяем первую ячейку, с которой начинаются данные на листе. Важно понимать для чего это — если у вас первые строк 5 не заполнены ничем, то данная строка вернет 6(т.е. номер первой строки с данными). Если же все строки заполнены — то вернет 1.
  • ActiveSheet.UsedRange.Rows.Count — определяем кол-во строк, входящих в весь диапазон данных на листе.
    Т.е. получается: первая строка данных + кол-во строк с данными — 1. Зачем вычитать единицу? Попробуем посчитать вместе: первая строка: 3. Всего строк: 3. 3 + 3 = 6. Вроде все верно, чего тут непонятного? А теперь выделите на листе три ячейки, начиная с 3-ей. Все верно. Ведь у нас в 3-ей строке уже есть данные. Думаю, остальное уже понятно и без моих пояснений.
  • То же самое и с ActiveSheet.UsedRange.Column, только уже не для строк, а для столбцов.

Обладает всеми недостатками предыдущего метода. . Однако, можно перед определением последней строки/столбца записать строку: With ActiveSheet.UsedRange: End With
Это должно переопределить границы рабочего диапазона и тогда определение последней строки/столбца сработает как ожидается, даже если до этого в ячейке содержались данные, которые впоследствии были удалены.

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

Способ 4:
Определение последней строки и столбца, а так же адрес ячейки методом Find

Dim rF As Range Dim lLastRow As Long, lLastCol As Long ‘ищем последнюю ячейку на листе, в которой хранится хоть какое-то значение Set rF = ActiveSheet.UsedRange.Find(«*», , xlValues, xlWhole, , xlPrevious) If Not rF Is Nothing Then lLastRow = rF.Row ‘последняя заполненная строка lLastCol = rF.Column ‘последний заполненный столбец MsgBox rF.Address ‘показываем сообщение с адресом последней ячейки Else ‘если ничего не нашлось — значит лист пустой ‘и можно назначить в качестве последних первую строку и столбец lLastRow = 1 lLastCol = 1 End If

Этот метод, пожалуй, самый оптимальный в случае, если надо определить последнюю строку/столбец на листе без учета форматов и формул — только по отображаемому значению в ячейке. Например, если на листе большая таблица и последние строки заполнены формулами, возвращающими пустую ячейку(=»»), предыдущие варианты вернут строку/столбец ячейки с последней формулой, в то время как данный метод вернет адрес ячейки только в случае, если в ячейке реально отображается какое-то значение. Такой подход часто используется для того, чтобы определить границы данных для последующего анализа заполненных данных, чтобы не захватывать пустые ячейки и не тратить время на их проверку.

Однако данный метод не будет учитывать в просмотре скрытые строки и столбцы. Это следует учитывать при его применении.

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

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

Важно знать: необходимо помнить, что если ячейка содержит формулу, пусть и возвращающую значение «», Excel не считает её пустой(к слову совершенно справедливо) и включает в просмотр при поиске последней ячейки.

Статья помогла? Поделись ссылкой с друзьями!

Excel vba последняя заполненная ячейка

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

Примеры кода

Скачать

Типовые задачи

Перебор ячеек диапазона (вариант 4)

Для коллекции добавил четвёртый вариант перебора ячеек. Как видите, можно выбирать, как перебирается диапазон — по столбцам или по строкам. Обратите внимание на использование свойства коллекции Cells . Не путайте: свойство Cells рабочего листа содержит все ячейки листа, а свойство Cells диапазона ( Range ) содержит ячейки только этого диапазона. В данном случае мы получаем все ячейки столбца или строки.

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

Работа с текущей областью

Excel умеет автоматически определять текущую область вокруг активной ячейки. Соответствующая команда на листе вызывается через Ctrl + A . Через ActiveCell мы посредством свойства Worksheet легко выходим на лист текущей ячейки, а уже через него можем эксплуатировать свойство UsedRange , которое и является ссылкой на Range текущей области. Чтобы понять, какой диапазон мы получили, мы меняем цвет ячеек. Функция GetRandomColor не является стандартной, она определена в модуле файла примера.

Определение границ текущей области

Демонстрируем определение левого верхнего и правого нижнего углов диапазона текущей области. С левым верхним углом всё просто, так как координаты этой ячейки всегда доступны через свойства Row и Column объекта Range (не путать с коллекциями Rows и Columns !). А вот для определения второго угла приходится использовать конструкцию вида .Rows(.Rows.Count).Row , где .Rows.Count — количество строк в диапазоне UsedRange , .Rows(.Rows.Count) — это мы получили последнюю строку, и уже для этого диапазона забираем из свойства Row координату строки. Со столбцом — по аналогии. Также обратите внимание на использование оператора With . Как видите, оператор With , помимо сокращения кода, также позволяет отказаться от объявления отдельной объектной переменной через оператор Set , что очень удобно.

Выделение столбцов / строк текущей области

Тут нет ничего нового, мы всё это обсудили в предыдущем примере. Мы получаем ссылки на столбцы / строки, меняя их цвет для контроля результата работы кода.

Сброс форматирования диапазона

Для возвращения диапазона к каноническому стерильному состоянию очень просто и удобно использовать свойство Style , и присвоить ему имя стиля «Normal». Интересно, что все остальные стандартные стили в локализованном офисе имеют русские имена, а у этого стиля оставили англоязычное имя, что неплохо.

Поиск последней строки столбца (вариант 1)

Range имеет 2 свойства EntireColumn и EntireRow , возвращающие столбцы / строки, на которых расположился ваш диапазон, но возвращают их ЦЕЛИКОМ. То есть, если вы настроили диапазон на D5 , то Range(«D5»).EntireColumn вернёт вам ссылку на D:D , а EntireRow — на 5:5 .

Идём далее — свойство End возвращает вам ближайшую ячейку в определенном направлении, стоящую на границе непрерывного диапазона с данными. Как это работает вы можете увидеть, нажимая на листе комбинации клавиш Ctrl + стрелки . Кстати, это одна из самых полезных горячих клавиш в Excel. Направление задаётся стандартными константами xlUp , xlDown , xlToRight , xlToLeft .

Классическая задача у Excel программиста — определить, где кончается таблица или, в данном случае, конкретный столбец. Идея состоит в том, чтобы встать на последнюю ячейку столбца (строка 1048576) и, стоя в этой ячейке, перейти по Ctrl + стрелка вверх (что на языке VBA — End(xlUp) ).

Поиск последней строки столбца (вариант 2)

Ещё один вариант.

Поиск «последней» ячейки листа

Тут показывается, как найти на листе ячейку, ниже и правее которой находятся только пустые ячейки. Соответственно данные надо искать в диапазоне от A1 до этой ячейки. На эту ячейку можно перейти через Ctrl + End . Как этим воспользоваться в VBA показано ниже:

Разбор клипо-генератора

Ну, и в качестве развлечения и разрядки взгляните на код клипо-генератора, который генерирует цветные квадраты в заданных границах экрана. На некоторых это оказывает умиротворяющий эффект 🙂

По нашей теме в коде обращает на себя внимание использование свойства ReSize объекта Range . Как не трудно догадаться, свойство расширяет (усекает) текущий диапазон до указанных границ, при этом левый верхний угол диапазона сохраняет свои координаты. А также посмотрите на 2 последние строчки кода, реализующие очистку экрана. Там весьма показательно использован каскад свойств End и Offset .

Ссылка на основную публикацию
Adblock
detector