Як видалити порожні рядки в Excel швидкими способами

При імпорті та копіюванні таблиць в Excel можуть формуватися порожні рядки та комірки. Вони заважають роботі, відволікають.

Деякі формули можуть працювати некоректно. Використовувати ряд інструментів щодо неповно заповненого діапазону неможливо. Навчимося швидко видаляти порожні осередки наприкінці чи середині таблиці. Використовуватимемо прості засоби, доступні користувачеві будь-якого рівня.

Як у таблиці Excel видалити порожні рядки?

Щоб показати на прикладі, як видалити зайві рядки, для демонстрації порядку дій візьмемо таблицю з умовними даними:

приклад 1 . Сортування даних у таблиці. Виділяємо всю таблицю. Відкриваємо вкладку «Дані» – інструмент «Сортування та фільтр» – натискаємо кнопку «Сортування». Або клацаємо правою кнопкою миші по виділеному діапазону та робимо сортування «від мінімального до максимального».

Порожні рядки після сортування за зростанням виявляються внизу діапазону.

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

Приклад 2 . Фільтр. Діапазон має бути відформатований як таблиця із заголовками. Виділяємо "шапку". На вкладці "Дані" натискаємо кнопку "Фільтр" ("Сортування та фільтр"). Праворуч від назви кожного стовпця з'явиться стрілочка вниз. Натискаємо – відкривається вікно фільтрації. Знімаємо виділення навпроти імені «Порожні».

У такий же спосіб можна видалити порожні комірки в рядку Excel. Вибираємо потрібний стовпець та фільтруємо його дані.

Приклад 3 . Виділення групи осередків. Виділяємо всю таблицю.У головному меню на вкладці «Редагування» натискаємо кнопку «Знайти та виділити».

У вікні вибираємо пункт «Порожні осередки».

Програма відзначає порожні осередки.

Результат – заповнений діапазон без порожнеч.

Увага! Після видалення частина осередків перескакує вгору – дані можуть переплутатися.

Корисна порада! Поєднання клавіш для видалення виділеного рядка в Excel CTRL+«-».

Як видалити рядки, що повторюються в Excel?

Щоб видалити однакові рядки в Excel, виділяємо всю таблицю.

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

Після натискання ОК Excel формує міні-звіт:

Як видалити кожен другий рядок у Excel?

Прорідити таблицю можна за допомогою макросу.

А можна ручками. Пропонуємо простий спосіб, доступний кожному користувачеві.

1. В кінці таблиці робимо допоміжний стовпець. Заповнюємо даними, що чергуються. до кінця діапазону.
2. Встановлюємо "Фільтр". Відфільтровуємо останній стовпець за значенням "у".
3. Виділяємо все, що залишилося після фільтрації і видаляємо.
4. Забираємо фільтр – залишаться тільки осередки з «про».

Допоміжний стовпець можна усунути та працювати з «прорідженою таблицею».

Як видалити приховані рядки в Excel?

Одного разу користувач приховав інформацію в рядках, щоб вона не відволікала від роботи. Думав, що згодом дані ще знадобляться. Чи не знадобилися – приховані рядки можна видалити: вони впливають на формули, заважають.

У тренувальній таблиці приховані ряди 5, 6, 7:

1. Переходимо на "Файл"-"Відомості"-"Пошук проблем" – інструмент "Інспектор документів".
2. У вікні, що відчинилося, ставимо галочку навпроти «Приховані рядки і стовпці». Натискаємо "Перевірити".
3. За кілька секунд програма відображає результат перевірки.
4. Натискаємо «Видалити все». На екрані з'явиться відповідне повідомлення.

В результаті виконаної роботи приховані осередки видалені, нумерація відновлена.

Таким чином, прибрати порожні, повторювані або приховані осередки таблиці можна за допомогою вбудованого функціоналу Excel.

• Створити таблицю
• Форматування
• Функції Excel
• Формули та діапазони
• Фільтр та сортування
• Діаграми та графіки
• Зведені таблиці
• Друк документів
• Бази даних та XML
• Можливості Excel
• Налаштування параметри
• Уроки Excel
• Макроси VBA
• Завантажити приклади

Швидко видалити порожні стовпці в Excel У цьому посібнику ви дізнаєтеся, як легко видалити порожні стовпці в Excel за допомогою макросу, формули і навіть простого натискання кнопки. Як не банально це може здатися, видалення порожніх стовпців в Excel не може бути виконане одним клацанням миші. Це теж не можна зробити у два кліки. Однак зробити це можна швидко та точно. Вам безперечно слід уникати перспективи поглянути на всі стовпці на аркуші і вручну видалити прогалини.На щастя, Microsoft Excel пропонує багато різних функцій, і, використовуючи їх творчо, ви можете виконати свою роботу! Отже, як ми збираємось вирішити проблему видалення порожніх стовпців? Швидкий, але неправильний спосіб видалити порожні стовпці. Коли справа доходить до видалення прогалин у таблиці в Excel (чи то порожні комірки, рядки або стовпці), багато користувачів покладаються на команду «Знайти та вибрати». Ніколи не робіть цього на простирадлах! Цей метод (Знайти і виділити> Група осередків> Пробіли) знаходить і вибирає всі порожні комірки в діапазоні: Якщо тепер клацнути правою кнопкою миші вибрані комірки і вибрати "Видалити"> "Весь стовпець", всі стовпці, що містять хоча б одну порожню комірку, будуть втрачені! Якщо ви зробили це випадково, натисніть Ctrl+Z, щоб повернути все на свої місця. Тепер, коли ви знаєте неправильний спосіб видалення порожніх стовпців в Excel, погляньмо, як це зробити правильно.

Як видалити порожні стовпці в Excel за допомогою VBA

Потужні користувачі Excel знають це практичне правило: щоб не витрачати годинник на щось вручну, витратите кілька хвилин на написання макросу, який зробить це автоматично. Наведений нижче макрос VBA видалить усі порожні стовпці у вибраному діапазоні. І робить це безпечно: видаляються лише абсолютно порожні. Якщо стовпець містить значення хоча б в одному осередку, навіть порожній рядок («»), що повертається будь-якою формулою, залишиться на аркуші.Public Sub DeleteEmptyColumns () Dim SourceRange As Range Dim CompleteColumn As Range On Error Resume Next Set SourceRange = Application.InputBox (_ "Вибрати діапазон:", "Видалити порожні стовпці", _ Application.Selection.Address, Тип: = 8) Якщо ні (SourceRange – ніщо), то Application.ScreenUpdating = False Для i = SourceRange.Columns.Count До 1 Крок -1 Встановіть CompleteColumn = SourceRange.Cells (1, i) .EntireColumn Якщо Application.WorksheetFunction.CountA (CompleteColum Потім Ви можете замінити повідомлення англійською на російську в тексті макросу в шостому рядку. Однак я рекомендую залишити все як є, щоб уникнути проблем із відображенням російських символів у VBA.

Як використовувати макрос видалення порожніх стовпців

1. Натисніть Alt + F11, щоб відкрити редактор Visual Basic.
2. У рядку меню натисніть Вставити> Модуль).
3. Вставте наведений вище код у вікно, що з'явилося. Клацніть піктограму диска та збережіть проект.
4. Якщо вікно модуля ще відкрито, натисніть F5, щоб запустити макрос. Далі для дзвінка програми використовуйте комбінацію Alt+F8.
5. Коли з'явиться спливаюче діалогове вікно, перейдіть до листа, виберіть потрібний діапазон і натисніть OK:

Порожні стовпці у вибраному діапазоні будуть видалені:

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

Видалення порожніх стовпців в Excel за допомогою формул.

Вищезгаданий макрос швидко та легко видаляє порожні стовпці.Але якщо ви належите до типу людей, які «тримають все під контролем» (наприклад, я :), ви можете візуально подивитися, які будуть видалені. У цьому прикладі ми спочатку визначимо порожні стовпці за допомогою формули, щоб ми могли їх швидко побачити, а потім видалимо всі або деякі з них.

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

Після збереження резервної копії в безпечному місці виконайте такі дії:

Крок 1. Вставте новий рядок.

Додайте новий рядок вгорі таблиці. Для цього клацніть правою кнопкою миші заголовок першого рядка та оберіть «Вставити». Не турбуйтеся про порушення структури таблиці та розташування ваших даних – ви можете видалити їх пізніше.

Крок 2. Знайдіть порожні стовпці.

У самій лівій комірці щойно доданого рядка введіть таку формулу:

Потім скопіюйте формулу рядково при необхідності, перетягнувши маркер заповнення праворуч.

Логіка формули дуже проста: COUNT перевіряє кількість порожніх осередків у стовпці, від рядка 2 до рядка 1048576, що є максимальною кількістю рядків у Excel 2019-2007. Ви порівнюєте це число з нулем, і результатом є ІСТИНА в пробілах і брехня, якщо є хоча б один непустий осередок. Використовуючи відносні посилання, формула правильно налаштовується кожного стовпця, у якому вона копіюється.

Якщо ви налаштовуєте лист для когось іншого, ви можете помітити його більш явним та зрозумілим способом. Немає проблем, це легко зробити за допомогою функції ЯКЩО, приблизно так:

= ЯКЩО (РАХУНОК (A2: A1048576) = 0; «Пусто»; «Не порожньо»)

Формула тепер явно вказує, які стовпці порожні, а які ні.

У порівнянні з макросом цей метод пропонує більшу гнучкість щодо того, які стовпці слід вважати порожніми. У цьому вся прикладі ми перевіряємо всю таблицю, включаючи рядок заголовка. Це означає, що якщо стовпець містить лише заголовок, він не вважається порожнім і не видаляється. Якщо ви хочете перевірити лише рядки даних, ігноруючи заголовки, виключіть рядки заголовків із цільового діапазону. В результаті це буде, наприклад, A3: A1048576. Заголовок, що має заголовок, але не містить інших даних, вважатиметься порожнім і, отже, має бути видалено.

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

Крок 3. Видаліть порожні стовпці.

Тепер ви можете просто вибрати ті стовпці, в яких у першому рядку написано «Пусто» (щоб вибрати кілька стовпців одночасно, утримуйте клавішу Ctrl, натискаючи на їхні літери). Потім клацніть правою кнопкою миші один із вибраних стовпців і виберіть у контекстному меню команду «Видалити:

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

1. Виберіть верхній рядок формул, перейдіть на вкладку «Дані» > групу «Сортування та фільтр» та натисніть кнопку «Сортування» .
2. У вікні з попередженням виберіть «Розгорнути виділення» і натисніть «Замовити…».

1. З'явиться діалогове вікно «Сортування», в якому ви натиснете кнопку «Параметри…», виберіть «Стовпці діапазону» та натисніть «OK» .

1. Налаштуйте лише один рівень сортування, як показано нижче, і натисніть кнопку ОК:
   • Сортувати за: Рядок 1
   • Сортування: значення осередків
   • Порядок: від А до Я

В результаті порожні стовпці будуть переміщені в праву частину робочого аркуша, наприкінці таблиці:

Якщо ви вибрали порядок сортування від Я до А, перемістіть їх у ліву частину листа на початок.

1. Виділіть усі порожні стовпці наприкінці таблиці. Для цього клацніть першу літеру, натисніть Shift і натисніть останню літеру.
2. Клацніть правою кнопкою миші виділення та виберіть «Видалити» у спливаючому меню.

Виконано! Ви позбавилися порожніх стовпців і тепер ніщо не заважає вам видалити верхній рядок з формулами.

Найшвидший спосіб видалити порожні стовпці в Excel.

На початку цього посібника я написав, що в Excel не існує способу обробки порожніх стовпців одним клацанням миші. Насправді, це не так. Довелося сказати, що в самому Excel таких стандартних функцій немає. Користувачі доповнення Ultimate Suite можуть видалити їх практично автоматично, в пару кліків

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

Відео: видалення порожніх рядків

Видалити порожні рядки

При видаленні порожніх рядків з набору даних важливо бути обережними, оскільки деякі часто запропоновані методи можуть випадково видалити рядки, що містять дані.Наприклад, в Інтернеті можна знайти дві популярні поради (які також представлені в цьому посібнику нижче):

• З використанням "Перейти до спеціального", щоб вибрати порожні комірки, а потім видалити рядки цих вибраних порожніх осередків.
• Подивіться на графік ФІЛЬТР функція фільтрації порожніх осередків у ключовому стовпці та подальшого видалення порожніх рядків у відфільтрованому діапазоні.

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

Щоб уникнути таких ненавмисних видалень, рекомендується використовуйте один із наступних чотирьох методів щоб точно видалити порожні рядки.

>> Видалити порожні рядки за допомогою допоміжного стовпця

Крок 1. Додайте допоміжний стовпець та використовуйте функцію COUNTA.

1. У самому правому наборі даних додайте " Помічник і використовуйте наведену нижче формулу в першому осередку стовпця:

Увага: У формулі A2:C2 – це діапазон, в якому ви хочете підрахувати непусті осередки

Крок 2. Відфільтруйте порожні рядки по допоміжному стовпцю.

1. Клацніть будь-яку комірку допоміжного стовпця, виберіть Дані >ФІЛЬТР .
2. Потім натисніть на стрілка фільтра і тільки перевірити 0 у розширеному меню та натисніть OK . Тепер усі порожні рядки відфільтровані.

Крок 3. Видаліть порожні рядки.

Виберіть порожні рядки (клацніть номер рядка і перетягніть вниз, щоб вибрати всі порожні рядки), а потім клацніть правою кнопкою миші, щоб вибрати Видалити рядок з контекстного меню (або ви можете використовувати ярлики Ctrl + – ).

Крок 4. Виберіть «Фільтр» у групі «Сортування та фільтр», щоб очистити застосований фільтр.

Результат:

Увага: Якщо вам більше не потрібний допоміжний стовпець, видаліть його після фільтрації.

>> Видаліть порожні рядки за допомогою Kutools за 3 секунди

Для швидкого та легкого видалення порожніх рядків з вашого вибору найкращим рішенням є використання Видалити порожні рядки особливість Kutools for Excel . Ось як:

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

1. Виберіть діапазон, з якого потрібно видалити порожні рядки.
2. Натисніть Кутулс >Видалити >Видалити порожні рядки >У вибраному діапазоні .
3. Виберіть потрібний варіант, як вам потрібно, та натисніть OK у спливаючому діалоговому вікні.

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

>> Видалити порожні рядки вручну

Якщо потрібно видалити кілька порожніх рядків, ви можете видалити їх вручну.

Крок 1. Виберіть пусті рядки

Натисніть номер рядка, щоб вибрати один порожній рядок. Якщо є кілька порожніх рядків, утримуйте Ctrl та натисніть на номери рядків один за одним, щоб вибрати їх.

Крок 2. Видаліть порожні рядки.

Після вибору порожніх рядків клацніть правою кнопкою миші та виберіть Видалити з контекстного меню (або ви можете використовувати ярлики Ctrl + – ).

Результат:

>> Видалити порожні рядки за допомогою VBA

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

Крок 1Скопіюйте VBA у вікно Microsoft Visual Basic для програм.

1. Активуйте аркуш, з якого ви бажаєте видалити порожні рядки, потім натисніть інший + F11 ключі.
2. У вікні, натисніть Вставити >Модулі .
3. Потім скопіюйте та вставте один із наведених нижче кодів у порожній новий модуль. Код 1: видалити порожні рядки з активного робочого листа

Sub RemoveBlankRows() 'UpdatebyExtendoffice Dim wsheet As Worksheet Dim lastRow As Long Dim i As Long ' Натиснути worksheet variable to the active sheet Set wsheet = ActiveSheet ' Get the row of the data in the worksheet lastRow = w. .Count, 1).End(xlUp).Row ' Loop through each row in reverse order For i = lastRow To 1 Step -1 ' 0 Then ' If the row is blank, delete it wsheet.Rows(i).Delete End If Next i End Sub 

Sub RemoveBlankRowsInRange() 'UpdatebyExtendoffice Dim sRange As Range Dim row As Range ' Повідомити користувача, щоб вибрати range On Error Resume Next Set sRange = Application.InputBox(prompt:="Select a range", Title:="Kuto , Тип:=8) ' Check if a range is selected If Not sRange Is Nothing Then ' Loop through each row in reverse order For Each row in sRange.Rows ' Check if the entire row is blank If WorksheetFun. 0 Then ' If the row is blank, delete it row.Delete End If Next row Else MsgBox "Не range selected. Please select a range and run the macro again." 

Крок 2. Запустіть код та видаліть порожні рядки

Натисніть Кнопка Run або натисніть F5 ключ для запуску коду.

• Якщо ви використовуєте код 1 для видалення порожніх рядків на активному аркуші, після запуску коду всі порожні рядки на аркуші будуть видалені.
• Якщо ви використовуєте код 2 для видалення порожніх рядків з вибору, після запуску коду з'явиться діалогове вікно, виберіть діапазон, з якого потрібно видалити порожні рядки в діалоговому вікні, потім натисніть OK.

Результати:

Code1: видалити порожні рядки в активному аркуші

Code2: видалити порожні рядки у виборі

Видалити рядки, що містять порожні комірки

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

>> Видаліть рядки, що містять порожні комірки, за допомогою кнопки «Перейти до спеціального».

Функція Go To Special рекомендується для видалення порожніх рядків. Це може бути корисним інструментом, коли вам потрібно видалити рядки, що містять хоча б одну порожню комірку.

Крок 1: оберіть порожні комірки в діапазоні

1. Виберіть діапазон, з якого потрібно видалити порожні рядки, виберіть Головна >Знайти та вибрати >Перейти до спеціального . Або ви можете безпосередньо натиснути F5 ключ для включення Перейти до діалогове вікно та клацніть Особливий кнопка для перемикання на Перейти доОсобливий Діалог.
2. У Перейти до спеціального діалогу, виберіть Прогалини варіант та натисніть OK . Тепер усі порожні комірки у вибраному діапазоні вибрано.

Крок 2. Видаліть рядки, що містять порожні комірки.

1. Клацніть правою кнопкою миші будь-яку виділену комірку та виберіть Видалити з контекстного меню (або ви можете використовувати ярлики Ctrl + – ).
2. У Видалити діалогу, виберіть Весь ряд варіант та натисніть OK .

Результат:

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

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

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

Крок 1. Відфільтруйте порожні комірки у ключовому стовпці.

1. Виберіть набір даних, натисніть Дані вкладку, перейдіть до Сортувати та фільтрувати групу, натисніть ФІЛЬТР щоб застосувати фільтр до набору даних.
2. Натисніть стрілка фільтра для ключового стовпця, який потрібно використовувати для видалення рядків. У цьому прикладі, ID стовпець є ключовим стовпцем, і перевіряється тільки Прогалини з розширеного меню. Натисніть OK . Тепер усі порожні осередки у ключовому стовпці відфільтровані.

Крок 2. Видаліть рядки

Виберіть рядки, що залишилися (клацніть по номеру рядка і перетягніть вниз, щоб вибрати всі порожні рядки), потім клацніть правою кнопкою миші, щоб вибрати Видалити рядок у контекстному меню (або можна використовувати ярлики Ctrl + – ). І натисніть OK у спливаючому діалоговому вікні.

Крок 3. Виберіть «Фільтр» у групі «Сортування та фільтр», щоб очистити застосований фільтр.

Результат:

Увага: Якщо потрібно видалити порожні рядки на основі двох або більше ключових стовпців, повторіть крок 1, щоб відфільтрувати порожні рядки в ключових стовпцях по одному, потім видаліть рядки з порожніми осередками.