Перейти до основного матеріалу

Як вставити порожні рядки при зміні значення в Excel?

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

Вставляйте порожні рядки, коли значення змінюється за допомогою функції Проміжні підсумки

Вставте порожні рядки, коли значення змінюється за допомогою допоміжних стовпців

Вставляйте порожні рядки, коли значення змінюється за допомогою коду VBA

Вставте певну кількість порожніх рядків, коли значення змінюється за допомогою потужної функції


Вставляйте порожні рядки, коли значення змінюється за допомогою функції Проміжні підсумки

З Разом: Ви можете вставити порожні рядки між даними, коли значення змінюється, як описано нижче:

1. Виберіть діапазон даних, який ви хочете використовувати.

2. Клацання дані > Разом: відкрити Разом: і у вікні Разом: діалогове вікно, будь ласка, виконайте такі параметри:

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

3. Потім натисніть OK, між різними продуктами вставлені рядки проміжних підсумків, а символи контурів відображаються зліва таблиці, див. знімок екрана:

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

5. Потім виберіть дані про проміжний підсумок і натисніть Alt +; комбінації клавіш, щоб вибрати лише видимі рядки, див. знімок екрана:

6. Вибравши лише видимі рядки, натисніть кнопку видаляти клавішу на клавіатурі, і всі проміжні підсумки видалено.

7. Потім клацніть будь-яку іншу комірку і поверніться назад дані > Ungroup > Чіткий контур щоб видалити Контури, див. знімок екрана:

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

9. Нарешті, ви можете видалити стовпець А, як вам потрібно.


Вставте розрив сторінки, порожні рядки, нижню межу або колір заливки, коли значення швидко змінюється

Якщо у вас є Kutools для Excel's Розрізняють відмінності функцію, ви можете швидко вставити розрив сторінки, порожні рядки, нижню межу або колір заливки, коли значення змінюється, як вам потрібно. Дивіться нижче демонстраційну версію.         Клацніть, щоб завантажити Kutools для Excel!


Вставте порожні рядки, коли значення змінюється за допомогою допоміжних стовпців

За допомогою допоміжних стовпців ви можете спочатку вставити формулу, а потім застосувати Знайти і замінити функція, нарешті, вставте порожні рядки між змінними значеннями. Будь ласка, виконайте наступне:

1. В пусту клітинку С3 введіть цю формулу = A3 = A2, а в комірку D4 введіть цю формулу = A4 = A3, див. скріншот:

2. Потім виберіть C3: D4 і перетягніть маркер заповнення до діапазону, до якого ви хочете застосувати формули тез, і ви отримаєте Правда or Помилковий у клітинках див. знімок екрана:

3. А потім натисніть Ctrl + F ключі, щоб відкрити Знайти і замінити діалогове вікно, у спливаючому діалоговому вікні введіть ПОМИЛКОВИЙ в Знайти те, що текстове поле під знайти та натисніть Опції , щоб розгорнути це діалогове вікно, і виберіть Цінності від Заглядати випадаючий список, див. знімок екрана:

4. Натисніть Знайти все , а потім натисніть Ctrl + A щоб вибрати всі результати пошуку, відразу були вибрані всі FALSE комірки, див. знімок екрана:

6. Закрийте Знайти і замінити на наступному кроці ви можете вставити порожні рядки, клацнувши Головна > Insert > Вставити рядки аркуша, а порожні рядки були вставлені в дані при зміні значення на основі стовпця A, див. скріншоти:

7. Нарешті, ви можете видалити допоміжні стовпці C та D, як вам потрібно.


Вставляйте порожні рядки, коли значення змінюється за допомогою коду VBA

Якщо ви втомилися використовувати вищезазначені методи, ось код також може допомогти вам вставити порожні рядки між зміненими значеннями одночасно.

1. Утримуйте клавішу ALT + F11 ключі, щоб відкрити Вікно Microsoft Visual Basic для програм.

2. Клацання Insert > Модуліта вставте наступний код у Вікно модуля.

Код 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, порожні рядки були вставлені між даними, коли значення змінюється на основі стовпця А.


Вставте певну кількість порожніх рядків, коли значення змінюється за допомогою потужної функції

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

Порада:Щоб застосувати це Розрізняють відмінності По-перше, вам слід завантажити Kutools для Excel, а потім швидко та легко застосувати функцію.

після установки Kutools для Excel, будь ласка, зробіть так:

1. Клацання Кутулс > сформований > Розрізняють відмінності, див. скріншот:

2, в Розрізнити відмінності за ключовим стовпцем діалоговому вікні, виконайте такі дії:

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

3. Потім натисніть Ok , а конкретна кількість порожніх рядків було вставлено в дані, якщо значення комірки змінюється, див. скріншоти:

Натисніть, щоб завантажити Kutools для Excel та безкоштовну пробну версію зараз!

Найкращі інструменти продуктивності офісу

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

Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу.  Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...

вкладка kte 201905


Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 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