Как в excel убрать пустые столбцы

Как удалить пустые столбцы? Программное удаление пустых столбцов макросом VBA

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

Стандартные способы удаления пустых столбцов

Удаление вручную

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

Удаление при помощи сортировки

Суть этого способа сводится к тому, чтобы используя сортировку по возрастанию либо по убыванию сгруппировать все пустые столбцы и после этого удалить их, выделив диапазон пустых столбцов. При этом очередность столбцов нарушается и для того, чтобы ее затем восстановить, необходимо предварительно в спомогательной строке ввести нумерацию всех столбцов. Кроме того, необходимо изменить параметры сортировки. По умолчанию в Excel установлен параметр “сортировать строки диапазона”. Для сортировки столбцов необходимо изменить этот параметр на “сортировать столбцы диапазона”.

Удаление при помощи выделения группы ячеек

Для того чтобы удалить пустые столбцы в Excel 2007 и 2010 можно воспользоваться возможностью выделения группы ячеек. Для этого выделяем строку, содержащую пустые ячейки, после чего находим на ленте Excel вкладку “Главная”, в группе “Редактирование” выбираем кнопку “Найти и выделить” и выбираем пункт “Выделение группы ячеек”. В появившемся диалоговом окне “Выделение группы ячеек” включаем опцию “Пустые ячейки” и нажимаем кнопку ОК. В предварительно выделенной строке будут выделены все пустые ячейки. Остается кликнуть правой кнопкой мыши в любом месте выделенного поля и выбрать пункт “Удалить. ” в контекстном меню, а в появившемся диалоговом окне “Удаление ячеек” выбрать пункт “Столбец” и нажать ОК. При этом, строго говоря, будут удалены не пустые столбцы, а столбцы, содержащие пустые ячейки в предварительно выделенной строке.

Нестандартные методы удаления пустых столбцов

Программное удаление макросом VBA

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

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

Автоматическое удаление с использованием надстройки

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

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

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

Источник: macros-vba.ru

Как в Excel удалить пустые строки

Работая с большими объемами данных в таблицах Excel, мы часто сталкиваемся с наличием пустых строк в Excel. Для того чтобы убрать пустые строки есть несколько способов:

  • с помощью сортировки данных;
  • с помощью фильтрации данных;
  • с помощью выделения группы ячеек;
  • с помощью вспомогательного столбца;

Давайте рассмотрим каждый из них.

Как удалить пустые строки в Excel с помощью сортировки

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

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

Для того чтобы отсортировать данные нашей таблицы проделаем следующие шаги:

  • Выделим левой клавишей мыши диапазон данных таблицы, затем на вкладке “Данные” нажмем на кнопку “Сортировка”:

  • В появившемся диалоговом окне сортировки выберем столбец, по которому будут отсортированы данные и укажем их порядок:
Читайте также:  Ширина столбца в excel в мм

  • После этого система автоматически отсортирует строки с пустыми ячейками, и разместит их внизу таблицы:

  • Затем, выделите пустые строки левой клавишей мыши и удалите их, нажав правой кнопкой мыши и выбрав пункт “Удалить”:

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

Как удалить пустые строки в Excel с помощью фильтрации

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

  • Выделим левой клавишей мыши диапазон данных таблицы, затем на вкладке “Данные” нажмем на кнопку “Фильтр”:

Важно! Для корректной фильтрации данных обязательно, перед нажатием кнопки “Фильтр”, выделите область со значениями таблицы, включая пустые строки. Если этого не сделать – фильтрация данных таблицы будет осуществляться до первой пустой строки!

  • Нажмите на выпадающий список фильтра любого из заголовков таблицы и выберите пустые строки:

  • После того как пустые строки выбраны, выделите их и удалите, как это показано на примерах выше.

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

Как убрать пустые строки в Excel с помощью выделения группы ячеек

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

  • Выделите ячейки с данными вашей таблицы;
  • Перейдите в меню “Найти и выделить” и выберите пункт “Выделить группу ячеек”:

  • Во всплывающем окне отметьте пункт “Пустые ячейки”:

  • Система автоматически выделит все пустые ячейки:

  • Для удаления выделенных строк, нажмите на любой из правой кнопкой мыши и выберите пункт “Удалить”:

  • Затем, во всплывающем окне выберите пункт “Строку”:

Внимание! Будьте аккуратны с использованием данного способа! Удаляя выделенные ячейки, есть высокая вероятность того, что вы удалите строки с данными, например, в которых есть только одна пустая ячейка, а в соседних ячейках строки есть данные!

Как удалить пустые строки в Экселе с помощью вспомогательного столбца

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

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

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

Поэтому, чтобы избежать потери данных при удалении пустых строк, создадим справа от таблицы столбец, в который вставим функцию СЧИТАТЬПУСТОТЫ, в ней укажем диапазон всех ячеек каждой строки таблицы и протянем формулу до конца таблицы:

  • Функция СЧИТАТЬПУСТОТЫ ведет подсчет пустых ячеек в указанном диапазоне. Так как столбцов в нашей таблице с данными 4 штуки, то если во вспомогательном столбце мы найдем значение “4” это будет означать, что эта строка в таблице пустая и ее можно удалить. Для этого отфильтруем значения таблицы по вспомогательному столбцу по числу “4” и определим пустые строки:

  • Пустые строки найдены. Теперь их можно выделить и удалить.

Источник: excelhack.ru

Удаление пустых ячеек из диапазона

Постановка задачи

Имеем диапазон ячеек с данными, в котором есть пустые ячейки:

Задача – удалить пустые ячейки, оставив только ячейки с информацией.

Способ 1. Грубо и быстро

  1. Выделяем исходный диапазон
  2. Жмем клавишу F5, далее кнопка Выделить(Special) . В открывшмся окне выбираем Пустые ячейки(Blanks) и жмем ОК.

Выделяются все пустые ячейки в диапазоне.

  • Даем в меню команду на удаление выделенных ячеек: правой кнопкой мыши Удалить ячейки (Delete Cells) со сдвигом вверх.
  • Способ 2. Формула массива

    Для упрощения дадим нашим рабочим диапазонам имена, используя Диспетчер Имен (Name Manager) на вкладке Формулы (Formulas) или – в Excel 2003 и старше – меню Вставка – Имя – Присвоить (Insert – Name – Define)


    Диапазону B3:B10 даем имя ЕстьПустые, диапазону D3:D10 – НетПустых. Диапазоны должны быть строго одного размера, а расположены могут быть где угодно относительно друг друга.

    Теперь выделим первую ячейку второго диапазона (D3) и введем в нее такую страшноватую формулу:

    В английской версии это будет:
    =IF(ROW()-ROW(НетПустых)+1>ROWS(ЕстьПустые)-COUNTBLANK(ЕстьПустые),””,INDIRECT(ADDRESS(SMALL((IF(ЕстьПустые<>“”,ROW(ЕстьПустые),ROW()+ROWS(ЕстьПустые))),ROW()-ROW(НетПустых)+1),COLUMN(ЕстьПустые),4)))

    Причем ввести ее надо как формулу массива, т.е. после вставки нажать не Enter (как обычно), а Ctrl+Shift+Enter. Теперь формулу можно скопировать вниз, используя автозаполнение (потянуть за черный крестик в правом нижнем углу ячейки) – и мы получим исходный диапазон, но без пустых ячеек:

    Способ 3. Пользовательская функция на VBA

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

    Читайте также:  Скрыть столбцы в excel плюсик

    Для этого откройте редактор Visual Basic (ALT+F11), вставьте новый пустой модуль (меню Insert – Module) и скопируйте туда текст этой функции:

    Не забудьте сохранить файл и вернитесь из редактора Visual Basic в Excel. Чтобы использовать эту функцию в нашем примере:

    1. Выделите достаточный диапазон пустых ячеек, например F3:F10.
    2. Идем в меню Вставка – Функция (Insert – Function) или жмем на кнопку Вставить функцию(Insert Function) на вкладке Формулы (Formulas) в новых версиях Excel. В категории Определенные пользователем (User Defined) выберите нашу функцию NoBlanks.
    3. В качестве аргумента функции укажите исходный диапазон с пустотами (B3:B10) и нажмите Ctrl+Shift+Enter, чтобы ввести функцию как формулу массива.

    Источник: www.planetaexcel.ru

    Как удалить пустые столбцы и строки в Excel 2013 – самый быстрый и простой способ

    Если в Вашей таблице Excel присутствует много пустых строк, Вы можете удалить каждую по отдельности, щелкая по ним правой кнопкой мыши и выбирая в контекстном меню команду Delete (Удалить), но для большой таблицы это займёт уйму времени! Существует более простой и быстрый способ удалить пустые столбцы или строки в Excel.

    Сначала займёмся пустыми строками. Потом удалим пустые столбцы. Это делается практически точно так же.

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

    Нажмите Find & Select (Найти и выделить) в разделе Editing (Редактирование) на вкладке Home (Главная) и в раскрывшемся меню выберите Go To Special (Выделить группу ячеек).

    В диалоговом окне Go To Special (Выделить группу ячеек) выберите пункт Blanks (Пустые) и нажмите ОК.

    В выбранной Вами области будут выделены все пустые ячейки.

    В разделе Cells (Ячейки) на вкладке Home (Главная) нажмите Delete (Удалить) и в выпадающем меню выберите Delete Sheet Rows (Удалить строки с листа).

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

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

    Повторяем те же шаги: нажмите Find & Select (Найти и выделить) в разделе Editing (Редактирование) на вкладке Home (Главная) и в раскрывшемся меню выберите Go To Special (Выделить группу ячеек).

    В диалоговом окне Go To Special (Выделить группу ячеек) выберите пункт Blanks (Пустые) и нажмите ОК.

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

    В разделе Cells (Ячейки) на вкладке Home (Главная) нажмите Delete (Удалить) и в выпадающем меню выберите Delete Sheet Columns (Удалить столбцы с листа).

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

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

    Примечание переводчика: Хоть этот материал и является переводом зарубежной статьи, мы в любом случае обязаны предупредить Вас. Выше описан яркий пример того, как не нужно удалять пустые строки и столбцы в Excel. Используйте опцию Go To Special (Выделить группу ячеек) с большой осторожностью, особенно в таких ситуациях, как описана здесь. Если Вы не обратили внимания, то в процессе всех манипуляций проведенных автором, была удалена первая строка в таблице, а он этого даже не заметил!

    Источник: office-guru.ru

    Как удалить пустые строки в Excel быстрыми способами

    При импорте и копировании таблиц в Excel могут формироваться пустые строки и ячейки. Они мешают работе, отвлекают.

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

    Как в таблице Excel удалить пустые строки?

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

    Пример 1 . Сортировка данных в таблице. Выделяем всю таблицу. Открываем вкладку «Данные» – инструмент «Сортировка и фильтр» – нажимаем кнопку «Сортировка». Или щелкаем правой кнопкой мыши по выделенному диапазону и делаем сортировку «от минимального к максимальному».

    Пустые строки после сортировки по возрастанию оказываются внизу диапазона.

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

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

    Читайте также:  В excel показать скрытые столбцы

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

    Пример 3 . Выделение группы ячеек. Выделяем всю таблицу. В главном меню на вкладке «Редактирование» нажимаем кнопку «Найти и выделить». Выбираем инструмент «Выделение группы ячеек».

    В открывшемся окне выбираем пункт «Пустые ячейки».

    Программа отмечает пустые ячейки. На главной странице находим вкладку «Ячейки», нажимаем «Удалить».

    Результат – заполненный диапазон «без пустот».

    Внимание! После удаления часть ячеек перескакивает вверх – данные могут перепутаться. Поэтому для перекрывающихся диапазонов инструмент не подходит.

    Полезный совет! Сочетание клавиш для удаления выделенной строки в Excel CTRL+«-». А для ее выделения можно нажать комбинацию горячих клавиш SHIFT+ПРОБЕЛ.

    Как удалить повторяющиеся строки в Excel?

    Чтобы удалить одинаковые строки в Excel, выделяем всю таблицу. Переходим на вкладку «Данные» – «Работа с данными» – «Удалить дубликаты».

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

    После нажатия ОК Excel формирует мини-отчет вида:

    Как удалить каждую вторую строку в Excel?

    Проредить таблицу можно с помощью макроса. Например, такого:

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

    1. В конце таблицы делаем вспомогательный столбец. Заполняем чередующимися данными. Например, «о у о у о у» и т.д. Вносим значения в первые четыре ячейки. Потом выделяем их. «Цепляем» за черный крестик в правом нижнем углу и копируем буквы до конца диапазона.
    2. Устанавливаем «Фильтр». Отфильтровываем последний столбец по значению «у».
    3. Выделяем все что осталось после фильтрации и удаляем.
    4. Убираем фильтр – останутся только ячейки с «о».

    Вспомогательный столбец можно устранить и работать с «прореженной таблицей».

    Как удалить скрытые строки в Excel?

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

    В тренировочной таблице скрыты ряды 5, 6, 7:

    Будем их удалять.

    1. Переходим на «Файл»-«Сведения»-«Поиск проблем» – инструмент «Инспектор документов».
    2. В отрывшемся окне ставим галочку напротив «Скрытые строки и столбцы». Нажимаем «Проверить».
    3. Через несколько секунд программа отображает результат проверки.
    4. Нажимаем «Удалить все». На экране появится соответствующее уведомление.

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

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

    Источник: exceltable.com

    Удаляем в EXCEL пустые строки в таблице

    Часто, особенно при импорте данных в EXCEL , на листе могут формироваться таблицы с ПОЛНОСТЬЮ пустыми строками. Научимся быстро удалять эти ненужные строки, которые в дальнейшем могут затруднить работу с таблицей.

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

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

    Сортировка

    Самым простым способом удаления пустых строк является сортировка таблицы: выделяем диапазон, в котором содержится таблица ( А2:C17 ), затем вызываем команду меню Данные/ Сортировка и фильтр/ Сортировка от минимального к максимальному или просто /Сортировка .

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

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

    Фильтр

    Для нахождения пустых ячеек можно также воспользоваться Фильтром ( Главная/ Редактирование/ Сортировка и фильтр/ Фильтр ). Выбираем подходящий столбец, отображаем только пустые ячейки, выделяем строки не содержащие данных и удаляем их.

    Выделение группы ячеек

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

    • выделяем диапазон А2:C17 ;
    • выбираем пункт меню Главная/ Редактирование/ Найти и выделить/ Выделение группы ячеек… ,

    • выберите пункт пустые ячейки в разделе Выделить ;

    • далее нажимаем маленькую стрелочку в меню Главная/ Ячейки/ Удалить ;
    • выберем Удалить ячейки (удалить ячейки, со сдвигом вверх) .

    Здесь нужно быть аккуратным: если таблица сдержит помимо пустых строк, строки с заполненными и пустыми ячейками, то часть ячеек перескочит со своих строк на другие, что испортит таблицу. Если таблица в формате EXCEL 2007 , то EXCEL не даст испортить таким образом таблицу: появится сообщение «Данная команда неприменима для перекрывающихся диапазонов».

    СОВЕТ: Стоит помнить, что ячейки, содержащие формулы (даже если это ссылки на пустые ячейки), не считаются пустыми по определению.

    Источник: excel2.ru