Как найти первую пустую ячейку vba - Исправление недочетов и поиск решений вместе с Examum.ru

My sheet look like :

I have a function to get index of the LAST empty cell in column A:

NextRow = Range("A" & Rows.Count).End(xlUp).Row + 1

This function works to write on second array (Type2).

But now, i would like a function to get index of the FIRST empty cell in column A. So i went to this website: Select first empty cell and i tried to adapt code but it’s doesn’t work:

If Array= "Type1" Then
    Dim ws As Worksheet
    Set ws = ActiveSheet
    For Each cell In ws.Columns(1).Cells
       If IsEmpty(cell) = True Then NextRow = cell: Exit For 'ERROR 1004
    Next cell
End If
If Array= "Type2" Then 'It s works
    NextRow = Range("A" & Rows.Count).End(xlUp).Row + 1
End If

ActiveSheet.Range("A" & NextRow) = "TEST"

Could you help me to adapt my code to have NextRow = IndexOf FIRST empty cell in A ?

asked Oct 28, 2016 at 9:13

You could just use the same method you did to get the last one.

NextRow = Range("A1").End(xlDown).Row + 1

answered Oct 28, 2016 at 11:22

LissLiss

4413 silver badges8 bronze badges

I do this and it’ works:

If Array= "Type1" Then
       Dim ws As Worksheet
            Set ws = ActiveSheet
            For Each cell In ws.Columns(1).Cells
               If IsEmpty(cell) = True Then
                    NextRow = cell.Row
                    Exit For
                    MsgBox NextRow
                End If
            Next cell
    End If
    If Array= "Type2" Then 'It s works
        NextRow = Range("A" & Rows.Count).End(xlUp).Row + 1
    End If

    ActiveSheet.Range("A" & NextRow) = "TEST"

Vasily

5,7073 gold badges19 silver badges34 bronze badges

answered Oct 28, 2016 at 9:58

FerfaFerfa

2112 gold badges4 silver badges16 bronze badges

You should look bottom up for this.

And Find is better than xlUp.

Sub FindBlank()
    Dim ws As Worksheet
    Dim rng1 As Range
    Set ws = ActiveSheet
    Set rng1 = ws.Columns(1).Find("*", ws.[a1], xlFormulas, , xlByColumns, xlPrevious)
    If Not rng1 Is Nothing Then
        MsgBox "Last used cell is " & rng1.Address(0, 0)
    Else
        MsgBox ws.Name & " row1 is completely empty", vbCritical
    End If
End Sub

answered Oct 31, 2016 at 8:02

brettdjbrettdj

54.6k16 gold badges113 silver badges176 bronze badges

I took a similar approach to some of the answers, but with the goal of repeatedly looking down the column until I could guarantee that there was no more populated cells below.

I turned this into a small function that I put in a standard module:-

Public Function getFirstBlankRowNumberOnSheet(sht As Worksheet, Optional startingRef As String = "A1") As Long 'may get more than 32767 rows in a spreadsheet (but probably not!)
  Dim celTop As Range
  Dim celBottom As Range
  On Error Resume Next
  Set celTop = sht.Range(startingRef)

  Do
    Set celBottom = celTop.End(xlDown)
    Set celTop = celBottom.Offset(1) 'This will throw an error when the bottom cell is on the last available row (1048576)
  Loop Until IsEmpty(celBottom.value)

  getFirstBlankRowNumberOnSheet = celTop.Row

End Function

This will throw an error if there happens to be content in the row #1048576! The particulars of this are dependent on the Excel version I suppose in terms of maximum row cont allowed.

answered Jun 12, 2020 at 21:39

Источник

21 / 21 / 6

Регистрация: 12.12.2010

Сообщений: 677

Узнать первую пустую ячейку

10.09.2015, 08:34. Показов 25681. Ответов 4

Здравствуйте. Подскажите пожалуйста, у меня есть лист, на котором в некотором месте есть таблица. Дело в том что я через макрос провожу некоторую манипуляцию в таблице — тем самым удаляю в ней ненужные строки. Как мне можно узнать, где заканчивается данная таблица? По первой пустой ячейке? Через цикл? Или есть какой-то другое более лучший способ (может функция специальная или ещё что)? Как это написать на VBA?

Programming Эксперт 94731 / 64177 / 26122 Регистрация: 12.04.2006 Сообщений: 116,782	10.09.2015, 08:34
4

Tolyn

8 / 8 / 6

Регистрация: 08.09.2015

Сообщений: 16

10.09.2015, 09:24

Сообщение было отмечено Linked как решение

Решение

Я всегда делал по первой пустой ячейке

Visual Basic

Do While Cells(i, 1) <> "" 'выполняем пока не появится пустая строка
' Какие то манипуляции с таблицей
    i = i + 1
Loop

smmkp

1 / 1 / 0

Регистрация: 07.08.2015

Сообщений: 5

10.09.2015, 10:05

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

Visual Basic

1 2	LastRow = Sheets("Лист1").Cells(Rows.Count, 1).End(xlUp).Row LastColumn = Sheets("Лист1").Cells(1, Columns.Count).End(xlToLeft).Column

сороконожка

15 / 15 / 5

Регистрация: 01.12.2014

Сообщений: 117

11.09.2015, 13:40

Visual Basic

1	Range("A2:A" & Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row

первая пустая в колонке А.

Границы коментом выше.

Источник

maverick

Пользователь

Сообщений: 20
Регистрация: 01.01.1970

Прошу помощи знающих.
Задача: имеем таблицу данных, которая постоянно пополняется (добавляются строки)
В таблице важным вяляется второй столбец; ячейки в нем заполнены в каждой строке.
Необходим макрос, который будет устанавливать табличный курсор в первую свободную (пустую) ячейку внизу этого столбца (т.е. фактически в следующую заполняему строку таблицы).

К сожалению, я пока «не очень» дружен с VBA =)
Пробовал воспользоваться простой записью макроса и примитивной последовательностью: поиск крайней заполненной ячейки с помощью Ctrl+PageDown и шаг вниз с помощью стрелки. Но попытка провалились, т.к. выбор крайней заполненной ячейки он понимает и записывает, как надо, а вот действия клавиатурных стрелок запоминаются не как «перемещение вниз (вверх, влево, вправо)», а как выбор какой-то конкретной ячейки с конкретным адресом.

Помогите, плз.

vlanib Пользователь Сообщений: 26 Регистрация: 01.01.1970	[В1].End(xlDown).Offset(1, 0).Value = «Последняя свободная ячейка столбца В»

Юрий М Модератор Сообщений: 60741 Регистрация: 14.09.2012 Контакты см. в профиле	В общем случае макрос может выглядеть так: Sub Макрос1() Dim iLastRow As Long iLastRow = Cells(Rows.Count,2).End(xlUp).Row cells(iLastRow+1,2).Select End Sub

maverick Пользователь Сообщений: 20 Регистрация: 01.01.1970	{quote}{login=vlanib}{date=13.05.2010 10:37}{thema=}{post}[В1].End(xlDown).Offset(1, 0).Value = «Последняя свободная ячейка столбца В»{/post}{/quote} уточнение: мне необходима первая, а не крайняя свободная ячейка. Можно как-то переделать?

maverick

Пользователь

Сообщений: 20
Регистрация: 01.01.1970

{quote}{login=Юрий М}{date=13.05.2010 10:41}{thema=}{post}В общем случае макрос может выглядеть так:
Sub Макрос1()
Dim iLastRow As Long
iLastRow = Cells(Rows.Count,2).End(xlUp).Row
cells(iLastRow+1,2).Select
End Sub{/post}{/quote}

Спасибо огромное!
Я совершенно не понял как это работает, но это РАБОТАЕТ! =)))

Serge Пользователь Сообщений: 11309 Регистрация: 01.01.1970	А как переделать код: Sub Макрос1() Dim iLastRow As Long iLastRow = Cells(Rows.Count,2).End(xlUp).Row cells(iLastRow+1,2).Select End Sub , что бы искалась первая пустая ячейка в том столбце, в котором находится активная ячейка?

The_Prist Пользователь Сообщений: 14253 Регистрация: 15.09.2012 Профессиональная разработка приложений для MS Office	Sub Макрос1() Dim iLastRow As Long iLastRow = Cells(Rows.Count,activeCell.Column).End(xlUp).Row cells(iLastRow+1,activeCell.Column).Select End Sub Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…

Serge Пользователь Сообщений: 11309 Регистрация: 01.01.1970	Спасибо! Один вопрос. Если в столбце нет заполненых ячеек, то находится ячейка во второй строке… Почему и как сделать правильно (в первой)?

maverick

Пользователь

Сообщений: 20
Регистрация: 01.01.1970

тут же возник попутный вопрос:
в указаном столбце постоянно добавляются значения; это числа (что-то типа порядкового номера строки).
Как в тоу самую первую пустую ячейку подставить максимальное число этого столбца? А если быть точнее — «максимальное плюс один»

The_Prist Пользователь Сообщений: 14253 Регистрация: 15.09.2012 Профессиональная разработка приложений для MS Office	Sub Макрос1() Dim iLastRow As Long iLastRow = Cells(Rows.Count,activeCell.Column).End(xlUp).Row if iLastRow > 1 then iLastRow = iLastRow + 1 cells(iLastRow,activeCell.Column).Select End Sub Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…

Serge Пользователь Сообщений: 11309 Регистрация: 01.01.1970

The_Prist

Пользователь

Сообщений: 14253
Регистрация: 15.09.2012

Профессиональная разработка приложений для MS Office

{quote}{login=maverick}{date=13.05.2010 11:16}{thema=}{post}тут же возник попутный вопрос:
в указаном столбце постоянно добавляются значения; это числа (что-то типа порядкового номера строки).
Как в тоу самую первую пустую ячейку подставить максимальное число этого столбца? А если быть точнее — «максимальное плюс один»{/post}{/quote}Максимальное и порядковый номер это разные вещи. Если нужно просто следующий порядковый номер в первую пустую ячейку внести:

Sub Макрос1()
Dim iLastRow As Long
iLastRow = Cells(Rows.Count,1).End(xlUp).Row
cells(iLastRow+1,1).Value =cells(iLastRow,1)+1
End Sub

Если максимальное+1
Sub Макрос1()
Dim iLastRow As Long
iLastRow = Cells(Rows.Count,1).End(xlUp).Row
cells(iLastRow+1,1).Value =application.Max(Range(«A1:A» & iLastRow))+1
End Sub

Все применено к первому столбцу

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…

	Спасибо! =) кой-чего подправил и всё работает =)))

А как можно такое провернуть:
Номера строк у меня добавляются автоматом. При этом так же автоматически создаётся копия листа-шаблона.
Надо теперь присвоить имя новому (только что созданому) листу, равное новому номеру строки.
Возможно ли такое?

The_Prist Пользователь Сообщений: 14253 Регистрация: 15.09.2012 Профессиональная разработка приложений для MS Office	Какое отношение вопрос имеет к данной теме? Создавайте тему по правилам и получите ответ. Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…

	А как прописать, что бы просматривался только диапазон P4:P16 , т.к. после этого идут дальше заполненные ячейки. Dim iLastRow As Long iLastRow = Cells(Rows.Count, 16).End(xlUp).Row Cells(iLastRow + 1, 16).Select

The_Prist Пользователь Сообщений: 14253 Регистрация: 15.09.2012 Профессиональная разработка приложений для MS Office	Range(«P16»).End(xlUp).Select Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…

KuklP

Пользователь

Сообщений: 14868
Регистрация: 21.12.2012

E-mail и реквизиты в профиле.

{quote}{login=The_Prist}{date=13.05.2010 11:16}{thema=}{post}Sub Макрос1()
Dim iLastRow As Long
iLastRow = Cells(Rows.Count,activeCell.Column).End(xlUp).Row
if iLastRow > 1 then iLastRow = iLastRow + 1
cells(iLastRow,activeCell.Column).Select
End Sub{/post}{/quote}А так:
Sub www()
Columns(ActiveCell.Column).SpecialCells(4)(1).Select
End Sub
?

Я сам — дурнее всякого примера! …

{quote}{login=The_Prist}{date=24.12.2011 01:20}{thema=}{post}Range(«P16»).End(xlUp).Select{/post}{/quote}

Dim iLastRow As Long
iLastRow = Range(«P16»).End(xlUp).Select
Cells(iLastRow + 1, 16).Select

Переносит на ячейку Р2, а необходимо с верху вниз с Р4 до Р16 в зависимости от заполнения ячеек. Т.к. ячейка Р19 и ниже содержит данные.
Спасибо.

Hugo

Пользователь

Сообщений: 23365
Регистрация: 22.12.2012

Вот это
iLastRow = Range(«P16»).End(xlUp).Select
откуда взяли?

Можно так:
Sub tt()
Dim iLastRow As Long
iLastRow = Range(«P16»).End(xlUp).Row
Cells(iLastRow + 1, 16).Select
End Sub

Это от P16 идём вверх.
Можно наоборот, от P3 идём вниз по пустым:

Sub tt()
Range(«P3:P16»).SpecialCells(xlCellTypeBlanks).Cells(1) = «Пишем_сюда»
End Sub

Hugo Пользователь Сообщений: 23365 Регистрация: 22.12.2012	Вернее так, с обработкой ошибки, когда всё будет заполнено: Sub tt() On Error Resume Next Err.Clear Range(«P4:P15»).SpecialCells(xlCellTypeBlanks).Cells(1) = «Пишем_сюда» If Err <> 0 Then MsgBox «Всё заполнено!!!» Err.Clear End Sub

{quote}{login=Hugo}{date=25.12.2011 01:58}{thema=}{post}Вернее так, с обработкой ошибки, когда всё будет заполнено:

Sub tt()
On Error Resume Next
Err.Clear
Range(«P4:P15»).SpecialCells(xlCellTypeBlanks).Cells(1) = «Пишем_сюда»
If Err <> 0 Then MsgBox «Всё заполнено!!!»
Err.Clear
End Sub{/post}{/quote}

Спасибо. Но нужно чтобы он выделял ячейки с Р4 до Р16, а в этом макросе он вставляет в свободную ячейку надпись Всё заполнено!!!. Т.к. данные в эти ячейка вставляются тоже через макрос а не в ручную.
Спасибо.

{quote}{login=}{date=25.12.2011 02:06}{thema=Re: }{post}{quote}{login=Hugo}{date=25.12.2011 01:58}{thema=}{post}Вернее так, с обработкой ошибки, когда всё будет заполнено:

Попробую так:

Sub tt()
On Error Resume Next
Err.Clear
Range(«P4:P15»).SpecialCells(xlCellTypeBlanks).Cells(1).Select
If Err <> 0 Then MsgBox «Всё заполнено!!!»
Err.Clear
End Sub

{quote}{login=}{date=25.12.2011 02:12}{thema=Re: Re: }{post}{quote}{login=}{date=25.12.2011 02:06}{thema=Re: }{post}{quote}{login=Hugo}{date=25.12.2011 01:58}{thema=}{post}Вернее так, с обработкой ошибки, когда всё будет заполнено:

Попробую так:

Sub tt()
On Error Resume Next
Err.Clear
Range(«P4:P15»).SpecialCells(xlCellTypeBlanks).Cells(1).Select
If Err <> 0 Then MsgBox «Всё заполнено!!!»
Err.Clear
End Sub{/post}{/quote}

Опа. Заработало. Спасибо всем!!!

	Убрал лишнее и оставил только это: Range(«P4:P16»).SpecialCells(xlCellTypeBlanks).Cells(1).Select

Hugo Пользователь Сообщений: 23365 Регистрация: 22.12.2012	On Error Resume Next всёж оставьте

Станислав Пользователь Сообщений: 120 Регистрация: 11.10.2010	{quote}{login=Hugo}{date=25.12.2011 02:29}{thema=}{post}On Error Resume Next всёж оставьте :){/post}{/quote} Спасибо.

А не подскажите ли вы, мне нужно нечто похожее, как выбирать каждую ячейку первого столбца, пока не наткнешься на пустую, естественно после выбора будет какое-либо действие, и это вроде как должно выполняться через цикл, если я правильно понимаю… помогите пожалуйста

KuklP

Пользователь

Сообщений: 14868
Регистрация: 21.12.2012

E-mail и реквизиты в профиле.

floove, Вы демонстрируете недюжинную сообразительность. Тема называется «Поиск первой свободной ячейки в стообце». Все ответы посвящены этому. И тут появляетесь Вы и задаете вопрос: «А как найти первую свободную ячейку в столбце?». Браво!

Я сам — дурнее всякого примера! …

Hugo Пользователь Сообщений: 23365 Регистрация: 22.12.2012	#30 28.12.2011 01:39:16 Сергей, мне кажется, что ты ошибаешься — тут нужен цикл по занятым до первой свободной.

Источник

Dim lClmn As Long
    With ActiveSheet
        lClmn = .Cells(1, .Columns.Count).End(xlToLeft).Column + 1

В переменную запишется крайняя справа пустая ячейка в строке 1

Среди данных пустоту можно найти с помощью цикла:

Sub FindEmpty()
Dim lClmn As Long, j As Long
    lClmn = Cells(1, Columns.Count).End(xlToLeft).Column + 1 ' первый пустой после данных в строке 1
    If lClmn < 9 Then Exit Sub

    For j = 9 To lClmn ' начало поиска со столбца I
        If Cells(1, j).Value = "" Then Exit For
    Next j

    MsgBox j ' сообщение с номером столбца
End Sub

Если пустых ячеек среди данных нет, в сообщении укажется номер первого столбца после диапазона данных

Источник

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

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

Найдите первую пустую ячейку в столбце с формулой

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

1. Выберите пустую ячейку для отображения результата.

2. Скопируйте и вставьте формулу. = МИН (ЕСЛИ (A1: A23 = «»; СТРОКА (A1: A23))) в панель формул, затем нажмите Ctrl + Shift + Enter клавиши на клавиатуре. Смотрите скриншот:

Внимание: Измените диапазон в формуле на свой собственный.

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

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

Помимо формулы, вы также можете найти первую пустую ячейку в столбце с кодом VBA. Пожалуйста, сделайте следующее.

1. Нажмите другой + A11 в то же время открыть Microsoft Visual Basic для приложений окно.

2. в Microsoft Visual Basic для приложений окна, дважды щелкните текущее имя рабочего листа на левой панели, чтобы открыть редактор кода.

3. Скопируйте и вставьте следующий код VBA в редактор.

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

Sub Findfirstblankcell()
	Dim Rng As Range
	Dim WorkRng As Range
	On Error Resume Next
	xTitleId = "KutoolsforExcel"
	Set WorkRng = Application.Selection
	Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type: = 8)
	For Each Rng In WorkRng
		If Rng.Value = "" Then
			MsgBox "No Value, in " & Rng.Address
		End If
	Next
End Sub

4. Нажмите Run кнопку или нажмите кнопку F5 ключ для запуска кода.

5. После запуска кода KutoolsforExcel появится диалоговое окно, выберите диапазон, в котором вы хотите найти первую пустую ячейку, затем щелкните OK кнопка. Смотрите скриншот:

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

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

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

Как найти позицию первого / последнего числа в текстовой строке в Excel?
Как найти ячейки с условным форматированием / с условным форматированием в Excel?
Как найти ячейки с / с проверкой данных в Excel?

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Комментарии (2)

Оценок пока нет. Оцените первым!

Источник