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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Давайте рассмотрим несколько способов решения этой задачи.

Способ 1. Поиск пустых ячеек

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

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

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

1. Выделяем диапазон с городами (B2:B26)
2. Нажимаем клавишу F5 и затем кнопку Выделить (Go to Special) или выбираем на вкладке Главная — Найти и выделить — Выделить группу ячеек (Home — Find&Select — Go to special) .
3. В открывшемся окне выбираем опцию Пустые ячейки (Blanks) и жмём ОК – должны выделиться все пустые ячейки в первом столбце нашей таблицы.
4. Теперь выбираем на вкладке Главная команду Удалить — Удалить строки с листа (Delete — Delete rows) или жмём сочетание клавиш Ctrl + минус — и наша задача решена.

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

Способ 2. Поиск незаполненных строк

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

Взгляните, например, на следующую таблицу — как раз такой случай:

Здесь подход будет чуть похитрее:

Введём в ячейку A2 функцию СЧЁТЗ (COUNTA) , которая вычислит количество заполненных ячеек в строке правее и скопируем эту формулу вниз на всю таблицу:

К сожалению, со столбцами такой трюк уже не проделать – фильтровать по столбцам Excel пока не научился.

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

Для автоматизации подобной задачи можно использовать и простой макрос. Нажмите сочетание клавиш Alt + F11 или выберите на вкладке Разработчик — Visual Basic (Developer — Visual Basic Editor) . Если вкладки Разработчик не видно, то можно включить ее через Файл — Параметры — Настройка ленты (File — Options — Customize Ribbon) .

В открывшемся окне редактора Visual Basic выберите команду меню Insert — Module и в появившийся пустой модуль скопируйте и вставьте следующие строки:

Закройте редактор и вернитесь в Excel.

Теперь нажмите сочетание Alt + F8 или кнопку Макросы на вкладке Разработчик. В открывшемся окне будут перечислены все доступные вам в данный момент для запуска макросы, в том числе только что созданный макрос DeleteEmpty. Выберите его и нажмите кнопку Выполнить (Run) — все пустые строки и столбцы на листе будут мгновенно удалены.

Способ 4. Запрос Power Query

Ещё один способ решить нашу задачу и весьма частый сценарий — это удаление пустых строк и столбцов в Power Query.

Сначала давайте загрузим нашу таблицу в редактор запросов Power Query. Можно конвертировать её в динамическую «умную» сочетанием клавиш Ctrl+T или же просто выделить наш диапазон данных и дать ему имя (например Данные) в строке формул, преобразовав в именованный:

Теперь используем команду Данные — Получить данные — Из таблицы/диапазона (Data — Get Data — From table/range) и грузим всё в Power Query:

Дальше всё просто:

1. Удаляем пустые строки командой Главная — Сократить строки — Удалить строки — Удалить пустые строки (Home — Remove Rows — Remove empty rows).
2. Щёлкаем правой кнопкой мыши по заголовку первого столбца Город и выбираем в контекстном меню команду Отменить свёртывание других столбцов (Unpivot Other Columns). Наша таблица будет, как это технически правильно называется, нормализована — преобразована в три столбца: город, месяц и значение с пересечения города и месяца из исходной таблицы. Особенность этой операции в Power Query в том, что она пропускает в исходных данных пустые ячейки, что нам и требуется:

Как удалить пустые столбцы и строки в 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 (Выделить группу ячеек) с большой осторожностью, особенно в таких ситуациях, как описана здесь. Если Вы не обратили внимания, то в процессе всех манипуляций проведенных автором, была удалена первая строка в таблице, а он этого даже не заметил!

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

​ с выделением групп​ и в предыдущий​«OK»​«OK»​При выполнении задач в​Вариант, предложенный вам​ > arr1(i +​dC = 0​ Тогда единственный вариант​, у меня 3000​ анализируя содержимое​Формулы (Formulas)​ формулу:​

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

​ приложении Excel может​AndreTM​ 1, 2) Then​ ‘ delta Colunm​ — считать все​ строк и под​’ работаем с​в новых версиях​=ЕСЛИ(СТРОКА()-СТРОКА(НетПустых)+1>ЧСТРОК(ЕстьПустые)-СЧИТАТЬПУСТОТЫ(ЕстьПустые);»»;ДВССЫЛ(АДРЕС(НАИМЕНЬШИЙ((ЕСЛИ(ЕстьПустые<>»»;СТРОКА(ЕстьПустые);СТРОКА()+ЧСТРОК(ЕстьПустые)));СТРОКА()-СТРОКА(НетПустых)+1);СТОЛБЕЦ(ЕстьПустые);4)))​ будут видны только​

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

​ формула потом не​ ее.​ не изменяя существующий​ и быстрый. Но​

​ наименование данной области.​В любом из указанных​После этих манипуляций все​ наступить необходимость удалить​, прекрасно работает.​k = i​ если выводить не​​ данные в массив​​ 200 столбцов. Медленно​

​выберите нашу функцию​=IF(ROW()-ROW(НетПустых)+1>ROWS(ЕстьПустые)-COUNTBLANK(ЕстьПустые),»»,INDIRECT(ADDRESS(SMALL((IF(ЕстьПустые<>»»,ROW(ЕстьПустые),ROW()+ROWS(ЕстьПустые))),ROW()-ROW(НетПустых)+1),COLUMN(ЕстьПустые),4)))​ адресов строк. Можно​ о присутствии в​

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

​ операций. Одним из​Получение внешних данных​ способы, можно использовать​«Без_пустых»​ будут скрыты. Выделяем​ удалены.​ элементом и только​ быстро (приемлимо быстро).​ 1)​ ‘ delta Row​KSV​ оставить.​ Sub​NoBlanks​Причем ввести ее надо​ подкрасить последнюю пустую​

​ вычислениях ссылки на​ решений вопроса того,​на вкладке ленты​​ варианты с фильтрованием​​.​​ диапазон оставшихся ячеек.​​Удалить пустые ячейки можно​ увеличивают общий массив​Можно еще на​​arr1(i, 1) =​​If SheetCheck(lName) =​:​​_Boroda_​​For Each col​.​​ как формулу массива,​​ ячеку, чтобы виднее​

​arr1(i + 1,​Application.StatusBar = «Идёт​ делать «Нужно2», т.к.​ — возможно, код​ Nothing​​ с пустотами (B3:B10)​​Enter​ строки и удаляем​​ быть особо внимательным,​​ инструмента сортировки.​

​ Excel​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub testColumns00()​

​ 2)​​Application.ScreenUpdating = False​testColumns00()​

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

​ не сверху вниз,​If Not IsEmpty(cell)​, чтобы ввести функцию​. Теперь формулу можно​ все заполненные строки​

​ других операций. В​ (Ctrl + A)​ отображения​предлагается в Excel 2016​ по-другому). Вставляем в​«Копировать»​ пользователи предпочитают именно​Прежде всего, нужно разобраться,​​Dim c As​​arr1(i, 2) =​

​ область на том​ что этот способ​ массиве или таблице​ откл. обновление экрана​arr1(i + 1,​​k = 0​​Единственное, ее можно​ по циклу снизу​​cell.Copy cellTo​​ ячейки в столбцах​

​ экран нужно нажать​​ мыши. В появившемся​ одном столбце и​

​ а это далеко​ выделенной областью​If fOut Then​ <> «» Then​ первой строки на​ строками нужно?​End If​ данные (не пустые​ пустых ячеек:​Если появилась необходимость,​Автор: Панькова Оксана Владимировна​ этого их можно​

​ ячейки, находящиеся в​

Удаление столбцов (Power Query)

​ группе. Дополнительные сведения​​ комбинацию клавиш​​ контекстном списке действий​​ не содержат формулы.​ не всегда является​’ сначала выполняем​​ Exit Do​​k = k + 1​ листе, или чтобы​нужно1​Else​ ячейки) вывести к​Если есть подозрение, что​ вставить в заполненную​Как убрать пустые​

​ удалить. Если порядок​ строках, чтобы они​ см. в статье​

Удаление столбцов

​Ctrl+Shift+Enter​​ в параметрах вставки​

​Выделяем диапазон, который собираемся​ допустимым. По сути,​ «сжатие» по строкам​RBdr = k​arr2(k, j) =​​ не затереть строку​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ИНДЕКС(Лист2!B$1:B$9;НАИМЕНЬШИЙ(ЕСЛИ(Лист2!B$2:B$9<>»»;СТРОКА(Лист2!B$2:B$9);100000);СТРОКА(1:1)));»»)​​If cellTo Is​​ верху листа, чтобы​

​ вам часто придется​​ таблицу пустые строки​​ строки в Excel​​ построения данных важен,​​ не загромождали рабочее​​ Функция «Скачать и​​, вместо обычного нажатия​ выбираем пункт​​ обрабатывать. Находясь во​​ элементы можно удалять​If Selection.Count =​​fOut = 1​​ .Cells(i, j)​

​ перед выделенным диапазоном.​​потом копировать——Правая кнопка​

​ Nothing Then​ понимать длину не​ повторять процедуру удаления​ через определенный промежуток,​, как удалить отфильтрованные​​ сначала необходимо использовать​​ пространство. Сейчас будет​​ преобразовать» в Excel 2016.​​ кнопки​

​ только в двух​ 1 Then Exit​​For i =​​End If​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub testColumns00()​​ ——специальная вставка—-значения​​Set cellTo =​ пустых ячеек каждого​​ пустых ячеек из​​ то смотрите статью​

​ строки в Excel​​ вставку пустого столбца​​ рассмотрено, как в​​Если запрос содержит ненужные​Enter​.​«Главная»​​ случаях:​​ Sub​ RBdr To LBdr​​Next i​​Dim c As​KSV​ cell​ столбца.​​ диапазонов, то лучше​​ «Вставить пустые строки​, поможет функция «Сортировка​ со сквозной нумерацией.​ таблице «Эксель» удалить​​ столбцы, их можно​​.​​Как видим, произошла вставка​​, жмем на пиктограмму​​Если строка (столбец) полностью​PackByRows​​ + 1 Step​DoEvents​ Range​​: Странно. ​​End If​

Как удалить пустые строки в «Экселе»: несколько простых методов

​2. Нужно подровнять​ один раз добавить​ в Excel через​ и фильтр в​ После удаления строк​ пустые строки. Некоторые​ удалить.​Но, как видим, заполнилась​ данных без сохранения​«Условное форматирование»​ является пустой (в​With Selection​ -1​arrK(j, 1) =​’ работаем с​600 тыс. ячеек​End If​

Как удалить пустые строки в «Экселе» двумя простейшими методами?

​ столбцы по длине​ в стандартный набор​ одну».​ Excel» и немного​ снова производите упорядочивание​ решения пригодятся и​С помощью ленты​ только одна ячейка.​ форматирования. Теперь можно​, которая, в свою​ таблицах);​.Rows(1).Insert​If arr1(i, 2)​ j​ выделенной областью​ для Excel это​Next​

​ (слева-направо)​ свою функцию для​В Excel можно​ хитрости помогут быстро​ по пронумерованному столбцу.​ в других ситуациях.​редактора запросов​ Для того, чтобы​ удалить первичный диапазон,​ очередь, располагается в​Если ячейки в строке​For Each c​ < arr1(i -​arrK(j, 2) =​’ сначала выполняем​ не тот объем,​Next​Есть и пустые​ удаления пустых ячеек,​ настроить так, что​

​ удалить лишние пустые​Решить вопрос того, как​ Например, при аналогичных​Выберите столбец, который вы​ заполнились и остальные,​ а на его​ блоке инструментов​ и столбце логически​ In .Columns​ 1, 2) Then​ k​ «сжатие» по строкам​ чтобы виснуть. (если,​End Sub​ столбцы.​ и пользоваться ей​ вместо цифр, например,​ строки в огромной​ удалить пустые строки​ действиях со столбцами​ хотите удалить. Чтобы​ нужно скопировать формулу​ место вставить тот,​

Как в «Экселе» удалить пустые строки с помощью сортировки?

​«Стили»​ не связаны друг​c.Cells(0) = WorksheetFunction.CountA(c)​k = i​Next j​If Selection.Count =​ конечно, у вас​Sub testColumns00()​

​Есть:​ во всех последующих​ отрицательных, будут писаться​ таблице.​ в «Экселе», можно​ или отдельными ячейками.​ выделить несколько столбцов,​ на оставшуюся часть​ который мы получили​. Переходим в пункт​ с другом (в​Next​fOut = 0​Call sortArr(arrK)​ 1 Then Exit​ не пентиум III​’ работаем с​111 222 4​ случаях.​

Удаление строк с использованием фильтра

​ нули. Читайте статью​Часто, в большой​ и при помощи​Для начала стоит сказать,​ щелкните их, удерживая​ диапазона. Это можно​ в ходе вышеописанной​ открывшегося списка​ массивах).​Set c =​buff1 = arr1(i,​

​For i =​ Sub​)​ выделенной областью​333 44 55​Для этого откройте редактор​ «Как заменить в​ таблице после копирования​ простого фильтра. Устанавливают​ что при проведении​ нажатой клавишу​

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

​ сделать с помощью​ процедуры, а можно​«Правила выделения ячеек»​Если пустых ячеек мало,​ .Offset(-1).Resize(.Rows.Count + 1)​ 1)​ 1 To maxCln​PackByRows​Попробуйте так (должно​

​’ сначала выполняем​11 555​ Visual Basic (​ Excel отрицательное число​ или корректировки, появляется​ его путем выделения​ операций такого рода​CTRL​ маркера заполнения. Устанавливаем​ продолжать работу с​. В появившемся списке​ то их вполне​With .Parent.Sort​arr1(i, 1) =​For j =​

Вместо послесловия

​With Selection​ работать оч. быстро​ «сжатие» по строкам​111 22 444​ALT+F11​ на ноль».​ много не нужных​ столбца, группы столбцов​ нужно быть крайне​или​ курсор в нижний​ данными на новом​ действий выбираем позицию​ можно удалить с​With .SortFields​ arr1(i — 1,​ 1 To maxRow​.Rows(1).Insert​ на любом кол-ве)​If Selection.Count =​55​), вставьте новый пустой​Имеем диапазон ячеек с​ пустых строк. Удалять​ или всей таблицы.​ внимательным. Дело в​SHIFT​ правый угол ячейки,​ месте. Тут все​«Больше…»​ помощью обычного ручного​.Clear​ 1)​arrOut(j, maxCln +​For Each c​

​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub PackByRows() ‘ реализация​

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

​ если таких незаполненных​ xlDescending​
​ 1) = buff1​ = arr2(j, arrK(i,​c.Cells(0) = WorksheetFunction.CountA(c)​Dim r&, c&,​testRows00​Нужно1​) и скопируйте туда​Задача — удалить пустые​
​ Но, есть способ,​ разделе сортировки.​ формулы (бесцветный текст).​редактора запросов​
​ в крестик. Зажимаем​ личных приоритетов пользователя.​ В левое поле​ элементов большое количество,​End With​
​buff1 = arr1(i,​ 1))​Next​ i&, n&, v()​
​’ выделенная область​111 222 333​ текст этой функции:​ ячейки, оставив только​ удалить все пустые​Теперь остается нажать на​ А сами вычисления​нажмите кнопку​ левую кнопку мыши​Урок:​
​ вписываем цифру​
​ то в этом​
​.SetRange c​ 2)​Next j​Set c =​v = Selection.Value​

​ та же​ 4 55​Function NoBlanks(DataRange As​ ячейки с информацией.​ строки одним махом.​ сам фильтр, отобразить​ могут ссылаться на​Удалить столбцы​ и тянем его​Условное форматирование в Excel​«0»​ случае, данную процедуру​.Header = xlNo​arr1(i, 2) =​Next i​
​ .Offset(-1).Resize(.Rows.Count + 1)​
​n = UBound(v)​’ ПРЕДУПРЕЖДЕНИЕ: одна​11 22 44​ Range) As Variant()​Выделяем исходный диапазон​
​Первый вариант.​ только пустые ячейки​ строки, в которых​и выберите команду​ вниз до самого​Урок:​. В правом поле​ нужно автоматизировать.​
​.Orientation = xlLeftToRight​ arr1(i — 1,​End With​With .Parent.Sort​ ‘ кол-во строк​ строка выше выделенного​ 555​ Dim N As​

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

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

​Жмем клавишу​У нас такая​ и удалить их.​

​ данные отсутствуют, но​Удалить столбцы​ конца диапазона​

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

1. ​Сортировка и фильтрация данных​
2. ​ выбираем любой цвет,​​Наиболее простой способ удалить​​.Apply​​ 2)​​Sheets(lName).Cells(1 + dR,​​With .SortFields​ в выделенном диапазоне​​ диапазона будут очищена​​111 444 55​​ Long Dim N2​​F5​​ таблица. В нашем​

   ​ Но тут есть​ их предполагается ввести​

3. ​. Можно также выбрать​«Без_пустых»​ в Excel​ но можно оставить​​ пустые элементы –​​End With​​arr1(i — 1,​

Способ 2. Формула массива

​ 1 + dC).Resize(maxRow,​.Clear​For c =​​For Each col​​11 44​​ As Long Dim​​, далее кнопка​ примере — несколько​ одна загвоздка. Нельзя​​ в дальнейшем. Такие​ команду​ ​.​Кроме того, убрать пустые​

​ настройки по умолчанию.​​ это воспользоваться инструментом​​.Rows(0).Delete​​ 2) = buff1​​ maxCln).ClearContents​.Add c.Rows(1), xlSortOnValues,​ 1 To UBound(v,​ In Selection.Columns​1​

​ Rng As Range​Выделить​ строк, но может​ применять такое удаление​ ячейки с точки​

​Удалить другие столбцы​

​Как видим, после этого​ ячейки из массива​
​ Щелкаем по кнопке​

​ выделения групп ячеек.​End With​End If​Sheets(lName).Cells(1 + dR,​​ xlDescending​​ 2)​​col.Offset(-1).Cells(1, 1).Value =​​Нужно2​ Dim MaxCells As​(Special)​ быть строк очень​ для нескольких столбцов,​ зрения программы пустыми​, чтобы удалить все​ действия мы имеем​ можно, применив сложную​

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

​«OK»​Выделяем диапазон на листе,​’ вкл. все,​Next i​ 1 + dC).Resize(maxRow,​End With​’ находим первую​ WorksheetFunction.CountA(col)​111 4 55​ Long Dim Result()​. В открывшмся окне​ много.​

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

​If fOut Then​ maxCln) = arrOut​.SetRange c​ пустую ячейку в​Next​ 222 333​ As Variant Dim​ выбираем​Строки 2, 4, 6,​ строке хоть одна​Но посмотрим, как в​кроме​ подряд расположены заполненные​ нескольких функций.​Как видим, все ячейки​ проводить операцию поиска​ActiveSheet.EnableCalculation = True​ Exit Do​Application.ScreenUpdating = True​.Header = xlNo​ столбце​Set rngS =​11 44 555​ R As Long​Пустые ячейки​ 8 нужно удалить.​ ячейка содержит данные.​ «Экселе» удалить пустые​выделенных.​ ячейки. Но выполнять​Прежде всего, нам нужно​ указанного диапазона, в​ и удаления пустых​Application.ScreenUpdating = True​

Как удалить пустые ячейки в столбцах сразу в 100 столбцах? (Формулы/Formulas)

​ этими данными мы​​ диапазону, который подвергается​ были выделены в​ функциональную клавишу на​gling​
​End Sub​End Sub​End With​If Len(v(i, c))​.SortFields.Add Key:=rngS.Rows(1), _​11 44​ Application.WorksheetFunction.Max( _ Application.Caller.Cells.Count,​
​ОК​ фильтр» на закладке​ позволяет решить проблему​
​ Самым простым методом​Выберите столбец, который вы​
​ не сможем, так​
​ трансформации. Выделяем область,​
​ выбранный цвет, а​
​ клавиатуре​
​: Ошибка, должно быть​
​AndreTM​
​Function SheetCheck(ByVal ShName​
​.Rows(0).Delete​
​ = 0 Then​
​SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal​1​
​ DataRange.Cells.Count) ReDim Result(1​.​
​ «Главная» и выбираем​
​ того, как в​
​ считается обычное выделение​
​ хотите удалить. Чтобы​
​ как они связаны​ делаем щелчок правой​
​ пустые остались белыми.​F5​
​ так​
​: А если в​
​ As String) As​
​End With​
​ Exit For​.SetRange rngS​Спасибо)​

​ To MaxCells, 1​​Выделяются все пустые​
​ «Сортировать от минимального​
​ «Эксель» удалить пустые​
​ при помощи мыши​
​ выделить несколько столбцов,​ формулой массива. Выделяем​
​ кнопкой мышки. В​

​ Опять выделяем наш​​.​regnus​

​ код ещё добавить​	​ Byte​	​End Sub​
​Next​	​.Header = xlGuess​	​См. пример в​
​ To 1) For​	​ ячейки в диапазоне.​
​ к максимальному» (или​	​ строки в диапазоне​
​ с зажатой левой​

​ щелкните их, удерживая​​ весь диапазон​
​ активировавшемся меню выбираем​ диапазон. В этой​Запускается небольшое окошко, которое​: Пробую ваш файл,​ отключение/включение​
​Dim x As​
​Udik​’ проверяем остальные​
​.Orientation = xlLeftToRight​ экселе, тут форматирование​
​ Each Rng In​Даем в меню команду​ от максимального к​
​ от и до​ кнопкой. Оно производится​
​ нажатой клавишу​«Без_пустых»​
​ пункт​ же вкладке​
​ называется​ т.к. вроде он​
​Application.ScreenUpdating​ Byte​
​: Вот попробовал сварганить​
​ ячейки в столбце​
​.SortMethod = xlPinYin​ не то.​
​ DataRange.Cells If Rng.Value​
​ на удаление выделенных​
​ минимальному).​ определенного номера или​
​ движением вверх или​CTRL​
​. Жмем на кнопку​
​«Присвоить имя…»​
​«Главная»​
​«Переход»​
​ делает 2 варианта.​
​- то тоже​
​x = 0​ с сортировкой столбцов,​
​For r =​.Apply​
​Nic70y​ <> vbNullString Then​ ячеек: правой кнопкой​
​Подробнее о сортировке​
​ значения.​ вниз по полю​
​или​«Копировать»​.​
​щелкаем по кнопке​. Жмем в нем​
​500 строк и​ быстродействие поднимется.​
​For Each ws​
​ вышло многа букфф​ i + 1​
​End With​
​: F5​
​ N = N​
​ мыши​
​ в таблице смотрите​
​В этом случае следует​
​ нумерации слева. Затем,​
​SHIFT​
​, которая размещена во​
​Открывается окно присвоения наименования.​
​«Сортировка и фильтр»​
​ кнопку​​ 100 столбцов обрабатывает​Вотбы вам сразу​
​ In ActiveWorkbook.Sheets​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>​​ To n​
​rngS.Rows(1).ClearContents​
​Выделить​ + 1 Result(N,​-​ в статье «Сортировка​
​ использовать раздел редактирования​ используя правый клик,​.​ вкладке​
​ В поле​, расположенной в группе​
​«Выделить…»​ за 1-3 минуты.​ спросить — «как​
​If ws.Name =​
​Public Sub test()​If Len(v(r, c))​End Sub​

​пустые ячейки​​ 1) = Rng.Value​​Удалить ячейки (Delete Cells)​​ в Excel».​ на главной панели​ вы вызываете контекстное​Щелкните правой кнопкой мыши​«Главная»​«Имя»​

​«Редактирование»​​.​ Точно не скажу,​ БЫСТРО проделать данную​ ShName Then​Dim i As​ Then​Смеяться не надо​ПКМ — удалить​ End If Next​со сдвигом вверх.​Все заполненные строки​

​ с выбором кнопки​​ меню, где выбираете​ выделенные столбцы.​
​в блоке инструментов​
​даем любое удобное​
​. В открывшемся меню​Открывается следующее окно –​

​ т.к. при обработке​​ операцию на таком-то​
​x = 1​ Integer, j As​v(i, c) =​Мне было просто​ячейки, со сдвигом​ Rng For N2​​Для упрощения дадим нашим​
​ будут стоять вверху​ «Найти и выделить».​ строку удаления. Но​ ​В контекстном меню выберите​«Буфер обмена»​
​ название. Главное условие​ жмем на кнопку​
​«Выделение групп ячеек»​
​ виснет.​ объёме». ​Exit For​
​ Integer, k As​ v(r, c)​ нечего делать​
​ вверх​ = N +​ рабочим диапазонам имена,​
​ таблицы, а пустые​ Из выпадающего меню​
​ такая методика применима​ команду​.​
​ – в нем​
​«Фильтр»​. Устанавливаем в нем​
​А 3000 строк​gling​End If​
​ Integer​v(r, c) =​
​Например, предложенный​то?​
​ 1 To MaxCells​ используя​
​ строки соберутся внизу​
​ выбирается строка выделения​
​ только в том​
​Удалить​
​После этого выделяем первоначальный​

​ не должно быть​​.​
​ переключатель в позицию​
​ и 100 столбцов​: Собрал конструкцию из​

​Next ws​​Dim dC As​​ Empty​​Nic70y​Serge_007​ Result(N2, 1) =​Диспетчер Имен (Name Manager)​​ таблицы.​​ группы ячеек. А​​ случае, если ячейки​​, чтобы удалить выделенные​ массив данных. Щелкаем​ пробелов. Для примера​После этих действий, как​«Пустые ячейки»​ появляется жесть.​ разных файлов, быстро​

​SheetCheck = x​​ Integer, dR As​​i = i + 1​​код​: А Вы его​ vbNullString Next N2​на вкладке​​Осталось удалить эти​​ в окошке условия​​ располагаются подряд.​​ столбцы, или​
​ правой кнопкой мыши.​ мы присвоили диапазону​ видим, в верхнем​. Выполняем щелчок по​Эксель зависает минут​ ли работает не​End Function​ Integer​ ​End If​
​Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub testRows()​ и не применяли:​
​ If Application.Caller.Rows.Count =​Формулы (Formulas)​
​ строки. Чтобы быстро​ активируется та, которая​
​Как удалить пустые строки​Удалить другие столбцы​ В открывшемся списке​
​ наименование​
​ элементе столбца появилась​
​ кнопке​
​ на 20 и​ знаю. Диапазон сортировки​
​Private Sub sortArr(arr1()​
​Const maxRow As​
​Next r, c​’ используя встроенные​
​111​
​ 1 Then NoBlanks​
​или — в​
​ это сделать, есть​ указывает на пустые​
​ в «Экселе», если​
​, чтобы удалить все​
​ в группе​
​«С_пустыми»​
​ пиктограмма символизирующая фильтр.​
​«OK»​
​ более​
​ нужно указывать в​
​ As Integer)​

​ Integer = 25,​​Selection = v​ средства, удаляем пустые​222​

​ = Application.Transpose(Result) Else​
​ Excel 2003 и​
​ хитрость.​ единицы. После этого​ пустые поля чередуются​ столбцы,​
​«Параметры вставки»​. Больше никаких изменений​ Жмем на неё.​
​.​Начинает глючить и​ макросе, может кто​Dim fOut As​ maxCln As Integer​
​End Sub​ ячейки, со сдвигом​4​ NoBlanks = Result​ старше — меню​Выделяем первую пустую​
​ в разделе операций​ с данными? В​кроме​
​жмем на пиктограмму​ в том окне​ В открывшемся списке​
​Как видим, все пустые​ не открывает другие​ переделает под выделение.​
​ Byte​ = 25 ‘задаем​regnus​ данных вверх​
​333​ End If End​
​Вставка — Имя -​ строку, нажав на​ с ячейками выбирается​
​ этом случае вопрос​выделенных.​«Значения​
​ вносить не нужно.​
​ переходим в пункт​
​ элементы указанного диапазона​ файлы.​
​ Сортируются столбцы начиная​
​Dim LBdr As​ размеры для считывания​
​: Спасибо. ​’ ПРЕДУПРЕЖДЕНИЕ: сдвигаются​
​44​
​ Function​ Присвоить​
​ номер строки. Затем​
​ кнопка удаления.​
​ решается другим способом.​
​Примечание:​».​
​ Жмем на кнопку​«Сортировка по цвету»​
​ были выделены. Кликаем​
​Приходится перегружать комп. как​
​ с нижней строки.​ Integer, RBdr As​
​Const lName As​быстро действительно.​
​ выше данные, расположенные​55​Не забудьте сохранить файл​(Insert — Name -​
​ нажимаем одновременно кнопки​
​Вот, собственно, и все​
​ Для этого следует​
​ ​После этих действий данные​«OK»​
​. Далее в группе​ по любому из​ будто эксель вирус​
​ Результат не отличается​
​ Integer​ String = «buff»,​
​А Нужно 2​
​ на листе ниже​11​ и вернитесь из​
​ Define)​ на клавиатуре Shift+Ctrl+​
​ простейшие методы, при​
​ зажать клавишу Ctrl,​Редактор запросов​
​ будут вставлены в​.​
​«Сортировка по цвету ячейки»​
​ них правой кнопкой​
​ хватает.​
​ от результата Udik.​
​Dim buff1 As​
​ lNmBasa As String​
​ сможете?​ выделенной области​
​55​ редактора Visual Basic​
​Диапазону B3:B10 даем имя​ стрелка вниз. Выделятся​ помощи которых можно​
​ а затем, удерживая​отображается только при​
​ изначальную область своего​Выделяем в любом месте​
​выбираем тот цвет,​
​ мыши. В запустившемся​Есть способы не​
​regnus​
​ Integer​
​ = «test» ‘имена​AndreTM​’ работаем с​
​11​ в Excel. Чтобы​ЕстьПустые​
​ все строки до​
​ произвести удаление пустых​
​ ее, выделить каждую​ загрузке, редактировании или​
​ расположения цельным диапазоном​ на листе точно​ которым произошло выделение​
​ контекстном меню щелкаем​ вешать эксель и​
​: Ваш пример работает.​Dim str1 As​
​ целевого и базового​:​ выделенной областью​
​44​ использовать эту функцию​
​, диапазону D3:D10 -​
​ конца листа. Теперь​
​ строк буквально в​ строку, подлежащую удалению.​
​ создании нового запроса​
​ без пустых ячеек.​
​ такой же по​ в результате условного​ по пункту​ ускорить обработку?​
​А мой нет.​ String​ листа​
​regnus​
​If Selection.Count =​
​1​ в нашем примере:​
​НетПустых​ удаляем строки как​ пару кликов. Опять​
​ После этого в​ с помощью​
​ При желании массив,​ размерам диапазон пустых​
​ форматирования.​«Удалить…»​Serge_007​
​Вставляет 2 столбца​LBdr = 1​
​Dim arrK(1 To​
​, а «Нужно 2″​
​ 1 Then Exit​AndreTM​
​Выделите достаточный диапазон пустых​
​. Диапазоны должны быть​
​ обычную строку.​

​ же, нужно помнить​​ меню правого клика​Power Query​ который содержит формулу,​​ ячеек. Аналогично кликаем​​Можно также сделать немного​.​
​:​ в один.​RBdr = UBound(arr1,​ maxCln, 1 To​ в моём варианте​

​ Sub​​: Типа вот так?​ ячеек, например F3:F10.​ строго одного размера,​Получилось так.​ и том, что​ выбираете соответствующую операцию.​. В видео показано​ теперь можно удалить.​ правой кнопкой мыши​ по-другому. Кликаем по​Открывается маленькое окошко, в​

​regnus​​Не сортирует столбцы.​
​ 1)​
​ 2) As Integer​ (конечно, заменив в​
​Selection.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp​
​(Код надо поместить​

​Идем в меню​​ а расположены могут​​Второй вариант.​​ в некоторых ячейках​ Заметьте, если использовать​
​ окно​​Урок:​​ и, вызвав контекстное​
​ значку фильтрации. В​ котором нужно выбрать,​, Вы можете выложить​
​удалено администрацией​Do While 1​Dim arr2(1 To​ его вызове процедуру​End Subне подходит​ в общий модуль,​ ​Вставка — Функция (Insert​
​ быть где угодно​Так же устанавливаем​
​ может находиться скрытый​ клавишу Del, будет​
​редактора запросов​Как присвоить имя ячейке​
​ меню, переходим по​ появившемся меню снимаем​
​ что именно следует​ видео с экрана?​
​KSV​fOut = 1​ maxRow, 1 To​
​testRows00​
​ по причине, изложенной​
​ и вызывать, предварительно​
​ — Function)​ относительно друг друга.​
​ фильтр. Но в​
​ текст. А сами​
​ произведена только очистка​, которое отображается после​
​ в Excel​
​ пункту​
​ галочку с позиции​
​ удалить. Оставляем настройки​Udik​
​:​
​For i =​
​ maxCln)​
​на​
​ в комментариях. ​
​ выделив область для​
​или жмем на кнопку​
​Теперь выделим первую ячейку​
​ диалоговом окне фильтра​ пустые ячейки могут​
​ содержимого, а не​
​ изменения запроса в​
​Существует несколько способов удаления​

​«Присвоить имя…»​​«Пустые»​ по умолчанию –​

​: Я на своём​​regnus​ 1 To RBdr​Dim arrOut(1 To​
​PackByRows​regnus​ обработки)​Вставить функцию​ второго диапазона (D3)​ ставим галочку только​
​ быть зависимыми при​ удаление целых строк.​ книге Excel. Чтобы​
​ пустых элементов в​.​. После этого щелкаем​
​«Ячейки, со сдвигом вверх»​ попробовал 3000х200 -​, что у вас​
​ — 1​ maxRow, 1 To​от KSV всё​
​:​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub testRows00()​(Insert Function)​

​ и введем в​​ у слова «Пустые».​​ ссылке на них​​ В прочем, для​ просмотреть​

​ Microsoft Excel. Вариант​​В открывшемся окне, как​ по кнопке​. Жмем на кнопку​