Забыли пароль?


Хитрости »
Основные понятия (24)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (17)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (65)
Разное (42)
Баги и глюки Excel (4)

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

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

Sub Get_First_Cell()
    Dim lFirstRow As Long, lFirstCol As Long, rFndRng As Range
    'проверяем, есть ли данные в первой ячейке диапазона данных
    If ActiveSheet.UsedRange.Cells(1, 1) <> "" Then
        lFirstRow = ActiveSheet.UsedRange.Row
        lFirstCol = ActiveSheet.UsedRange.Column
    Else
        'ищем ячейку с любым значением(так же с формулой)
        Set rFndRng = ActiveSheet.UsedRange.Find("*", , xlFormulas, xlWhole)
        If rFndRng Is Nothing Then
            MsgBox "Лист не содержит данных", vbInformation, "Информация": Exit Sub
        End If
        lFirstRow = rFndRng.Row: lFirstCol = rFndRng.Column
    End If
    MsgBox "Номер строки первой заполненной ячейки: " & lFirstRow & vbNewLine & _
           "Номер столбца первой заполненной ячейки: " & lFirstCol
End Sub

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

Если необходимо определить первую заполненную ячейку в определенном столбце:

Sub Get_First_Cell_InColumn()
    Dim lFirstRow As Long, rFndRng As Range
    'проверяем, есть ли данные в первой ячейке столбца B (2)
    If ActiveSheet.Columns(2).Rows(1) <> "" Then
        lFirstRow = 1
    Else
        'ищем ячейку с любым значением(так же с формулой)
        Set rFndRng = ActiveSheet.Columns(2).Find("*", , xlFormulas, xlWhole)
        If rFndRng Is Nothing Then
            MsgBox "Лист не содержит данных", vbInformation, "Информация": Exit Sub
        End If
        lFirstRow = rFndRng.Row
    End If
    MsgBox "Номер строки первой заполненной ячейки столбца B: " & lFirstRow
End Sub

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

Sub Get_First_Cell_IgnoreHiddenCells()
    Dim lFirstRow As Long, lFirstCol As Long, rFndRng As Range
 
    'делаем пропуск ошибок на случай, если на листе нет данных
    On Error Resume Next
    'ищем ячейку с любым значением(так же с формулой)
    Set rFndRng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Find("*", , xlFormulas, xlWhole)
    'не нашли ячейку - завершаем процедуру
    If rFndRng Is Nothing Then
        MsgBox "Лист не содержит данных", vbInformation, "Информация"
        Exit Sub
    Else'нашли ячейку - получаем данные по номеру строки и столбца
        lFirstRow = rFndRng.Row: lFirstCol = rFndRng.Column
        MsgBox "Номер строки первой заполненной ячейки: " & lFirstRow & vbNewLine & _
               "Номер столбца первой заполненной ячейки: " & lFirstCol
    End If
End Sub

Если искать первую ячейку необходимо не по наличию данных(коды выше определят ячейку, если в ней есть формула или значение), а исключительно по видимому значению(т.е. чтобы даже если в ячейке есть формула, но она не возвращает никакого значения, то такая ячейка не учитывалась), то в любом приведенном выше коде надо в строке:
.Find("*", , xlFormulas, xlWhole)
заменить xlFormulas на xlValues

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


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

Поиск по меткам

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки
Обсуждение: 9 комментариев
  1. Maksim:

    Добрый день. Огромное спасибо за статью, очень помогла. Только с одним не получается разобраться... В этом макросе он ищет первую заполненную строку или столбец на всем листе... а как изменить чтобы он искал в определенном диапазоне? Я сделал, чтобы он искал только строки... но мне надо чтобы он искал строки в диапазоне с B2:B1000 к примеру... т.е. столбик "B", но начиная со второй строки... Буду признателен за помощь.

  2. Владимир:

    1-й макрос работает.
    А 2-й нет. Выдает ошибку
    Run-time error '451'
    Property let procedure not definded property get procedure did not return an object

  3. Андрей:

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

    • Если скрытые ячейки учитывать не надо, то попробуйте так:
      Set rFndRng = ActiveSheet.UsedRange.SpecialCells(xlVisible).Find("*", , xlFormulas, xlWhole)

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти
Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2020 Excel для всех   Войти