Подсчитать количество строк в excel в vba

Обновлено: 23.11.2024

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

Использование VBA для подсчета строк

  1. Во-первых, вам нужно определить диапазон, для которого вы хотите подсчитать строки.
  2. После этого используйте точку (.), чтобы открыть список свойств и методов.
  3. Далее введите или выберите свойство «Строки».
  4. В конце используйте свойство "Количество".

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

Подсчет строк для используемого диапазона

Подсчет строк с данными с помощью VBA

Вы также можете подсчитывать строки, в которых у вас есть данные, игнорируя пустые строки.

Sub vba_count_rows_with_data() Dim counter As Long Dim iRange As Range With ActiveSheet.UsedRange 'перебрать каждую строку из используемого диапазона For Each iRange In .Rows 'проверить, содержит ли строка ячейку со значением If Application.CountA( iRange) > 0 Then 'подсчитывает количество непустых строк. Cells counter = counter + 1 End If Next End With MsgBox "Количество используемых строк равно " & counter End Sub

Есть еще

Это руководство по VBA написано Puneet Gogia для Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019 и Excel для Mac. Чтобы узнать больше о VBA, ознакомьтесь со статьями (Что такое VBA в Excel и Свойство диапазона и ячеек в VBA).

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

Как считать строки в VBA?

Например, посмотрите на приведенные ниже данные в Excel.

Из приведенных выше данных нам нужно определить, сколько строк находится в диапазоне от A1 до A8. Поэтому сначала определите переменную как целое число для хранения количества строк.

Код:

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

Код:

После предоставления диапазона нам нужно подсчитать количество строк, поэтому выберите свойство ROWS объекта RANGE.

В свойстве ROWS объекта RANGE мы подсчитываем количество строк, поэтому сейчас выберите свойство «COUNT».

Теперь в окне сообщения отображается значение переменной.

Код:

Теперь запустите код и посмотрите количество строк предоставленного диапазона ячеек.

Хорошо, для диапазона предоставлено 8 строк, поэтому количество строк в окне сообщения равно 8.

У нас есть и другие способы подсчета строк. Для описанного выше метода нам нужно указать диапазон ячеек, и в ячейках этого диапазона он показывает количество выбранных строк.

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

Чтобы перейти к последней использованной ячейке из ячейки A1, мы нажимаем горячую клавишу Excel Ярлык Excel Ярлык Excel — это способ более быстрого выполнения ручного задания. подробнее «Ctrl + стрелка вниз», чтобы перейти к последней ячейке перед пустой ячейкой.

Сначала укажите ячейку как A1, используя объект RANGE.

Код:

Посмотрите туда с помощью клавиши END, мы можем увидеть все клавиши со стрелками, такие как «xlDown, xlToLeft, xlToRight и xlUp», поскольку нам нужно двигаться вниз, используя опцию «xlDown».

Код:

Код:

Итак, в строках у нас есть данные.

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

Код:

Теперь нам нужно указать номер строки для начала, проблема здесь в том, что мы не уверены, сколько строк данных у нас есть, поэтому мы можем сразу перейти к последней строке рабочего листа, для это упоминает свойство ROWS.COUNT.

Код:

Далее нам нужно указать, в каком столбце мы находим последнюю использованную строку, поэтому в данном случае мы находим в первом столбце, поэтому укажите 1.

Код:

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

Код:

Таким образом, вы перейдете к последней использованной ячейке столбца 1, и в этой ячейке нам нужен номер строки, поэтому используйте свойство ROW, чтобы получить номер строки.

Код:

Что нужно помнить

  • COUNT – количество строк на листе.
  • Если у вас есть диапазон, то будет указано количество строк, выбранных в этом диапазоне.
  • Свойство ROW возвращает номер строки активной ячейки.

Рекомендуемые статьи

Это руководство по подсчету строк в VBA. Здесь мы обсуждаем, как подсчитывать используемые строки в Excel с помощью кодирования VBA, а также практические примеры и загружаемый шаблон Excel. Вы можете узнать больше об Excel из следующих статей-

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

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

Подсчет строк данных в диапазоне

Диапазон содержит непрерывные данные

В этом примере у нас есть список данных о ежемесячных расходах, как показано на рисунке ниже. Список непрерывный, между ними нет «прерывистых строк» ​​(т. е. в диапазоне нет пустых строк). В подобном списке легко найти количество строк.

Приведенный ниже макрос countDataRows1 использует range.Rows.Count для нахождения количества строк данных в текущем выделении. Чтобы использовать макрос, мы сначала выбираем список данных и запускаем макрос. Макрос также возвращает ответ в окне сообщения.

Однако вам может показаться неудобным вручную выбирать весь список перед запуском макроса. Мы можем улучшить макрос, чтобы он автоматически находил «CurrentRegion» диапазона таблицы. (См. строку 3 макроса «countDataRows2» ниже). Вам нужно будет только выбрать любую ячейку в списке перед запуском макроса.

Некоторые строки в диапазоне пусты

В отличие от таблиц баз данных (таких как Access), таблицы Excel не всегда непрерывны. Мы часто работаем с таблицами Excel с макетами «свободного стиля», и наши таблицы были разработаны с разрывами (пустыми строками), чтобы их было легче читать. Однако это вызывает дополнительную проблему, когда мы пытаемся найти фактическое количество заполненных строк на листе с помощью VBA.

Допустим, у нас есть отчет о прибылях и убытках, как показано ниже. Есть несколько пустых строк (например, строки 6, 25 и 28), которые разделяют ключевые разделы финансового отчета. Мы хотим использовать VBA, чтобы найти:

Некоторые плохие новости заключаются в том, что метод «CurrentRegion», который мы использовали в предыдущем примере (макрос «countDataRows2»), больше не будет работать в этом случае. Чтобы проиллюстрировать проблему, если мы выберем ячейку A2 вручную, а затем нажмем сочетание клавиш Ctrl+Shift+8, чтобы выбрать CurrentRegion, будет выбран только первый блок данных из строк с 1 по 5 (поскольку строка 6 пуста). Поэтому нам нужен альтернативный подход VBA, чтобы найти весь диапазон нашего отчета о прибылях и убытках.

Мы можем использовать свойство «UsedRange» объекта ActiveSheet, чтобы найти диапазон отчета о прибылях и убытках, не затрагивая пустые строки, а затем подсчитать количество строк. Приведенный ниже макрос countDataRows3 выполняет эту работу за один шаг в строке 3.

Следующий шаг — найти количество использованных строк с данными в этом отчете о прибылях и убытках. Ниже я продемонстрирую два подхода.

Подход 1 — проверка только одного столбца для определения используемых строк

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

В приведенном выше макросе «countDataRows4» в строке 8 встроенная функция листа Excel CountA была вызвана в VBA. Очень важно отметить, как он был назван. Вы всегда можете вызвать встроенную функцию листа Excel, используя синтаксис: «Application. ” (а затем входные параметры).

После запуска макроса окно сообщения возвращает ответы как для общего количества строк, так и для количества использованных строк. (Окно сообщения, показанное ниже, взято из версии Excel для Mac)

Подход 2 – проверить все столбцы, чтобы определить используемые строки

Однако, если нам нужно проверить все столбцы, чтобы убедиться, что вся строка пуста, мы можем использовать цикл For-Next вместе с функцией рабочего листа COUNTA

Идея состоит в том, чтобы (1) найти используемый диапазон, (2) перебрать каждую строку используемого диапазона и (3) использовать функцию рабочего листа COUNTA, чтобы проверить, не является ли какая-либо из ячеек в каждой строке непустой.

Если вас интересуют методы удаления пустых строк в Word вместо Excel, ознакомьтесь с этой статьей.

Подсчет строк с определенным словом

Что если мы хотим подсчитать количество строк, содержащих слово «Расходы»? Мы можем адаптировать предыдущий макрос и использовать функцию рабочего листа «CountIf» (вместо «CountA»).

В приведенном ниже макросе countDataRowswithWord, строка 7, функция CountIf используется для подсчета количества ячеек в каждой строке, содержащей слово «Expense». Мы используем здесь подстановочный знак «*», чтобы функция учитывала любую ячейку, часть содержимого которой содержит «Расходы», например, «Другие расходы 1», «Общие расходы».

Заключение

Мы полностью рассмотрели некоторые ключевые методы определения количества строк в диапазоне. В дополнение к Range.Rows.Count вы можете использовать «CurrentRegion» или «UsedRange», чтобы быстро узнать область блока данных. Кроме того, вы можете использовать цикл For-Next для просмотра каждой строки в диапазоне вместе со встроенными функциями Excel, такими как «COUNTA()» или «COUNTIF()», для выполнения более подробной проверки определенных критериев.

Об авторе Эдвине

Эдвин — Microsoft MVP, дипломированный бухгалтер аудиторской компании "большой четверки", специалист по обработке и анализу данных и специалист по ИТ-безопасности. Он имеет более чем двадцатилетний опыт разработки средств автоматизации для Microsoft Office в финансовой отрасли. В свободное время Эдвин также играет на гобое в симфоническом оркестре.

Сегодня я покажу вам, как можно подсчитать строки с помощью VBA в Excel из любого набора данных. Я покажу вам, как подсчитывать строки из определенного диапазона, из выбранного диапазона, с помощью соответствия определенному критерию, сопоставления определенного текстового значения и исключения пустых ячеек.

Загрузить практическую рабочую тетрадь

5 способов подсчета строк с помощью VBA в Excel

Здесь у нас есть набор данных с именами некоторых учеников и их оценками на английском языке в школе под названием Sunflower Kindergarten.

Сегодня наша цель — подсчитать общее количество строк с помощью кода VBA.

1. Использование кода VBA для подсчета строк определенного диапазона

⧪ Шаг 1:

➤ Нажмите клавиши ALT+F11 на клавиатуре. Откроется окно VBA.

⧪ Шаг 2:

➤ Перейдите на вкладку "Вставка" в окне VBA.

➤ Из доступных вариантов выберите Модуль.

Я

⧪ Шаг 3:

➤ Откроется новое окно модуля под названием «Модуль 1».

➤ Вставьте следующий код VBA в модуль.

Код:

Примечания:

  • Этот код создает макрос с именем Count_Rows.
  • Третья строка кода содержит указанный диапазон «B4:C13». Я хочу подсчитать количество строк в этом диапазоне.
  • Вы используете свой.

⧪ Шаг 4:

➤ Сохраните книгу как книгу Excel с поддержкой макросов.

⧪ Шаг 5:

➤ Вернитесь к рабочему листу и нажмите клавиши ALT+F8 на клавиатуре.

➤ Откроется диалоговое окно «Макрос». Выберите Count_Rows (имя макроса) и нажмите «Выполнить».

⧪ Шаг 6:

➤ Вы увидите небольшое окно сообщения, показывающее общее количество строк (в данном случае 10).

➤ Нажмите OK, чтобы выйти.

2. Запуск кода Excel VBA для подсчета строк выбранного диапазона

В предыдущем методе мы подсчитывали количество строк определенного диапазона (B4:C13).

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

Все шаги аналогичны методу 1 (шаги 1–6).

⧪ Только на шаге 3 вместо предыдущего кода вставьте этот код:

Код:

Примечание:

  • Этот код создает модуль с именем Count_Selected_Rows.

⧪ А на шаге 5 перед запуском кода сначала выберите диапазон. Здесь я выбрал весь свой набор данных (без заголовков столбцов).

⧪ Затем нажмите ALT+F8, выберите Count_Selected_Rows и нажмите Run.

Появится окно сообщения, показывающее общее количество строк в выбранном диапазоне (в данном случае 10).

3. Вставка кода VBA для подсчета строк с критериями в Excel

Мы также можем использовать код VBA для подсчета общего количества строк, соответствующих определенному критерию.

Например, давайте создадим макрос, который будет подсчитывать количество учащихся, получивших оценки менее 40.

Все шаги аналогичны методу 1 (шаги 1–6).

⧪ Только на шаге 3 измените код VBA на этот:

Код:

Примечание:

  • Этот код создает модуль с именем Count_Rows_with_Criteria.
  • В строке 6 мы использовали "

Похожие чтения

4. Встроить код VBA для подсчета строк, имеющих определенное текстовое значение

Вы также можете использовать код VBA для подсчета количества строк, содержащих определенное текстовое значение.

Посмотрите на этот новый набор данных.

У нас есть книжные записи некоторых книг книжного магазина под названием Martin Bookstore.

Давайте создадим макрос, который будет подсчитывать количество книг с определенным текстом из этого набора данных.

Все шаги аналогичны методу 1 (шаги 1–6).

⧪ Только на шаге 3 измените код VBA на этот:

Код:

Примечание:

  • Этот код создает модуль с именем Count_Rows_with_Specific_Text.

⧪ А на шаге 5 перед запуском кода выберите диапазон ячеек с текстовыми значениями. Здесь я выбрал диапазон B4:B13 (название книг).

⧪ Затем нажмите ALT+F8, выберите Count_Rows_with_Specific_Text и нажмите Run.

⧪ Появится окно ввода, в котором вас попросят ввести конкретное текстовое значение, которое вы хотите сопоставить.

Для этого примера я ввел его как «история».

Наконец, вы получите окно сообщения, показывающее общее количество строк, содержащих определенный текст (в данном случае 3).

5. Подсчет строк с пустыми ячейками с помощью VBA в Excel

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

Посмотрите на этот новый набор данных.

У нас есть Оценки некоторых кандидатов в тесте найма компании под названием APEX group.

Но, к сожалению, некоторые кандидаты не смогли явиться на тест и вместо их оценок остались пустые клетки.

Давайте разработаем макрос, который будет подсчитывать общее количество строк, исключая пустые ячейки.

Это означает, сколько кандидатов действительно участвовало в тесте.

Все шаги аналогичны методу 1 (шаги 1–6).

⧪ Просто на шаге 3 введите этот код VBA вместо предыдущего:

Код:

Примечание:

  • Этот код создает модуль с именем Count_Rows_with_Blank_Cells.

⧪ На шаге 5 перед запуском кода выберите диапазон ячеек с пустыми ячейками. Здесь я выбрал диапазон C4:C13 (оценки в тесте).

⧪ Затем нажмите ALT+F8, выберите Count_Rows_with_Blank_Cells и нажмите Run.

Появится окно сообщения, показывающее общее количество строк, исключая пустые ячейки (7 в данном случае).

Заключение

Используя эти методы, вы можете подсчитывать строки с помощью VBA из набора данных в Excel, соответствующие различным условиям. У тебя есть проблемы? Не стесняйтесь спрашивать нас.

Читайте также: