Макрос удалить пустые ячейки в столбце в excel

Обновлено: 06.07.2024

Удаление пустых ячеек в Microsoft Excel

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

Алгоритмы удаления

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

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

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

Способ 1: выделение групп ячеек

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

    Выделяем диапазон на листе, над которым будем проводить операцию поиска и удаления пустых элементов. Жмем на функциональную клавишу на клавиатуре F5.

Выделение диапазона в Microsoft Excel

Переход к выделению в Microsoft Excel

Выделение пустых ячеек в Microsoft Excel

Удаление ячеек в Microsoft Excel

Удаление ячеек со сдвигом вверх в Microsoft Excel

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

Пустые ячейки удалены в Microsoft Excel

Способ 2: условное форматирование и фильтрация

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

    Выделяем диапазон, который собираемся обрабатывать. Находясь во вкладке «Главная», жмем на пиктограмму «Условное форматирование», которая, в свою очередь, располагается в блоке инструментов «Стили». Переходим в пункт открывшегося списка «Правила выделения ячеек». В появившемся списке действий выбираем позицию «Больше…».

Переход к условному форматированию в Microsoft Excel

Окно условного форматирования в Microsoft Excel

Включение фильтра в Microsoft Excel

Применение фильтра в Microsoft Excel

Снятие галочки с фильтра в Microsoft Excel

Копирование в Microsoft Excel

Вставка данных в Microsoft Excel

Данные вставлены в Microsoft Excel

Способ 3: применение сложной формулы

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

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

Переход к присвоению имени в Microsoft Excel

Присвоение имени в Microsoft Excel

Переход к присвоению имени второго диапазона в Microsoft Excel

Присвоение имени второго диапазона в Microsoft Excel

Ввод формулы в Microsoft Excel

Маркер заполнения в Microsoft Excel

Копирование данных в Microsoft Excel

Вставка в Microsoft Excel

Данные вставлены в программе Microsoft Excel

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

Закрыть

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Закрыть

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

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

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

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

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

delete_blanks3.jpg

Способ 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

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

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

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

Удаление ячеек в программе Microsoft Excel

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

Процедура удаления ячеек

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

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

Способ 1: контекстное меню

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

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

Переход к удалению ячеек через контекстное меню в Microsoft Excel

  • Ячейки, со сдвигом влево;
  • Ячейки со сдвигом вверх;
  • Строку;
  • Столбец.

Окошко удаления ячеек в Microsoft Excel

Ячейки удалены со сдвигом вверх в Microsoft Excel

А, если был выбран второй пункт, то со сдвигом влево.

Ячейки удалены со сдвигом влево в Microsoft Excel

Способ 2: инструменты на ленте

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

  1. Выделяем элемент, который следует удалить. Перемещаемся во вкладку «Главная» и жмем на кнопку «Удалить», которая располагается на ленте в блоке инструментов «Ячейки».

Удаление ячейки через кнопку на ленте в Microsoft Excel

Ячейка удалена через кнопку на ленте в Microsoft Excel

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

    Выделяем эту группу элементов горизонтальной направленности. Кликаем по кнопке «Удалить», размещенной во вкладке «Главная».

Удаление горизонтальной группы ячеек через кнопку на ленте в Microsoft Excel

Горизонтальная группа ячеек удалена через кнопку на ленте в Microsoft Excel

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

    Выделяем группу элементов вертикальной направленности. Производим щелчок по кнопке «Удалить» на ленте.

Удаление вертикальной группы ячеек через кнопку на ленте в Microsoft Excel

Вертикальная группа ячеек удалена через кнопку на ленте в Microsoft Excel

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

    Выделяем этот массив и жмем на кнопку «Удалить» на ленте.

Удаление многомерного массива через кнопку на ленте в Microsoft Excel

Многомерный массив удален через кнопку на ленте в Microsoft Excel

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

Переход к удалению многомерного массива через кнопку на ленте в Microsoft Excel

Удаление ячееек со сдвигом вверх в окне удаления ячеек в Microsoft Excel

Многомерный массив удален со сдвигом вверх через кнопку на ленте в Microsoft Excel

Способ 3: использование горячих клавиш

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

Выделение группы элементов в Microsoft Excel

Окно удаления ячеек запущенное с помощью горячих клавиш в Microsoft Excel

Удаление с помощью горячих клавиш выполнено в Microsoft Excel

Способ 4: удаление разрозненных элементов

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

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

Выделение разрозненных элементов в Microsoft Excel

Разрозненные элементы удалены в Microsoft Excel

Способ 5: удаление пустых ячеек

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

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

Выделение таблицы для удаления пустых ячеек в Microsoft Excel

Окно перехода в Microsoft Excel

Окно выделения групп ячеек в Microsoft Excel

Пустые ячейки выделены в Microsoft Excel

Ячейки удалены в Microsoft Excel

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

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

Закрыть

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Закрыть

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


Удалить строку, если ячейка в третьем столбце пустая
Всем привет! Ребята, подскажите, помогите написать макрос: в Excel есть 3 столбца, макрос ссылаясь.

Удалить строку если опеределенная ячейка в ней пуста, если нет - оставить
Добрый день! Подскажите пожалуйста, как сделать: Есть лист excel, в котором находятся данные.

Печатать лист, только если определенная ячейка не пуста
Доброй ночи . Ув.знатоки,в очередной раз потребовалась Ваша помощь. Есть макрос,который.

Решение

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

Решение

Ещё один вариант :


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

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

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

Как удалить все значения в строке, если ячейка пустая?
Есть таблица на 75 строк и более 20-ти столбцов, иногда удаляются данные со стоблца С2:С77, какую.


Удалить строку из Memo, если в ней встречается определенная подстрока
Вообщем есть у нас на форме memo1 и button. В memo1 у нас написан какой-то текс: В пятницу.

условие если пересчиталась определенная ячейка
Условие: если пересчиталась определенная ячейка то выполнить такие-то действия Private Sub.

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