Удалить строку excel builder c rows

Обновлено: 04.07.2024

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

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

В этой статье вы узнаете несколько простых и надежных методов удаления “белых пятен” на листах Excel.

Как НЕ НАДО удалять пустые строки в Excel

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

Пожалуй, самый популярный совет – использовать инструмент «Найти и выделить» > «Перейти к специальному» > «Пробелы» .

Что плохого в этой технике? Она выбирает все пустоты в диапазоне данных. Но ведь в строке может быть, к примеру, только одна пустая ячейка, а в остальных будет записаны какие-то данные. И, следовательно, вы в итоге удалите множество строчек, которые содержат хотя бы одну пустую ячейку. Некоторые нужные данные, естественно, будут утеряны.

Специально не буду подробно описывать последовательность действий в этом методе, чтобы вы случайно его не повторили.

И ключевым в данном случае является вопрос: «Вы уверены, что все ячейки в вашей строке пустые? У вас точно нет таких, в которых по какой-то причине не заполнены одна или несколько ячеек, а остальные содержат данные?»

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

В качестве иллюстрации на изображении ниже слева показана исходная таблица, а справа - итоговая таблица. И в результирующей таблице все неполные строки пропали, даже 4,6 и 10, которые были частично заполнены:


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

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

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


С фильтрацией примерно та же история. Вы можете установить фильтр по пустым ячейкам в каком-то конкретном столбце. Далее все может произойти так же, как в истории с сортировкой. Какие-то из ячеек могут оказаться заполнены нужной информацией. Чтобы обезопасить себя от случайной её потери, такой фильтр по пустым ячейкам нужно установить в каждом столбце. Хорошо, если их в вашей таблице штук 5. А если 20? А 30? Вряд ли у вас есть желание устанавливать, а затем снимать даже 10 фильтров.


Поэтому эти методы – это также не наш выбор.

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

Формула для удаления пустых строк в Excel

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

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

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

Где A2 - первая, а E2 - последняя использованная ячейка первой строки данных.

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

В результате у вас будет записано «Пусто» в незаполненных строках и ничего, если есть хотя бы одна ячейка с данными:


Логика формулы очевидна: вы подсчитываете заполненные ячейки с помощью функции СЧЁТЗ и используете оператор ЕСЛИ для возврата «Пусто» для нулевого результата. Если же найдена хоть какая-то информация в нужном диапазоне, то не возвращаем ничего.

Фактически, вы можете обойтись без ЕСЛИ:

В этом случае формула вернет ИСТИНА для пустых и ЛОЖЬ для заполненных строк.

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

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

Как мы это удаление сделаем?

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

  1. Выберите любую ячейку в заголовке и нажмите «Фильтр» на вкладке «Данные». Это добавит стрелки раскрывающегося списка фильтрации ко всем ячейкам заголовка.
  2. Щелкните стрелку в заголовке столбца формулы, снимите флажок (Выбрать все), выберите «Пусто» и нажмите ОК:
  1. Выделите всё отфильтрованное. Для этого щелкните самую верхнюю ячейку и нажмите Ctrl + Shift + End, чтобы расширить выделение до последней ячейки отфильтрованной области.


Или же щелкните по номеру первой незаполненной строки на вертикальной шкале координат. При этом вся она должна подсветиться. Затем наберите на клавиатуре комбинацию Ctrl + Shift + (стрелка вниз). При этом будет выделена вся область до конца таблицы. Поле этого кликните по выделению правой кнопкой мыши и выберите из контекстного меню пункт «Удалить». Все выделенные строки будут удалены.

Можно не вызывать контекстное меню, а просто набрать на клавиатуре сочетание клавиш Ctrl и - .

  1. Снимите фильтр, нажав Ctrl + Shift + L . Или же поставьте курсор в самую верхнюю позицию и вновь нажмите на иконку фильтра в меню «Данные».
  2. Удалите столбец с формулой проверки, поскольку он вам больше не нужен.

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

Совет. Вместо удаления, вы можете скопировать непустые строки в другое место. Для этого отфильтруйте всё, кроме «Пусто», выделите и нажмите Ctrl + C . Затем переключитесь на другой лист, выберите верхнюю левую ячейку целевого диапазона и нажмите Ctrl + V .

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

В Excel 2016 и Excel 2019 есть еще один способ удалить незаполненные строки - с помощью инструмента Power Query. В Excel 2010 и 2013 его можно загрузить как надстройку.

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

Итак, давайте по порядку.

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

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

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

В качестве примера удалим все строки, в которых ячейка в столбце A не содержит никакой информации:

  1. Выберите ключевой столбец, в нашем случае столбец A.
  2. На вкладке «Главная» нажмите Найти и выделить > Перейти… > Выделить. Или просто нажмите F5 и затем — Выделить… .
  1. В диалоговом окне выберите «Пробелы» и затем — «ОК». Это выделит все пустоты в столбце A.
  2. Щелкните правой кнопкой мыши любую выделенную ячейку и выберите «Удалить…» из контекстного меню.
  3. В диалоговом окне «Удалить» кликните «Строку» и нажмите «ОК».

Готово! Строки, которые не имеют данных в столбце A, больше не существуют.

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

Как удалить лишние строки под данными или скрыть их.

Иногда строки, которые выглядят совершенно пустыми, на самом деле могут содержать пробелы, перевод строки или непечатаемые символы. Чтобы проверить, действительно ли последняя ячейка с данными является последней использованной ячейкой на листе, нажмите Ctrl + End. Если это привело вас к визуально свободной от данных строке под вашими данными, то с точки зрения Excel, в ней на самом деле что-то записано. Чтобы удалить эти лишние строки под таблицей, сделайте следующее:

  1. Щелкните заголовок первой свободной строки под данными, чтобы выбрать ее целиком.
  2. Нажмите Ctrl + Shift + End . Это выберет все строчки ниже, которые содержат что-либо, включая пробелы и непечатаемые символы.
  3. Щелкните выделение правой кнопкой мыши, как это мы уже делали ранее, и выберите «Удалить…».

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


  1. Выберите строку под последней строкой с данными, щелкнув ее заголовок.
  2. Нажмите Ctrl + Shift + , чтобы расширить выделение до конца вниз.
  3. Нажмите Ctrl + 9 чтобы скрыть выбранные строки. Или щелкните выделение правой кнопкой мыши и выберите «Скрыть».

Чтобы вновь показать ранее скрытое, нажмите Ctrl + A , чтобы выделить весь лист, а затем нажмите Ctrl + Shift + 9 , чтобы снова сделать все видимым.

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

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

Макрос 1. Удалить пустые строки в выбранном диапазоне.

Макрос удаляет строку, только если вся она пуста . Он определяет количество ячеек с данными в каждой строке, а затем удаляет строки с нулевым результатом.

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

После запуска макрос показывает вам поле ввода, вы указываете целевой диапазон и нажимаете OK:

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

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

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

Макрос 3. Удалить строку, если ячейка пуста.

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

Этот код проверяет столбец A на наличие пробелов. Чтобы удалить строки на основе другого столбца, замените «A» другой соответствующей буквой.

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

Чтобы удалить незаполненные строки в Excel с помощью макроса, вам нужно вставить код VBA в свою книгу.

Как добавить макрос в свою книгу?

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

Чтобы выполнить макрос в рабочей книге Excel, сделайте следующее:

  1. Откройте свою собственную книгу и включите макросы, если будет предложено.
  2. Перейдите к нужной таблице.
  3. На листе нажмите Alt + F8 , выберите макрос и нажмите «Выполнить».

Вы можете запустить один из следующих макросов:

DeleteBlankRows - удаляет строки без данных в текущем выбранном диапазоне.

RemoveBlankLines - удаляет чистые строки и перемещается вверх в диапазоне, выбранном вами после запуска макроса.

DeleteAllEmptyRows - удаляет все незаполненные строки на активном листе.

DeleteRowIfCellBlank - удаляет строку, если ячейка в определенном столбце пуста.

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

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

Читая предыдущие примеры, разве не казалось, что мы используем кувалду, чтобы расколоть орех? Существует способ буквально в два клика мышки удалить пустые строки в Excel.

При помощи надстройки Ultimate Suite вы можете удалить все пустые строки на листе:

  1. На вкладке Ablebits Tools в группе Преобразование (Transform) нажмите Удалить пробелы (Delete Blanks) > Пустые строки (Empty Rows) :
  1. Надстройка сообщит вам, что все пустые строки будут удалены из активного листа, и попросит подтвердить. Нажмите ОК, и через мгновение все строки без данных будут удалены.

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


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

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

Благодарю вас за чтение.

Быстрое удаление пустых столбцов в Excel - В этом руководстве вы узнаете, как можно легко удалить пустые столбцы в Excel с помощью макроса, формулы и даже простым нажатием кнопки. Как бы банально это ни звучало, удаление пустых… Как быстро объединить несколько файлов Excel - Мы рассмотрим три способа объединения файлов Excel в один: путем копирования листов, запуска макроса VBA и использования инструмента «Копировать рабочие листы» из надстройки Ultimate Suite. Намного проще обрабатывать данные в… 6 примеров — как консолидировать данные и объединить листы Excel в один - В статье рассматриваются различные способы объединения листов в Excel в зависимости от того, какой результат вы хотите получить: объединить все данные с выбранных листов,объединить несколько листов с различным порядком столбцов,объединить… Как полностью или частично зафиксировать ячейку в формуле - При написании формулы Excel знак $ в ссылке на ячейку сбивает с толку многих пользователей. Но объяснение очень простое: это всего лишь способ ее зафиксировать. Знак доллара в данном случае служит только… Чем отличается абсолютная, относительная и смешанная адресация - Важность ссылки на ячейки Excel трудно переоценить. Ссылка включает в себя адрес, из которого вы хотите получить информацию. При этом используются два основных вида адресации – абсолютная и относительная. Они… Относительные и абсолютные ссылки – как создать и изменить - В руководстве объясняется, что такое адрес ячейки, как правильно записывать абсолютные и относительные ссылки в Excel, как ссылаться на ячейку на другом листе и многое другое. Ссылка на ячейки Excel,… 6 способов быстро транспонировать таблицу - В этой статье показано, как столбец можно превратить в строку в Excel с помощью функции ТРАНСП, специальной вставки, кода VBA или же специального инструмента. Иначе говоря, мы научимся транспонировать таблицу.…

как форматировать Google таблицу

Как сделать пользовательский числовой формат в Excel - В этом руководстве объясняются основы форматирования чисел в Excel и предоставляется подробное руководство по созданию настраиваемого пользователем формата. Вы узнаете, как отображать нужное количество десятичных знаков, изменять выравнивание или цвет шрифта,… Как изменить формат даты в Excel и создать собственное форматирование - Это руководство посвящено форматированию даты в Excel и объясняет, как установить вид даты и времени по умолчанию, как изменить их формат и создать собственный. Помимо чисел, наиболее распространенными типами данных,… 7 способов поменять формат ячеек в Excel - Мы рассмотрим, какие форматы данных используются в Excel. Кроме того, расскажем, как можно быстро изменять внешний вид ячеек самыми различными способами. Когда дело доходит до форматирования ячеек в Excel, большинство…

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

Список вопросов, ответы на которые приводятся в данном уроке.

1 Как получить из ячейки не только ее значение, но и формулу данной ячейки ?
2 Как нарисовать диаграмму ?
3 Как нарисовать границы у выбранного диапазона ячеек (бордюр) ?
4 Как получить значение какой-либо ячейки ?
5 Как задать формат для ячейки ?
6 Как установить масштаб для документа ?
7 Как выровнять данные в ячейке ?
8 Как установить высоту строки ?
9 Как установить ширину столбца ?
10 Как установить цвет ячеек ?
11 Как задать количество листов в книге ?
12 Как дать название листу ?
13 Как сохранить получившийся документ ?
14 Как установить настройки при печати ?
14 Как распечатать получившийся документ ?
15 Как закрыть документ Excel ?

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


Примерная иерархия вложенных объектов OLE-сервера.

Итак, на вершине — объект Application (у нас в примере Variant App),
Workbooks — книга (не используем), работаем сразу с листом — WorkSheets — у нас Variant Sh,
группы ячеек Cells — для них используем объект Variant Rang;

"Свойствами объектов Excel могут являться так называемые коллекции объектов.
Например, коллекция Workbooks является свойством объекта Excel.Application, при этом она содержит
набор вложенных объектов — рабочих книг Excel, а те, в свою очередь, обладают свойством Worksheets,
представляющим собой коллекцию рабочих листов, каждый из которых обладает свойством Cells,
являющимся коллекцией ячеек.
Аналогично, коллекция Charts также является свойством рабочей книги"
из статьи " Создание контроллеров автоматизации с помощью C++Builder" Наталия Елманова

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

Вопрос. У меня в ячейке есть число, но оно рассчитывается по формуле в этой ячейке.
Как получить из ячейки не только ее значение, но и формулу данной ячейки ?

// получить формулу из ячейки
Variant __fastcall TForm1::fromExcelFormula(int Row, int Column)
try Variant result,cur;
cur = Sh.OlePropertyGet("Cells",Row,Column);
result = cur.OlePropertyGet("Formula");
return result;
>catch(. )
>
// получаем значение и формулу из ячейки 34,5
void __fastcall TForm1::Button1Click(TObject *Sender)
Label3->Caption = fromExcelCell(34,5); // получить значение из ячейки
Label4->Caption = fromExcelFormula(34,5); // получить формулу из ячейки
>
//-----------------------------------------------

Вопрос. Как нарисовать диаграмму ?

void __fastcall TForm1::Button5Click(TObject *Sender)
Variant Chart;
Chart=App.OlePropertyGet("Charts").OleFunction("Add"); // добавим объект диаграмму
Chart.OlePropertySet("ChartType",65); // установим один из типов диаграммы

// выберем ячейки для построения диаграммы, это строки 3 — 31 и столбец 3
Chart.OleProcedure("SetSourceData",Sh.OlePropertyGet("Range",Sh.OlePropertyGet("Cells",3,3),
Sh.OlePropertyGet("Cells",31,3)),2);
Chart.OlePropertyGet("SeriesCollection",1).OlePropertySet("XValues",Sh.OlePropertyGet("Range",
Sh.OlePropertyGet("Cells",2,3),Sh.OlePropertyGet("Cells",31,3)));
Chart.OleProcedure("Location",2,Sh.OlePropertyGet("Name"));
// установим место вывода диаграммы
// для этого получим UsedRange — испольуемый диапазон ячеек, и отступим вниз на 25 пикселей
Sh.OlePropertyGet("ChartObjects",1).
OlePropertySet("Top",Sh.OlePropertyGet("UsedRange").OlePropertyGet("Height")+25);
// и слева от края 10 пикселей
Sh.OlePropertyGet("ChartObjects",1).OlePropertySet("left",10);
// встаем на начало
Sh.OlePropertyGet("Cells",1,1).OleProcedure("Select");
>
//-----------------------------------------------------

Вопрос. Как нарисовать границы у выбранного диапазона ячеек (бордюр) ?

// Устанавливаем диапазон ячеек для рисования бордюра A2:E31
int m = 30;
AnsiString s = "A2:E"+IntToStr(m+1);
Variant range = App.OlePropertyGet("Range", s.c_str());
for (int i=1; i<=4; i++)
range.OlePropertyGet("Borders").OlePropertyGet("Item", i).OlePropertySet("LineStyle", 1);


Вопрос. Как получить значение какой-либо ячейки ?

Label3->Caption = fromExcelCell(34,5); // получить значение ячейки 34,5

// получить значение ячейки
Variant __fastcall TForm1::fromExcelCell(int Row, int Column)
try Variant result,cur;
cur = Sh.OlePropertyGet("Cells",Row,Column);
result = cur.OlePropertyGet("Value");
return result;
>catch(. )
>
//-------------------


Вопрос. Как задать формат для ячейки ?

// устанавливаем формат числа для ячейки 1,1
Sh.OlePropertyGet("Cells", 1,1).OlePropertySet("NumberFormat", "0.00");
// устанавливаем формат строка для ячейки 1,2
Sh.OlePropertyGet("Cells", 1,2).OlePropertySet("NumberFormat", "@");
// устанавливаем формат даты для ячейки 1,3
Sh.OlePropertyGet("Cells", 1,3).OlePropertySet("NumberFormat", "ДД.ММ.ГГГГ");

Вопрос. Как установить масштаб для документа ?

// Устанавливаем масштаб = 75%
App.OlePropertyGet("ActiveWindow").OlePropertySet("Zoom", 75);

Вопрос. Как выровнять данные в ячейке ?

// Выравнивам данные в ячейке
Sh.OlePropertyGet("Cells", 1,1).OlePropertySet("HorizontalAlignment", 4);
// где 4 — по левому краю, 2 — по правому, 3 — по центру)

// или по-другому это можно записать так
// Rang = Sh.OlePropertyGet("Cells",1,1);
// Rang.OlePropertySet("HorizontalAlignment",4);
// и для вертикального выравнивания — VerticalAlignment

Вопрос. Как установить высоту строки ?

// устанавливаем высоту 60
Rang.OlePropertySet("RowHeight", 60);

Вопрос. Как установить ширину столбца ?

// устанавливаем ширину столбца 12
Sh.OlePropertyGet("Columns").OlePropertyGet("Item",1).OlePropertySet("ColumnWidth", 12);

Вопрос. Как установить цвет ячеек ?

Rang = Sh.OlePropertyGet("Range", "A1:E1");
Rang.OlePropertyGet("Interior").OlePropertySet("ColorIndex",4);

Вопрос. Как задать количество листов в книге ?

// Определяем количество листов в книге — 1
App.OlePropertySet("SheetsInNewWorkbook", 1);

Вопрос. Как дать название листу ?

// Даем название нашему листу "Наш лист итогов"
Sh = App.OlePropertyGet("Worksheets").OlePropertyGet("Item", 1);
Sh.OlePropertySet("Name", "Наш лист итогов");

Вопрос. Как сохранить получившийся документ ?

// Не спрашивать о замене файла, если он уже есть
App.OlePropertySet("DisplayAlerts", false);

// Сохраняем файл как "Firm.xls"
App.OlePropertyGet("WorkSheets",1).OleProcedure("SaveAs","Firm.xls");

Вопрос. Как установить настройки при печати ?

// Левое и правое поля отступа для печати
Sh.OlePropertyGet("PageSetup").OlePropertySet("LeftMargin", 80);
Sh.OlePropertyGet("PageSetup").OlePropertySet("RightMargin",20);

Вопрос. Как распечатать получившийся документ ?

Вопрос. Как закрыть документ Excel ?

try App.OlePropertyGet("WorkBooks",1).OleProcedure("Close");
>catch(. ) ShowMessage("Не забудьте сами закрыть Excel.");
>
// закрыть сам Excel
App.OleProcedure("Quit");

//-----------------------------
Посмотреть как все это работает и другие возможности
для C++Builder 6 полный проект можно скачать и посмотреть в работе здесь (15 Кб).

Многие спрашивают — как ускорить вывод в Excel больших массивов данных ,
слишком медленно все работает.
Наиболее идеальный вариант — воспользоваться компонентами XLSReadWrite2 или аналогичными.
Также, в проекте есть пример, как можно немного ускорить такой вывод,
для этого при выводе данных можно не выводить данные последовательно в каждую ячейку
в цикле, а лучше сформировать вариантный массив, и выполнить присвоение области (Range)
этого массива, только после этого делая Excel видимым.
Также более быстрая работа происходит при использовании библиотеки типов.

В следующем уроке:

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

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

Способ первый:
Использовать встроенное средство Excel - фильтр. Сначала его необходимо "установить" на листе:

  • Выделяем таблицу с данными, включая заголовки. Если их нет - то выделяем с самой первой строки таблицы, в которой необходимо удалить данные
  • устанавливаем фильтр:
    • для Excel 2003 : Данные-Фильтр-Автофильтр
    • для Excel 2007-2010 : вкладка Данные (Data) -Фильтр (Filter)(или вкладка Главная (Home) -Сортировка и фильтр (Sort&Filter) -Фильтр (Filter) )

    Теперь выбираем условие для фильтра:

    • в Excel 2003 надо выбрать Условие и в появившейся форме выбрать непосредственно условие("равно", "содержит", "начинается с" и т.д.), а напротив значение в соответствии с условием.
    • Для 2007-2010 Excel нужно выбрать Текстовые фильтры (Text Filters) и либо сразу выбрать одно из предлагаемых условий, либо нажать Настраиваемый фильтр (Custom Filter) и ввести значения для отбора в форме

    После этого удалить отфильтрованные строки. В 2007 Excel могут возникнуть проблемы с удалением отфильтрованных строк, поэтому рекомендую сначала так же прочитать статью: Excel удаляет вместо отфильтрованных строк - все?! Как избежать.


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

    Если значение sSubStr не будет указано, то будут удалены строки, ячейки указанного столбца которых, пустые.
    Данный код необходимо поместить в стандартный модуль. Вызвать с листа его можно нажатием клавиш Alt + F8 , после чего выбрать Del_SubStr и нажать Выполнить. Если в данном коде в строке
    If -(InStr(Cells(li, 1), sSubStr) > 0) = lMet Then
    вместо = lMet указать <> lMet , то удаляться будут строки, не содержащие указанное для поиска значение. Иногда тоже удобно.
    Но. Данный код просматривает строки на предмет частичного совпадения указанного значения. Например, если Вы укажете текст для поиска "отчет", то будут удалены все строки, в которых встречается это слово("квартальный отчет", "отчет за месяц" и т.д.). Это не всегда нужно. Поэтому ниже приведен код, который будет удалять только строки, указанные ячейки которых равны конкретно указанному значению:

    Здесь так же, как и в случае с предыдущим кодом можно заменить оператор сравнения( Cells(li, lCol) = sSubStr ) с равно на неравенство( Cells(li, lCol) <> sSubStr ) и тогда удаляться будут строки, значения ячеек которых не равно указанному.

    УДАЛЕНИЕ СТРОК НА ОСНОВАНИИ СПИСКА ЗНАЧЕНИЙ(МНОЖЕСТВЕННЫЕ КРИТЕРИИ)
    Иногда бывают ситуации, когда необходимо удалить строки не по одному значению, а по нескольким. Например, если строка содержит или Итог или Отчет. Ниже приведен код, при помощи которого можно удалить строки, указав в качестве критерия диапазон значений.
    Значения, которые необходимо найти и удалить перечисляются на листе с именем "Лист2". Т.е. указав на "Лист2" в столбце А(начиная с первой строки) несколько значений - они все будут удалены. Если лист называется иначе(скажем "Соответствия") в коде необходимо будет "Лист2" заменить на "Соответствия". Удаление строк происходит на активном в момент запуска кода листе. Это значит, что перед запуском кода надо перейти на тот лист, строки в котором необходимо удалить.

    Чтобы код выше удалял строки не по точному совпадению слов, а по частичному(например, в ячейке записано "Привет, как дела?", а в списке есть слово "привет" - надо удалить, т.к. есть слово "привет"), то надо строку:

    If CStr(arr(li, 1)) = sSubStr Then

    заменить на такую:

    If InStr(1, arr(li, 1), sSubStr, 1) > 0 Then

    УДАЛЕНИЕ ИЗ ЛИСТА СТРОК, КОТОРЫХ НЕТ В СПИСКЕ ЗНАЧЕНИЙ(МНОЖЕСТВЕННЫЕ КРИТЕРИИ)
    Т.к. в последнее время стало поступать все больше и больше вопросов как не удалять значения по списку, а наоборот - оставить в таблице только те значения, которые перечислены в списке - решил дополнить статью и таким кодом.
    Значения, которые необходимо оставить перечисляются на листе с именем "Лист2". Т.е. указав на "Лист2" в столбце А(начиная с первой строки) несколько значений - после работы кода на листе будут оставлены только те строки, в которых присутствует хоть одно из перечисленных в списке значений. Если лист называется иначе(скажем "Соответствия") в коде необходимо будет "Лист2" заменить на "Соответствия". Удаление строк происходит на активном в момент запуска кода листе. Это значит, что перед запуском кода надо перейти на тот лист, строки в котором необходимо удалить.
    В отличие от приведенных выше кодов, данный код ориентирован на то, что значения в списке указаны не полностью. Т.е. если необходимо оставить только те ячейки, в которых встречается слово "активы", то в списке надо указать только это слово. В этом случае если в ячейке будет записана фраза "Нематериальные активы" или "Активы сторонние" - эти ячейки не будут удалены, т.к. в них встречается слово "активы". Регистр букв при этом неважен.

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

    If InStr(1, arr(li, 1), sSubStr, 1) > 0 Then

    заменить на такую:

    If CStr(arr(li, 1)) = sSubStr Then

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

    If Not rr Is Nothing Then rr.EntireRow.Delete

    заменить на такую:

    If Not rr Is Nothing Then rr.EntireRow.Hidden = True

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

    For li = 1 To lLastRow 'цикл с первой строки до конца

    1 - это первая строка; lLastRow - определяется автоматически кодом и равна номеру последней заполненной строки на листе. Если надо начать удалять строки только с 7-ой строки(например, в первых 6-ти шапка), то код будет выглядеть так:

    Включение и отключение макросов через меню разработчика

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

    Включить или отключить макросы в Microsoft Excel можно через меню разработчика. Но, проблема состоит в том, что по умолчанию данное меню отключено. Чтобы его включить, переходим во вкладку «Файл». Далее, кликаем по пункту «Параметры».


    В открывшемся окне параметров, переходим в раздел «Настройка ленты». В правой части окна данного раздела устанавливаем галочку около пункта «Разработчик». Жмем на кнопку «OK».


    После этого, на ленте появляется вкладка «Разработчик».

    Переходим во вкладку «Разработчик». В самой правой части ленты расположен блок настроек «Макросы». Чтобы включить или отключить макросы, кликаем по кнопке «Безопасность макросов».


    Открывается окно центра управления безопасностью в разделе «Макросы». Для того, чтобы включить макросы, переставляем переключатель в позицию «Включить все макросы». Правда, данное действие разработчик не рекомендует выполнять в целях безопасности. Так что, всё выполняется на свой страх и риск. Жмем на кнопку «OK», которая расположена в нижнем правом углу окна.


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

    1. Отключить все макросы без уведомления;
    2. Отключить все макросы с уведомлением;
    3. Отключить все макросы, кроме макросов с цифровой подписью.

    В последнем случае, макросы, у которых будет иметься цифровая подпись, смогут выполнять задачи. Не забываем жать на кнопку «OK».


    Включение и отключение макросов через параметры программы

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


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


    Настройка макросов в Параметрах программы


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

    Встроенная функция рабочего листа

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

    Пользовательская функция

    Можно бороться с лишними пробелами и с помощью пользовательской функции:

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

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

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

    Макрос для удаления пустых строк

    Как удалить строку макросом? Если нужно сделать так чтобы макрос автоматически не только выделял, но и сам удалял пустые целые и смежные диапазоны ячеек без использования других инструментов, тогда в конце кода для переменной diapaz2.Select следует изменить метод на [Delete]:

    Sub DelLine()
    Dim i As Long
    Dim diapaz1 As Range
    Dim diapaz2 As Range
    Set diapaz1 = Application.Range(ActiveSheet.Range( "A1" ), _
    ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))
    For i = 1 To diapaz1.Rows.Count
    If WorksheetFunction.CountA(diapaz1.Rows(i).EntireRow) = 0 Then
    If diapaz2 Is Nothing Then
    Set diapaz2 = diapaz1.Rows(i).EntireRow
    Else
    Set diapaz2 = Application.Union(diapaz2, diapaz1.Rows(i).EntireRow)
    End If
    End If
    Next
    If diapaz2 Is Nothing Then
    MsgBox "Ненайдено ниодной пустой строки!"
    Else
    diapaz2.[Delete]
    End If
    End Sub

    Пример второго VBA-кода:


    Макрос для скрытия пустых строк

    Как скрыть пустые строки макросом? Но если вам нужно не удалить, а только скрыть (например, при подготовке документа на печать), тогда эту строку кода следует модифицировать несколько иначе:

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