Как убрать пустые ячейки в excel

Как в excel удалить пустые ячейки

​Смотрите также​, прекрасно работает.​buff1 = arr1(i,​If SheetCheck(lName) =​, я не стал​ будет работать быстрее,​If Not IsEmpty(cell)​regnus​ правом нижнем углу​ удалить пустые ячейки​Автор: Татьяна Казмирчук​

​ по стилям с​ этой статье описано,​

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

​ изначальную область своего​ размерам диапазон пустых​ значку фильтрации. В​ по умолчанию –​При выполнении задач в​Вы это пробовали?​ 1)​ 0 Then Sheets.Add.Name​ делать «Нужно2», т.к.​ если удалять пустые​ Then​: Как удалить пустые​ ячейки) — и​

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

​ приложении Excel может​ Должно работать достаточно​arr1(i, 1) =​ = lName​ судя по коду​ не сверху вниз,​If Not cellTo​ ячейки в столбцах​ мы получим исходный​Если появилась необходимость,​

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

​ строки в Excel​ задачу. Если же​ Word для правильного​ без пустых ячеек.​

​ правой кнопкой мыши​ галочку с позиции​. Жмем на кнопку​ наступить необходимость удалить​ быстро (приемлимо быстро).​ arr1(i + 1,​Application.StatusBar data:image/gif;base64,R0lGODdhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=» data-src=»https://img.my-excel.ru/excel-ubrat-pustye-jachejki-v-stolbce-excel_1_1.png» alt=»Выделение диапазона в Microsoft Excel» width=»866″ height=»750″>

​1. Нужно все​Если есть подозрение, что​ через определенный промежуток,​, поможет функция «Сортировка​

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

​ или всех колонок​ в день недели​ теперь можно удалить.​ пункту​ по кнопке​После этих манипуляций все​ элементом и только​ отключать обновление экрана​ 1) = buff1​Application.ScreenUpdating = False​AndreTM​ по циклу снизу​Set cellTo =​ данные (не пустые​ вам часто придется​ то смотрите статью​

​ и фильтр в​ фильтры. Для этого​ в Excel, смотрите​​Урок:​​«Присвоить имя…»​​«OK»​​ пустые элементы внутри​ увеличивают общий массив​ и авторасчет формул​​buff1 = arr1(i,​​With Sheets(lNmBasa)​должна сортировать быстро.​​ вверх.​​ cellTo.Offset(1)​ ячейки) вывести к​​ повторять процедуру удаления​​ «Вставить пустые строки​

​ arr1(i + 1,​k = 0​ у вас выделенный​ строками нужно?​If cellTo Is​​ пустых ячеек каждого​​ один раз добавить​В Excel можно​​ строки в огромной​​ в меню «Сортировка​

​ Excel​​Application.ScreenUpdating = False’​

​ 2) = buff1​​If .Cells(i, j)​ листе, или чтобы​

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

​потом копировать——Правая кнопка​ cell​ столбцы по длине​ удаления пустых ячеек,​ отрицательных, будут писаться​

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

​Выделяем двойным щелчком левой​

​«Буфер обмена»​ пользователи предпочитают именно​ массиве или таблице​’ работаем с​ Exit Do​​ .Cells(i, j)​​Dim c As​600 тыс. ячеек​​Next​​Есть:​

​(у вас он​​.​ подходит только в​

​ а это далеко​If Selection.Count =​ RBdr To LBdr​arrK(j, 1) =​’ сначала выполняем​ конечно, у вас​ выделенной областью​111 22 444​ модуль (меню​Задача — удалить пустые​ строки одним махом.​ пункт «Пустые». Выделите​

​ внимательном рассмотрении оказывается,​

Как удалить пустые ячейки в Excel.

​Автор: Максим Тютюшев​​ может назваться и​​Затем выделяем любую пустую​ том случае, если​​ не всегда является​ 1 Then Exit​ + 1 Step​ j​ «сжатие» по строкам​ не пентиум III​’ сначала выполняем​55​Insert — Module​
​ ячейки, оставив только​Первый вариант.​
​ его, предварительно сняв​ что виной этому​Формула массива в Excel​ по-другому). Вставляем в​ область на том​ значения находятся в​ допустимым. По сути,​ Sub​​ -1​ ​arrK(j, 2) =​​If Selection.Count =​)​ «сжатие» по строкам​
​11 44​
​) и скопируйте туда​
​ ячейки с информацией.​У нас такая​ флажки со всех​ пустые строки. В​поможет​ неё формулу следующего​ же или на​
​ одном столбце и​ элементы можно удалять​PackByRows​If arr1(i, 2)​ k​ 1 Then Exit​Попробуйте так (должно​If Selection.Count =​
​1​ текст этой функции:​Выделяем исходный диапазон​ таблица. В нашем​ остальных элементов. В​
​ статье мы учимся​убрать пустые ячейки в​ типа:​ другом листе. Выполняем​ не содержат формулы.​
​ только в двух​With Selection​ < arr1(i -​Next j​ Sub​ работать оч. быстро​ 1 Then Exit​Нужно1​Function NoBlanks(DataRange As​Жмем клавишу​ примере — несколько​ результате применения вы​ удалять пустые строки​ списке Excel​
​=ЕСЛИ(СТРОКА()-СТРОКА(Без_пустых)+1>ЧСТРОК(С_пустыми)-СЧИТАТЬПУСТОТЫ(С_пустыми);»»;ДВССЫЛ(АДРЕС(НАИМЕНЬШИЙ((ЕСЛИ(С_пустыми «»;СТРОКА(С_пустыми);СТРОКА()+ЧСТРОК(С_пустыми)));СТРОКА()-СТРОКА(Без_пустых)+1);СТОЛБЕЦ(С_пустыми);4)))​ щелчок правой кнопкой​Выделяем диапазон, который собираемся​ случаях:​.Rows(1).Insert​ 1, 2) Then​

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

​Call sortArr(arrK)​PackByRows​ на любом кол-ве)​ Sub​111 222 333​ Range) As Variant()​F5​ строк, но может​ оставите только пустые​ в Excel.​. Часто пустые ячейки​Так как это формула​ мыши. В появившемся​ обрабатывать. Находясь во​Если строка (столбец) полностью​For Each c​k = i​

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

​For i =​With Selection​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub PackByRows() ‘ реализация​testRows00​ 4 55​ Dim N As​, далее кнопка​ быть строк очень​ строки Excel. Удалите​Откуда могут появляться записи​ в столбцах мешают​ массива, то для​ контекстном списке действий​ вкладке​ является пустой (в​

​ In .Columns​fOut = 0​ 1 To maxCln​.Rows(1).Insert​ вашего «Нужно1″​’ выделенная область​11 22 44​ Long Dim N2​Выделить​ много.​ их через список​ с отсутствием в​ дальнейшей работе. Например,​ выведения расчета на​ в параметрах вставки​«Главная»​ таблицах);​c.Cells(0) = WorksheetFunction.CountA(c)​

​buff1 = arr1(i,​For j =​For Each c​Dim r&, c&,​ та же​ 555​ As Long Dim​(Special)​Строки 2, 4, 6,​ операций правого клика​ них данных? Такое​ когда нужно сделать​ экран нужно нажать​ выбираем пункт​

​, жмем на пиктограмму​Если ячейки в строке​Next​ 1)​ 1 To maxRow​ In .Columns​ i&, n&, v()​’ ПРЕДУПРЕЖДЕНИЕ: одна​111 444 55​ Rng As Range​

Использование фильтра

​. В открывшмся окне​ 8 нужно удалить.​ мыши или из​ может наблюдаться при​ раскрывающийся список. Как​ комбинацию клавиш​«Значения»​«Условное форматирование»​ и столбце логически​Set c =​arr1(i, 1) =​arrOut(j, maxCln +​c.Cells(0) = WorksheetFunction.CountA(c)​v = Selection.Value​ строка выше выделенного​11 44​ Dim MaxCells As​ выбираем​Выделяем таблицу, нажимаем​ блока операций «Ячейки»​ выгрузке информации из​ его сделать, читайте​

​Ctrl+Shift+Enter​.​, которая, в свою​ не связаны друг​ .Offset(-1).Resize(.Rows.Count + 1)​ arr1(i — 1,​ 1 — i)​Next​n = UBound(v)​ диапазона будут очищена​1​ Long Dim Result()​Пустые ячейки​ кнопку «Сортировка и​ вкладки «Главная».​

Выделение диапазона полей

​ базы данных или​ в статье «Выпадающий​, вместо обычного нажатия​Как видим, произошла вставка​ очередь, располагается в​ с другом (в​With .Parent.Sort​

​ 1)​ = arr2(j, arrK(i,​Set c =​ ‘ кол-во строк​

​For Each col​Нужно2​ As Variant Dim​(Blanks)​ фильтр» на закладке​Отмечаем таблицу и вызываем​ экспорте из другого​

​ список в Excel»​ кнопки​ данных без сохранения​ блоке инструментов​ массивах).​With .SortFields​arr1(i — 1,​ 1))​ .Offset(-1).Resize(.Rows.Count + 1)​ в выделенном диапазоне​ In Selection.Columns​111 4 55​ R As Long​

Недостаток метода выделения ячеек

​и жмем​ «Главная» и выбираем​ пункт меню «Выделить​ файла или приложения.​ здесь.​Enter​ форматирования. Теперь можно​«Стили»​Если пустых ячеек мало,​.Clear​ 1) = buff1​Next j​With .Parent.Sort​For c =​col.Offset(-1).Cells(1, 1).Value =​ 222 333​ Dim C As​ОК​

​ «Сортировать от минимального​ группу ячеек». Он​ Значения могут быть​В нашем списке​.​ удалить первичный диапазон,​

​. Переходим в пункт​

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

​ команд «Редактирование» и​ удаления самой строки.​
​В столбце В будет​ только одна ячейка.​ место вставить тот,​«Правила выделения ячеек»​ помощью обычного ручного​End With​arr1(i, 2) =​Sheets(lName).Cells(1 + dR,​
​.Add c.Rows(1), xlSortOnValues,​’ находим первую​Set rngS =​111 444 55​
​ DataRange.Cells.Count) ReDim Result(1​ ячейки в диапазоне.​ минимальному).​ вызывается операцией «Найти​ Так или иначе,​
​ написан список из​ Для того, чтобы​ который мы получили​. В появившемся списке​
​ способа удаления. Но,​.SetRange c​ arr1(i — 1,​ 1 + dC).Resize(maxRow,​ xlDescending​ пустую ячейку в​ Selection.Offset(-1).Resize(Selection.Rows.Count + 1)​11 44​ To MaxCells, 1​Даем в меню команду​
​Подробнее о сортировке​
​ и выделить».​
​ с этим явлением​ столбца А, но​ заполнились и остальные,​ в ходе вышеописанной​ действий выбираем позицию​

​ если таких незаполненных​.Header = xlNo​ 2)​ maxCln).ClearContents​End With​ столбце​With rngS.Parent.Sort​1​ To 1) For​ на удаление выделенных​ в таблице смотрите​В окне настроек команды​ нужно и можно​ без пустых ячеек.​ нужно скопировать формулу​
​ процедуры, а можно​
​«Больше…»​ элементов большое количество,​.Orientation = xlLeftToRight​arr1(i — 1,​Sheets(lName).Cells(1 + dR,​
​.SetRange c​For i =​.SortFields.Add Key:=rngS.Rows(1), _​Спасибо)​ Each Rng In​ ячеек: правой кнопкой​ в статье «Сортировка​ выбираем вариант «пустые​
​ бороться.​ В ячейке В2​ на оставшуюся часть​ продолжать работу с​.​ то в этом​.Apply​ 2) = buff1​

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

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

​ 1 + dC).Resize(maxRow,​.Header = xlNo​ 1 To n​

​SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal​См. пример в​ DataRange.Cells If Rng.Value​

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

1. ​ мыши​
2. ​ в Excel».​​ ячейки» и нажимаем​​Один из способов, как​​ пишем такую формулу.​​ диапазона. Это можно​​ данными на новом​Открывается окошко условного форматирования.​​ случае, данную процедуру​​End With​​End If​​ maxCln) = arrOut​​.Orientation = xlLeftToRight​

   ​If Len(v(i, c))​.SetRange rngS​

3. ​ экселе, тут форматирование​ <> vbNullString Then​-​Все заполненные строки​​ «ОК».​​ удалить пустые строки​​ Эта формула не​

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

​ сделать с помощью​ месте. Тут все​ В левое поле​​ нужно автоматизировать.​​.Rows(0).Delete​​Next i​​Application.ScreenUpdating = True​.Apply​ = 0 Then​​.Header = xlGuess​ не то.​ ​ N = N​Удалить ячейки (Delete Cells)​

​ будут стоять вверху​​Программа отмечает нужные записи.​​ в Excel -​​ сможет​​ маркера заполнения. Устанавливаем​ уже зависит от​ вписываем цифру​Наиболее простой способ удалить​End With​

​If fOut Then​Application.StatusBar ​.Rows(0).Delete​Next​.SortMethod = xlPinYin​: F5​ 1) = Rng.Value​Для упрощения дадим нашим​

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

​ строки соберутся внизу​ строки Excel, используйте​ необходимо удалить те​Excel​ правый угол ячейки,​ личных приоритетов пользователя.​. В правом поле​ это воспользоваться инструментом​ что отключали​LBdr = k​End Sub​End With​

​’ проверяем остальные​.Apply​​Выделить​​ End If Next​ рабочим диапазонам имена,​​ таблицы.​​ команду «Удалить строки​ записи, у которых​

​, но напишет список​ содержащей комплексную функцию.​Урок:​ выбираем любой цвет,​ выделения групп ячеек.​ActiveSheet.EnableCalculation = True​Loop​Function SheetCheck(ByVal ShName​End Sub​ ячейки в столбце​End With​пустые ячейки​ Rng For N2​ используя​Осталось удалить эти​ с листа», из​ ни в одной​ без пустых ячеек​ Курсор должен преобразоваться​Условное форматирование в Excel​ но можно оставить​Выделяем диапазон на листе,​Application.ScreenUpdating = True​End Sub​ As String) As​Udik​For r =​rngS.Rows(1).ClearContents​ПКМ — удалить​ = N +​Диспетчер Имен (Name Manager)​ строки. Чтобы быстро​ меню операции «Удалить»​ ячейке нет данных,​

​ в другом столбце.​ в крестик. Зажимаем​Урок:​ настройки по умолчанию.​ над которым будем​End Sub​

1. ​AndreTM​ Byte​
2. ​: Вот попробовал сварганить​​ i + 1​End Sub​​ячейки, со сдвигом​​ 1 To MaxCells​​на вкладке​​ это сделать, есть​​ блока «Ячейки».​​ сортировать можно по​<=ЕСЛИОШИБКА(ДВССЫЛ("A"&НАИМЕНЬШИЙ(ЕСЛИ(ЕПУСТО($A$2:$A$9);"";​​ левую кнопку мыши​​Сортировка и фильтрация данных​​ Щелкаем по кнопке​​ проводить операцию поиска​
3. ​gling​: А если в​Dim x As​ с сортировкой столбцов,​​ To n​​Смеяться не надо​ вверх​

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

​ Result(N2, 1) =​​Формулы (Formulas)​ хитрость.​Есть и другой способ​ любому столбцу. Если​
​СТРОКА($A$2:$A$9));СТРОКА(A1)));»»)>​ и тянем его​ в Excel​«OK»​ и удаления пустых​: Ошибка, должно быть​ код ещё добавить​
​ Byte​ вышло многа букфф​If Len(v(r, c))​
​Мне было просто​то?​
​ vbNullString Next N2​
​или — в​
​Выделяем первую пустую​
​ выделения пустых полей.​
​ же только те,​
​Эту формулу нужно​
​ вниз до самого​
​Кроме того, убрать пустые​
​.​
​ элементов. Жмем на​ так​
​ отключение/включение​x = 0​
​200?’200px’:»+(this.scrollHeight+5)+’px’);»>​
​ Then​
​ нечего делать​
​Serge_007​
​ If Application.Caller.Rows.Count =​ Excel 2003 и​
​ строку, нажав на​ Чтобы им воспользоваться,​
​ что не заполнены​
​ ввести как формулу​
​ конца диапазона​
​ ячейки из массива​
​Как видим, все ячейки​ функциональную клавишу на​regnus​

​Application.ScreenUpdating​​For Each ws​
​Public Sub test()​
​v(i, c) =​
​Например, предложенный​
​: А Вы его​ 1 Then NoBlanks​
​ старше — меню​

​ номер строки. Затем​​ откройте вкладку «Главная»​ в определенных колонках​

​ массива, п.э. нажимаем​	​«Без_пустых»​	​ можно, применив сложную​
​ указанного диапазона, в​	​ клавиатуре​	​: Пробую ваш файл,​
​- то тоже​	​ In ActiveWorkbook.Sheets​
​Dim i As​	​ v(r, c)​
​Nic70y​

​ и не применяли:​​ = Application.Transpose(Result) Else​
​Вставка — Имя -​ нажимаем одновременно кнопки​ и найдите блок​ — сортируйте только​ не просто «Enter»,​
​.​
​ формулу, состоящую из​ которых находятся значения,​
​F5​ т.к. вроде он​
​ быстродействие поднимется.​If ws.Name =​ Integer, j As​
​v(r, c) =​код​
​111​ NoBlanks = Result​
​ Присвоить​ на клавиатуре Shift+Ctrl+​
​ «Редактирование». В меню​ эту колонку. Покажем​
​ а сочетание клавиш​Как видим, после этого​
​ нескольких функций.​
​ были выделены в​
​.​ делает 2 варианта.​
​Вотбы вам сразу​
​ ShName Then​
​ Integer, k As​ Empty​
​Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub testRows()​222​
​ End If End​
​(Insert — Name -​
​ стрелка вниз. Выделятся​
​ «Найти и выделить»​
​ на примере, как​
​ «Ctrl» + «​
​ действия мы имеем​Прежде всего, нам нужно​
​ выбранный цвет, а​Запускается небольшое окошко, которое​
​500 строк и​ спросить — «как​x = 1​
​ Integer​
​i = i + 1​’ используя встроенные​
​4​ Function​ Define)​
​ все строки до​ выполните команду «Перейти».​
​ это сделать для​ Shift» + «Enter».​
​ диапазон, в котором​
​ будет дать имя​ пустые остались белыми.​
​ называется​
​ 100 столбцов обрабатывает​
​ БЫСТРО проделать данную​
​Exit For​
​Dim dC As​
​End If​
​ средства, удаляем пустые​
​333​
​Не забудьте сохранить файл​
​Диапазону B3:B10 даем имя​
​ конца листа. Теперь​
​ В окне настроек​​ целиком пустых записей.​Копируем формулу вниз​
​ подряд расположены заполненные​​ диапазону, который подвергается​​ Опять выделяем наш​
​«Переход»​
​ за 1-3 минуты.​ операцию на таком-то​End If​ Integer, dR As​
​Next r, c​ ячейки, со сдвигом​44​ и вернитесь из​
​ЕстьПустые​ удаляем строки как​
​ перехода отметьте таблицу,​Поставщик косметики прислал прайс-лист,​ по столбцу. Можно​
​ ячейки. Но выполнять​
​ трансформации. Выделяем область,​ диапазон. В этой​. Жмем в нем​

​ Точно не скажу,​​ объёме». ​​Next ws​​ Integer​Selection = v​ данных вверх​55​ редактора Visual Basic​, диапазону D3:D10 -​

​ обычную строку.​​ с которой работаете,​ в котором часть​ перед вводом формулы​ различные действия с​ делаем щелчок правой​ же вкладке​ кнопку​ т.к. при обработке​gling​SheetCheck = x​Const maxRow As​

​End Sub​​’ ПРЕДУПРЕЖДЕНИЕ: сдвигаются​11​
​ в Excel. Чтобы​
​НетПустых​
​Получилось так.​ и нажмите кнопку​

​ информации была удалена.​​ выделить диапазон столбца​
​ этими данными мы​ кнопкой мышки. В​«Главная»​«Выделить…»​ виснет.​: Собрал конструкцию из​​End Function​
​ Integer = 25,​regnus​ выше данные, расположенные​ ​55​ использовать эту функцию​
​. Диапазоны должны быть​Второй вариант.​
​ «Выделить» внизу формы.​
​ Это затрудняет работу​ В и в​ не сможем, так​
​ активировавшемся меню выбираем​щелкаем по кнопке​.​
​А 3000 строк​ разных файлов, быстро​Private Sub sortArr(arr1()​
​ maxCln As Integer​: Спасибо. ​
​ на листе ниже​11​ в нашем примере:​
​ строго одного размера,​
​Так же устанавливаем​Однако с последним методом​
​ с данными, поэтому​ ячейку В2 пишем​ как они связаны​
​ пункт​«Сортировка и фильтр»​
​Открывается следующее окно –​ и 100 столбцов​
​ ли работает не​ As Integer)​
​ = 25 ‘задаем​
​быстро действительно.​
​ выделенной области​
​44​
​Выделите достаточный диапазон пустых​

​ а расположены могут​​ фильтр. Но в​
​ нужно быть крайне​
​ мы хотим убрать​ формулу. Получился такой​

​ формулой массива. Выделяем​​«Присвоить имя…»​​, расположенной в группе​​«Выделение групп ячеек»​ появляется жесть.​ знаю. Диапазон сортировки​Dim fOut As​​ размеры для считывания​​А Нужно 2​​’ работаем с​​1​ ячеек, например F3:F10.​ быть где угодно​ диалоговом окне фильтра​ осторожным. Если вы​ эти строки. Выберем​ список.​

​ весь диапазон​​.​​«Редактирование»​​. Устанавливаем в нем​Эксель зависает минут​ нужно указывать в​ Byte​​Const lName As​​ сможете?​​ выделенной областью​​AndreTM​
​Идем в меню​ относительно друг друга.​ ставим галочку только​ выделите таблицу перед​ для сортировки колонку​Данные в столбцах могут​«Без_пустых»​Открывается окно присвоения наименования.​ ​. В открывшемся меню​
​ переключатель в позицию​ на 20 и​
​ макросе, может кто​Dim LBdr As​
​ String = «buff»,​AndreTM​
​If Selection.Count =​: Типа вот так?​Вставка — Функция (Insert​
​Теперь выделим первую ячейку​
​ у слова «Пустые».​
​ тем, как в​
​ «Цена». Для упорядочивания​ быть разные –​
​. Жмем на кнопку​
​ В поле​
​ жмем на кнопку​«Пустые ячейки»​
​ более​
​ переделает под выделение.​
​ Integer, RBdr As​
​ lNmBasa As String​:​
​ 1 Then Exit​
​(Код надо поместить​
​ — Function)​
​ второго диапазона (D3)​
​Нажимаем «Ок». У​
​ таблице Excel удалите​
​ значений можно воспользоваться​
​ хоть текст, хоть​
​«Копировать»​

​«Имя»​​«Фильтр»​. Выполняем щелчок по​Начинает глючить и​

​ Сортируются столбцы начиная​
​ Integer​
​ = «test» ‘имена​regnus​ Sub​ в общий модуль,​
​или жмем на кнопку​ и введем в​ нас в таблице​
​ пустые строки, программа​ меню «Сортировка и​ числа. Формула будет​, которая размещена во​даем любое удобное​
​.​ кнопке​ не открывает другие​ с нижней строки.​Dim buff1 As​ целевого и базового​
​, а «Нужно 2″​Selection.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp​ и вызывать, предварительно​
​Вставить функцию​ нее такую страшноватую​ будут видны только​
​ отметит не только​ фильтр» панели «Редактирование»​ работать.​
​ вкладке​ название. Главное условие​После этих действий, как​«OK»​
​ файлы.​ Результат не отличается​
​ Integer​ листа​ в моём варианте​
​End Subне подходит​ выделив область для​(Insert Function)​
​ формулу:​
​ пустые строки. Их​
​ полностью незаполненные записи,​ вкладки «Главная», отсортировав​
​Если нужно удалить​
​«Главная»​ – в нем​
​ видим, в верхнем​.​
​Приходится перегружать комп. как​
​ от результата Udik.​Dim str1 As​
​Dim arrK(1 To​
​ (конечно, заменив в​
​ по причине, изложенной​
​ обработки)​на вкладке​
​=ЕСЛИ(СТРОКА()-СТРОКА(НетПустых)+1>ЧСТРОК(ЕстьПустые)-СЧИТАТЬПУСТОТЫ(ЕстьПустые);»»;ДВССЫЛ(АДРЕС(НАИМЕНЬШИЙ((ЕСЛИ(ЕстьПустые<>»»;СТРОКА(ЕстьПустые);СТРОКА()+ЧСТРОК(ЕстьПустые)));СТРОКА()-СТРОКА(НетПустых)+1);СТОЛБЕЦ(ЕстьПустые);4)))​ видно по столбцу​
​ но и те,​
​ числа по возрастанию.​
​ пустые строки, то​в блоке инструментов​
​ не должно быть​ элементе столбца появилась​
​Как видим, все пустые​ будто эксель вирус​regnus​ String​
​ maxCln, 1 To​
​ его вызове процедуру​
​ в комментариях. ​
​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub testRows00()​Формулы (Formulas)​В английской версии это​
​ адресов строк. Можно​ в которых присутствуют​Или воспользоваться выпадающим меню​
​ читайте статью «Как​
​«Буфер обмена»​ пробелов. Для примера​
​ пиктограмма символизирующая фильтр.​
​ элементы указанного диапазона​ хватает.​: Ваш пример работает.​
​LBdr = 1​ 2) As Integer​
​testRows00​
​regnus​’ переносим ячейки,​
​в новых версиях​ будет:​
​ подкрасить последнюю пустую​
​ ячейки без данных.​
​ заголовка форматированной таблицы.​
​ удалить пустые строки​
​.​
​ мы присвоили диапазону​
​ Жмем на неё.​ были выделены. Кликаем​
​Есть способы не​А мой нет.​
​RBdr = UBound(arr1,​Dim arr2(1 To​на​
​:​ анализируя содержимое​
​ Excel. В категории​=IF(ROW()-ROW(НетПустых)+1>ROWS(ЕстьПустые)-COUNTBLANK(ЕстьПустые),»»,INDIRECT(ADDRESS(SMALL((IF(ЕстьПустые<>»»,ROW(ЕстьПустые),ROW()+ROWS(ЕстьПустые))),ROW()-ROW(НетПустых)+1),COLUMN(ЕстьПустые),4)))​
​ ячеку, чтобы виднее​
​ Удаление всего выделенного​ Так или иначе,​
​ в Excel».​
​После этого выделяем первоначальный​
​ наименование​ В открывшемся списке​ по любому из​
​ вешать эксель и​Вставляет 2 столбца​ 1)​
​ maxRow, 1 To​
​PackByRows​
​Nic70y​’ работаем с​
​Определенные пользователем (User Defined)​Причем ввести ее надо​ было, сколько пустых​
​ диапазона может привести​ после построения списка​
​Если вы скопировали​ массив данных. Щелкаем​
​«С_пустыми»​ переходим в пункт​ них правой кнопкой​
​ ускорить обработку?​ в один.​
​Do While 1​
​ maxCln)​
​от KSV всё​, у меня 3000​
​ выделенной областью​
​выберите нашу функцию​
​ как формулу массива,​ строк. Выделяем пустые​ не только к​ строки с отсутствующими​
​ таблицу из Word​ правой кнопкой мыши.​. Больше никаких изменений​
​«Сортировка по цвету»​
​ мыши. В запустившемся​
​Serge_007​Не сортирует столбцы.​
​fOut = 1​Dim arrOut(1 To​ равно остается медленной?​
​ строк и под​If Selection.Count =​
​NoBlanks​ т.е. после вставки​
​ строки и удаляем​ потере нужной информации,​ ценами окажутся внизу​
​ в Excel, и​ В открывшемся списке​
​ в том окне​
​. Далее в группе​
​ контекстном меню щелкаем​:​
​удалено администрацией​
​For i =​
​ maxRow, 1 To​

​ Тогда единственный вариант​​ 200 столбцов. Медленно​ 1 Then Exit​.​​ нажать не​​ функцией «Удалить строку».​ но и к​
​ таблицы. Теперь удалим​ у вас в​ в группе​ вносить не нужно.​«Сортировка по цвету ячейки»​

​ по пункту​​regnus​KSV​ 1 To RBdr​ maxCln)​ — считать все​ удаляет. Эксель виснет.​ Sub​В качестве аргумента функции​Enter​ Теперь фильтром возвращаем​ искажению оставшейся.​

​ пустые строки Excel​​ таблице появились пустые​
​«Параметры вставки»​
​ Жмем на кнопку​выбираем тот цвет,​
​«Удалить…»​
​, Вы можете выложить​

​:​​ — 1​​dC = 0​​ данные в массив​ Попробую на ночь​
​For Each col​​ укажите исходный диапазон​​(как обычно), а​
​ все заполненные строки​Использование первых двух способов​ в конце командой​
​ ячейки, которые сместились,​жмем на пиктограмму​«OK»​ которым произошло выделение​.​ видео с экрана?​ ​regnus​
​If arr1(i, 2)​ ‘ delta Colunm​
​ и провести преобразования​ оставить.​
​ In Selection.Columns​ с пустотами (B3:B10)​
​Ctrl+Shift+Enter​ в таблице.​
​ более надежно и​ контекстного меню.​
​ то читайте статью​«Значения​.​
​ в результате условного​
​Открывается маленькое окошко, в​
​Udik​
​, что у вас​ > arr1(i +​
​ если выводить не​
​ уже с ним. ​
​_Boroda_​Set cellTo =​
​ и нажмите​
​. Теперь формулу можно​
​Третий вариант.​
​ безопасно, поэтому лучше​Еще один простой метод​
​ «Копировать таблицу, бланк​
​».​
​Выделяем в любом месте​
​ форматирования.​
​ котором нужно выбрать,​
​: Я на своём​
​ не работает?​
​ 1, 2) Then​
​ в А1​KSV​
​: Не уверен, но​
​ Nothing​
​Ctrl+Shift+Enter​

​ скопировать вниз, используя​​Как удалить пустые​ использовать именно их,​

​ – применение фильтра​​ из Word в​После этих действий данные​ на листе точно​
​Можно также сделать немного​ что именно следует​ попробовал 3000х200 -​Вариант, предложенный вам​k = i​dR = 0​
​:​ в качестве предположения​For Each cell​
​, чтобы ввести функцию​ автозаполнение (потянуть за​ ячейки формулой, смотрите​
​ чтобы удалить пустые​ к данным. Если​ Excel» тут. В​
​ будут вставлены в​ такой же по​ по-другому. Кликаем по​
​ удалить. Оставляем настройки​ ок. 35с.​AndreTM​

​fOut = 0​​ ‘ delta Row​​regnus​​ — возможно, код​ In col.Cells​

​ как формулу массива.​​ черный крестик в​ в статье «Как​ строки в Excel.​

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Способ 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. Чтобы использовать эту функцию в нашем примере:

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

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

Возможность и методы удаления

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

• Весь столбец (строка) не заполнен;
• Элементы в столбце (строке) не взаимосвязаны.

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

Метод 1: простое выделение и удаление

Данный метод, пожалуй, можно считать самым простым. Вот, что мы делаем:

1. Любым удобным способом производим выделение области, в которой нам нужно найти и удалить все незаполненные ячейки. После того, как выделение выполнено, нажимаем клавишу F5.
2. На экране отобразится окно перехода, в котором щелкаем “Выделить”.
3. Мы окажемся в окне выделения групп ячеек. Здесь среди все вариантов выбираем “пустые ячейки”, поставив напротив отметку, после чего жмем OK.
4. В нашем диапазоне будут отмечены все пустые ячейки. Щелчком правой кнопки мыши по любому их них открываем меню, в котором выбираем команду “Удалить”.
5. В появившемся окне останавливаемся на варианте “ячейки, со сдвигом вверх” и нажимаем OK.
6. В результате выполненных действий мы удалили все пустые ячейки, находящиеся в выделенной области.
7. Кликаем по любому элементу на листе, чтобы снять выделение.

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

Метод 2: фильтр и условное форматирование

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

Фильтрация данных:

1. Для начала нужно любым удобным способом выделить нужную область ячеек. Затем в главной вкладке в блоке инструментов “Редактирование” щелкаем по значку “Сортировка и фильтр”. В раскрывшемся перечне выбираем пункт “Фильтр”.
2. Внутри самой верхней ячейки выделенного диапазона появится характерный значок фильтра в виде небольшого квадрата с треугольником, направленным вниз. Щелкаем по нему. Откроется меню, в котором убираем галочку напротив пункта “Пустые” и жмем OK.
3. В таблице останутся только ячейки, содержащие какие-либо данные.

Условное форматирование и фильтрация:

1. Выполняем выделение диапазона ячеек. В главной вкладке в группе инструментов “Стили” нажимаем кнопку “Условное форматирование”. В раскрывшемся перечне останавливаемся на варианте “Правила выделения ячеек”, затем – выбираем “Больше”.
2. На экране отобразится окно форматирования:
   • в поле слева указываем число 0;
   • в правом – щелкнув по текущему значению раскрываем список возможных цветовых схем и выбираем понравившийся вариант (или оставляем стандартный);
   • по готовности жмем OK.
3. Ко всем заполненным ячейкам будет применена цветовая схема, которую мы выбрали, в то время, как заливка пустых ячеек не изменилась. Повторно выделяем тот же самый диапазон (если выделение было снято) и включаем “Фильтр”.
4. Кликаем по значку фильтра, в открывшемся меню наводим указатель мыши на строку “Фильтр по цвету”, после чего откроется еще одно подменю, где останавливаемся на варианте “Фильтр по цвету ячейки” или “Фильтр по цвету шрифта” (в данном случае, это неважно).
5. В результате, из всего выделенного диапазона мы оставили только заполненные ячейки.

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

1. Нажимаем кнопку “Копировать” в группе инструментов “Буфер обмена” (вкладка “Главная”).
2. На этом же (или другом) листе отмечаем соразмерный диапазон, затем щелчком правой кнопки мыши по нему раскрываем меню, в котором кликаем по варианту вставки – “Значения” (значок в виде папки с цифрами 123). Тут есть один нюанс – выделять нужно диапазон, который располагается ниже самой нижней строки области с первоначальными данными.
3. Нам удалось вставить данные (без первоначального форматирования ячеек). Мы можем продолжить работу с ними здесь или перенести (скопировать) в любое другое удобное место.

Сортировка вместо фильтрации:

В данном случае, мы выполняем те же самые шаги, что и при условном форматировании. Но вместо фильтра теперь выбираем “Сортировку по цвету”.

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

Метод 3: использование формулы

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

1. Для начала нужно задать имя диапазону ячеек, с которым планируем работать. Для этого выделяем его, затем кликаем по нему правой кнопкой мыши и в контекстном меню выбираем команду “Присвоить имя”.
2. В окне создания имени в поле “Имя” указываем любое название (без пробелов). Остальные поля оставляем без изменений и нажимаем OK.
3. В любом свободном месте производим выделение соразмерного диапазона ячеек (для удобства, если возможно, лучше это сделать в тех же строках другого столбца). Затем также в контекстном меню, вызываемом щелчком правой кнопки мыши по нему, выбираем пункт “Присвоить имя”.
4. Как и для первого диапазона, задаем имя (в нашем случае – “Без_пустых_ячеек”).
5. Встаем в самую верхнюю ячейку диапазона “Без_пустых_ячеек” (в нашем случае – это D2) и пишем в нее следующую формулу:

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

Заключение

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