Перейти к основному содержанию

Как вставить пустые строки при изменении значения в Excel?

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

Вставлять пустые строки при изменении значения с помощью функции промежуточных итогов

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

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

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


Вставлять пустые строки при изменении значения с помощью функции промежуточных итогов

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

1. Выберите диапазон данных, который вы хотите использовать.

2. Нажмите Данные > Сумма для открытия Сумма диалоговом окне и в Сумма диалог, пожалуйста, сделайте следующие опции:

1: Выберите имя столбца, в который вы хотите вставить пустые строки, в зависимости от того, когда значение изменяется под При каждом изменении в разделе;
2: Выберите Количество из Использование функции раскрывающийся список;
3: Отметьте имя столбца, в который вы хотите вставить промежуточный итог в Добавить промежуточный итог к список

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

4. А затем щелкните цифру 2 в верхней части символа контура, чтобы отобразить только строки промежуточных итогов.

5. Затем выберите данные диапазона промежуточных итогов и нажмите Alt +; сочетания клавиш для выбора только видимых строк, см. снимок экрана:

6. После выбора только видимых строк нажмите кнопку Удалить на клавиатуре, и все строки промежуточных итогов были удалены.

7. Затем щелкните любую другую ячейку и вернитесь Данные > Ungroup > Четкий контур чтобы удалить контуры, см. снимок экрана:

8. Символы контура были очищены сразу, и вы можете видеть, что между данными были вставлены пустые строки при изменении значения, см. Снимок экрана:

9. Наконец, вы можете удалить столбец A по мере необходимости.


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

Если вы только что Kutools for Excel's Различать различия вы можете быстро вставить разрыв страницы, пустые строки, нижнюю границу или цвет заливки, когда значение изменяется по мере необходимости. См. Демонстрацию ниже.         Нажмите, чтобы загрузить Kutools for Excel!


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

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

1. В пустой ячейке C3 введите эту формулу = A3 = A2, а в ячейку D4 введите эту формулу = A4 = A3, см. снимок экрана:

2. Затем выберите C3: D4 и перетащите маркер заполнения в диапазон, в котором вы хотите применить эти формулы, и вы получите Правда or Ложь в ячейках см. снимок экрана:

3, Затем нажмите Ctrl + F , чтобы открыть Найти и заменить диалоговое окно, в появившемся диалоговом окне введите НЕПРАВДА в Найти то, что текстовое поле под Найдите вкладку и щелкните Опции кнопку, чтобы развернуть это диалоговое окно, и выберите Наши ценности из Заглянуть выпадающий список, см. снимок экрана:

4. Нажмите Найти все кнопку, а затем нажмите Ctrl + для выбора всех результатов поиска были выбраны сразу все ячейки FALSE, см. снимок экрана:

6. Закрой Найти и заменить диалоговое окно, на следующем шаге вы можете вставить пустые строки, нажав Главная > Вставить > Вставить строки листа, а пустые строки были вставлены в данные при изменении значения в зависимости от столбца A, см. снимки экрана:

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


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

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

1. Удерживайте ALT + F11 , чтобы открыть Окно Microsoft Visual Basic для приложений.

2. Нажмите Вставить > Модулии вставьте следующий код в Окно модуля.

Код VBA: вставлять пустые строки при изменении значения

Sub InsertRowsAtValueChange()
'Update by Extendoffice
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)
Application.ScreenUpdating = False
For i = WorkRng.Rows.Count To 2 Step -1
    If WorkRng.Cells(i, 1).Value <> WorkRng.Cells(i - 1, 1).Value Then
        WorkRng.Cells(i, 1).EntireRow.Insert
    End If
Next
Application.ScreenUpdating = True
End Sub

3, Затем нажмите F5 нажмите клавишу для запуска этого кода, и появится окно подсказки, позволяющее выбрать данные одного столбца, в которые вы хотите вставить пустые строки при изменении значения, на котором вы основаны, см. снимок экрана:

4, Затем нажмите OK, пустые строки были вставлены между данными при изменении значения в зависимости от столбца A.


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

Если вы попробуете с помощью вышеуказанных сложных методов, здесь я представлю полезный инструмент, Kutools for Excel's Различать различия может помочь вам быстро и легко вставить разрыв страницы, пустые строки, нижнюю границу или цвет заливки при изменении значения ячейки.

Советы:Чтобы применить это Различать различия функция, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените эту функцию.

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Нажмите Кутулс > Формат > Различать различия, см. снимок экрана:

2. В Различать различия по ключевому столбцу диалоговом окне выполните следующие действия:

  • Выберите диапазон данных, который вы хотите использовать, а затем выберите ключевой столбец, на основе которого вы хотите вставить пустые строки;
  • Проверьте Пустая строка из файла Опции раздел и введите количество пустых строк, которые вы хотите вставить.

3. Затем нажмите Ok кнопку, и определенное количество пустых строк было вставлено в данные, если значение ячейки изменяется, см. снимки экрана:

Нажмите, чтобы загрузить Kutools for Excel и бесплатную пробную версию сейчас!

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

🤖 Kutools AI Помощник: Революционный анализ данных на основе: Интеллектуальное исполнение   |  Генерировать код  |  Создание пользовательских формул  |  Анализ данных и создание диаграмм  |  Вызов функций Kutools...
Популярные опции: Найдите, выделите или определите дубликаты   |  Удалить пустые строки   |  Объедините столбцы или ячейки без потери данных   |   Раунд без формулы ...
Супер поиск: Множественный критерий VLookup    VLookup с несколькими значениями  |   VLookup по нескольким листам   |   Нечеткий поиск ....
Расширенный раскрывающийся список: Быстрое создание раскрывающегося списка   |  Зависимый раскрывающийся список   |  Выпадающий список с множественным выбором ....
Менеджер столбцов: Добавить определенное количество столбцов  |  Переместить столбцы  |  Переключить статус видимости скрытых столбцов  |  Сравнить диапазоны и столбцы ...
Рекомендуемые функции: Сетка Фокус   |  Просмотр дизайна   |   Большой Формулный Бар    Менеджер книг и листов   |  Библиотека ресурсов (Авто текст)   |  Выбор даты   |  Комбинировать листы   |  Шифровать/дешифровать ячейки    Отправлять электронные письма по списку   |  Суперфильтр   |   Специальный фильтр (фильтровать жирным шрифтом/курсивом/зачеркиванием...) ...
15 лучших наборов инструментов12 Текст Инструменты (Добавить текст, Удалить символы, ...)   |   50+ График Тип (Диаграмма Ганта, ...)   |   40+ Практических Формулы (Рассчитать возраст по дню рождения, ...)   |   19 Вносимые Инструменты (Вставить QR-код, Вставить изображение из пути, ...)   |   12 Конверсия Инструменты (Числа в слова, Конверсия валюты, ...)   |   7 Слияние и разделение Инструменты (Расширенные ряды комбинирования, Разделить клетки, ...)   |   ... и более

Улучшите свои навыки работы с Excel с помощью Kutools for Excel и почувствуйте эффективность, как никогда раньше. Kutools for Excel предлагает более 300 расширенных функций для повышения производительности и экономии времени.  Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего...

Описание


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

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Comments (16)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi, Hassan,

This vba is amazing, whereas I need one more alternate code for insert single blank row after changes in sequencing numbers. Example:- In column having number series like 1, 2, 3, 5, 6, 9 & etc...
Need to add single blank row between 3 & 5 as well as 6 & 9.
Please can you with this.
This comment was minimized by the moderator on the site
Can the VBA method be adapted to ignore blank cells? I have a file which I need to insert rows in based on two different columns but when I run the macro on the second column I end up with three blank lines where the first macro run inserted rows.
Or can it run on two columns at the same time?
This comment was minimized by the moderator on the site
Hello,
Is very usefull in case i need to insert 1 row, but if i need to insert 145 rows in every time the data change in spwcific column, how can i do it??
This comment was minimized by the moderator on the site
Hi, Hassan,
To insert multiple blank rows when value changes in a specific column, you should apply the following VBA code:

Note: In the below code, you should change the number 99 to your need, for example, when you insert 145 blank rows, you should change the number 99 to 144. Please try it, hope it can help you!

Sub InsertRowsAtValueChange()
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)
Application.ScreenUpdating = False
For i = WorkRng.Rows.Count To 2 Step -1
If WorkRng.Cells(i, 1).Value <> WorkRng.Cells(i - 1, 1).Value Then
Range(WorkRng.Cells(i, 1).EntireRow, WorkRng.Cells(i + 99, 1).EntireRow).Insert
End If
Next
Application.ScreenUpdating = True
End Sub
This comment was minimized by the moderator on the site
Very helpful. The code that skyyang shows above worked perfectly. Just make sure that the data doesn't already have spaces in it.

I don't understand VBA, but I believe if you wanted to add more rows underneath data that already had the spacing, there should be a way to ignore spaces.

Could a line be added to ignore or skip over blank lines? That might make this code more universal and repeatable if needed. Also a delete function that is similar to this may be useful so undo isn't necessary.
This comment was minimized by the moderator on the site
RE: insert blank rows when value changes with vba code
Is there a way that I can save the Range & not have to pick it every time I run it?
This comment was minimized by the moderator on the site
The VBA code worked 1st time and did exactly what I was trying to do. Thank you so much!
This comment was minimized by the moderator on the site
I've been using my own solution for some time.
1. Insert a helper column into Column A
2. In A2, type "if(B2=B1,A1,A1+1)"
3. Copy that formula down to the last row
4. Copy all the populated cells in column A and Paste Special (Values) over them
5. Copy all the cells again and paste them into column A in the first unpopulated cell (e.g. if you have 104 rows of data plus a header row you would paste into cell A106)
6. Click on Data and Remove Duplicates (only on the cells you just pasted in Step 5; not on all rows)
7. Sort all of Column A
8. Delete Column A

Seems like a lot of steps but only takes a few seconds.
This comment was minimized by the moderator on the site
[quote]Hi all thank you!! its awesome , can you guys also let me how to insert 2 rows when the value changes in VBA or through excel.By Hudson[/quote] Please let me know how to insert more than 1 row.
This comment was minimized by the moderator on the site
Hi there, These are almost useful! The first method doesn't work for me because when I follow the steps explicitly, the the data that I delete in the subtotal panes deletes the entire columns that I've sorted. In the second method when I get to the step where I insert sheet rows, the rows are inserted ABOVE the FALSE cells which breaks up the data, but the last selection of every group is then added to the group below. Any advice???
This comment was minimized by the moderator on the site
Hi all thank you!! its awesome , can you guys also let me how to insert 2 rows when the value changes in VBA or through excel.
This comment was minimized by the moderator on the site
Where in the code would I need to modify to include more than one row, I need to add 10 after each break... Thanks
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations