Макрос для добавления строки в таблицу Excel

Обновлено: 21.11.2024

В этом духе, что именно подразумевается под строкой? Это строка таблицы? Ряд всего листа? Может ли отдельная ячейка быть строкой, если мы ограничимся одним столбцом? Давайте рассмотрим различные способы вставки строк.

Метод угадывания

Я буду называть этот метод методом угадывания, потому что даже непрограммисты, вероятно, могут догадаться, как он это делает. Допустим, вам нужна новая строка 2. Возможно, вам нужно загрузить в нее некоторые данные, потому что вы используете рабочий лист в качестве базы данных в обратном хронологическом порядке для продаж — вопреки всем советам, могу я добавить. Совет. Не используйте Excel в качестве базы данных. VBA работает и в Access.

Чтобы добавить новую строку 2, это действительно так же просто, как

Чтобы быть более точным, это обращается к коллекции Rows и использует метод .Insert для второй записи. .Insert относится ко многим объектам, но VBA интерполирует, что вы хотите вставить строку при доступе к коллекции Rows.

Если вы хотите вставить несколько строк, вам нужно заключить диапазон в кавычки:

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

Строка таблицы

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

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

Чтобы настроить таргетинг на строку в таблице, можно настроить таргетинг на верхнюю строку таблицы, используя диапазон двумя способами. Один из способов — указать столбцы и строки в вашем заявлении:

На самом деле, вы можете даже удалить из него часть .Rows и получить тот же результат, потому что Excel понимает, что этот диапазон действительно является одной строкой.

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

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

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

Создавайте мощные макросы с помощью нашего бесплатного комплекта разработчика VBA

Такой макрос легко скопировать и вставить, но создать его самостоятельно сложнее. Чтобы помочь вам создавать макросы, подобные этому, мы создали бесплатный комплект разработчика VBA и написали Большую книгу макросов Excel VBA, полную сотен готовых макросов, которые помогут вам освоить файловый ввод-вывод, массивы, строки и многое другое. скопируйте ниже.

Сдвиг

Метод .Insert принимает два дополнительных ввода: Shift и CopyOrigin . Поскольку они необязательны, вам не нужно их использовать, но в некоторых случаях они могут быть полезны.

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


Подсказка в графическом интерфейсе Excel для сдвига после вставки ячеек

В большинстве случаев вы, вероятно, будете использовать xlShiftDown , который используется по умолчанию для .Insert при обращении к строкам. Однако иногда вам может понадобиться скопировать вправо, например, для визуальной проверки после запуска подпрограммы:

Это приведет к следующему результату, где диапазон H2:J2 был вставлен, а исходные значения были перемещены вправо:


Ячейки смещены вправо

Другой необязательный параметр выбирает, откуда копировать формат вставленных ячеек. По умолчанию используется значение xlFormatFromLeftOrAbove. В нашем примере таблицы, если мы вставляем в «A2:E2», мы копируем форматирование ячейки из «A1:E1».Поскольку это наш заголовок, вставленные ячейки изначально выделены полужирным шрифтом. Чтобы убедиться, что у нас не жирный шрифт, вы должны явно указать параметр CopyOrigin:

Это гарантирует, что вставленные ячейки будут использовать формат ячеек ниже диапазона до вставки, а не тот, что выше (формат заголовка).

Свойство всей строки

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

Чтобы воспроизвести команду Insert Entire Row графического интерфейса из этого примера, вы добавите свойство EntireRow объекта Range:

Это наиболее полезно, если у вас есть только местоположение ячейки (особенно относительное местоположение, так что вы не знаете истинное местоположение) и вы хотите вставить полную строку.

Заключение

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

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

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

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

Эта статья была написана Кори Сарвером, автором статей для блога VBA Tutorials. Посетите его в LinkedIn и на его личной странице.

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

Вставьте одну строку с помощью VBA

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

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

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

Вставить несколько строк

Я нашел два способа вставки нескольких строк на лист. Первый — это тот же метод вставки, который мы использовали в приведенном выше примере.

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

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

Итак, вот второй метод.

Когда вы запускаете этот код, он просит вас ввести количество строк, которые вы хотите добавить, а затем номер строки, в которую вы хотите добавить все эти строки. Он использует FOR LOOP (For Next), чтобы зациклить это количество раз и вставить строки одну за другой.

Вставка строк на основе значений ячеек

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

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

Вставить строку без форматирования

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

Когда вы запускаете приведенный выше код, он вставляет новую строку перед 7-й строкой. Теперь, что происходит, когда вы вставляете строку перед 7-й строкой, эта новая строка становится 7-й строкой, а затем вторая строка кода удаляет форматы из этой строки.

Вставить скопированную строку

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

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

Макрос Excel для добавления строки в конец таблицы (быстрый просмотр)

⧭ Объяснение кода:

  • Этот код создает макрос с именем Add_Empty_Row.
  • Он берет имя таблицы в качестве входных данных от пользователя, а затем устанавливает таблицу как ListObject.
  • Затем в конец таблицы добавляется новая строка ListRow.

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

2 способа разработки макроса Excel для добавления строки в конец таблицы

Здесь у нас есть таблица с именем Table1 на листе Excel, которая содержит имена, начальную и текущую зарплаты некоторых сотрудников компании Sunflower Group.

Сегодня наша цель — разработать макрос для добавления новой строки в конец этой таблицы.

1. Макрос Excel для добавления пустой строки в конец таблицы

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

Для этой цели можно использовать следующий код VBA:

⧭ Код VBA:

⧪ Примечание. Этот код создает макрос с именем Add_Empty_Row.

⧭ Вывод:

Сначала сохраните файл как книгу Excel с поддержкой макросов. Затем запустите макрос.

Появится поле ввода, в котором вас попросят ввести название таблицы. Здесь я ввел Table1.

Нажмите "ОК". И вы получите новую пустую строку, добавленную в конец таблицы.

2. Макрос Excel для добавления строки с данными в конец таблицы

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

Давайте вставим строку, заполненную данными Анжела Хопкинс, 35 000 долларов США и 40 000 долларов США, в конец таблицы Table1.

Код VBA будет следующим:

⧭ Код VBA:

⧪ Примечание. Этот код создает макрос с именем Add_Row_with_Data

⧭ Вывод:

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

Вы получите два поля ввода. В первом поле вас попросят ввести имя таблицы. Здесь я ввел Table1.

Нажмите "ОК". Во втором поле вам будет предложено ввести данные, которые вы хотите ввести в новую строку, разделенные запятыми.

Здесь я ввел Анджелу Хопкинс, 35000,40000 (без пробела после запятой).

Нажмите "ОК".И ваши данные будут введены в новую строку в нижней части таблицы.

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

Здесь мы использовали ListObject и ListRow, два очень важных объекта VBA. Чтобы узнать больше об объектах и ​​коллекциях VBA, перейдите по этой ссылке.

Заключение

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

Для вставки строк или столбцов мы будем использовать метод вставки.

Вставить одну строку или столбец

Вставить новую строку

Чтобы вставить одну строку, вы можете использовать объект Rows:

Или вы можете использовать объект Range вместе с EntireRow:

Вставить новый столбец

Подобно вставке строк, мы можем использовать объект Columns для вставки столбца:

Или объект Range вместе с EntireColumn:

Вставить несколько строк или столбцов

Вставить несколько строк

При вставке нескольких строк с помощью объекта Rows вы должны вводить строки в кавычках:

Вставка нескольких строк с помощью объекта Range работает так же, как и с одной строкой:

Вставить несколько столбцов

При вставке нескольких столбцов с помощью объекта Columns вводите буквы столбцов в кавычках:

Вставка нескольких столбцов с помощью объекта Range работает так же, как и с одним столбцом:

Программирование VBA стало проще

Прекратите искать код VBA в Интернете. Узнайте больше об AutoMacro — конструкторе кода VBA, который позволяет новичкам создавать процедуры с нуля с минимальными знаниями в области кодирования и множеством функций, позволяющих сэкономить время для всех пользователей!

Вставить — Сдвинуть и скопировать источник

Метод Insert имеет два необязательных аргумента:

  • Shift — в каком направлении смещать ячейки.
  • CopyOrigin — форматирование ячейки для копирования (сверху, снизу, слева или справа).

Аргумент Shift не имеет значения при вставке целых строк или столбцов. Это позволяет вам только указать сдвиг вниз или сдвиг вправо:

  • xlShiftDown — сдвиг ячеек вниз
  • xlShiftToRight — сдвиг ячеек вправо

Как видите, сдвинуться вверх или вправо нельзя.

Аргумент CopyOrigin имеет два потенциальных входных значения:

  • xlFormatFromLeftorAbove — (0) только что вставленные ячейки форматируются из ячеек выше или слева
  • xlFormatFromRightorBelow (1) Новые вставленные ячейки форматируются из ячеек, расположенных ниже или справа.

Давайте рассмотрим несколько примеров аргумента CopyOrigin. Вот наши исходные данные:

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

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