Microsoft Excel:
  Таблицы и VBA. Справочник.
  Вопросы и Ответы. Советы. Примеры.
Меню Заметки | Поиск последней заполненной ячейки

Rambler's Top100


Counter CO.KZ
Вариант I.
Для поиска последней заполненной ячейки можно воспользоваться методом SpecialCells и специальной константой xlLastCell

Пример определения адреса последней заполненной ячейки, а также номера её строки и столбца.
iAddress = Range("A1").SpecialCells(xlLastCell).Address
iRow = Range("A1").SpecialCells(xlLastCell).Row
iClm = Range("A1").SpecialCells(xlLastCell).Column
Предупреждение :
Если рабочий лист защищён, то использование этого варианта приведёт к возникновению ошибки, которую можно избежать, если воспользоваться этим советом.


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

Пример определения номера строки и столбца последней заполненной ячейки.
iRow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1
iClm = ActiveSheet.UsedRange.Column + ActiveSheet.UsedRange.Columns.Count - 1
With ActiveSheet.UsedRange
     iRow = .Row + .Rows.Count - 1
     iClm = .Column + .Columns.Count - 1
End With
Set iList = ActiveSheet
iRow = iList.UsedRange.Row + iList.UsedRange.Rows.Count - 1
iClm = iList.UsedRange.Column + iList.UsedRange.Columns.Count - 1
Set iDiapazon = ActiveSheet.UsedRange
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 If
Set 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

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.

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

For iRow = 1 To iRowLast

Next


Автор [вариант I - IV, VI - VIII] : Климов Павел Юрьевич
© 2004-2016 Климов П.Ю. Все права защищены. WebDesign & Error's Klimoff