Удалить скрытые строки в excel vba
Как удалить только видимые строки или столбцы в Excel?
В Excel мы обычно скрываем некоторые важные строки или столбцы информации, иногда нам нужно удалить только видимые строки или столбцы, если вы удалите их напрямую с помощью клавиши Delete, скрытые значения будут удалены одновременно. Как удалить видимые строки или столбцы, не удаляя скрытые строки или столбцы в Excel? В этом руководстве будут представлены способы удаления только видимых строк.
Пакетное удаление пустых / видимых / скрытых строк или столбцов в Excel
Вы можете удалить видимые, скрытые или пустые строки в Excel, выбирая их одну за другой, затем нажимая клавишу Delete, чтобы удалить их, но если необходимо удалить сотни строк, Удалить скрытые (видимые) строки и столбцы in Kutools for Excel сделаю вам одолжение. С помощью этого инструмента вы можете удалить пустые строки (столбцы), видимые строки (столбцы) или скрытые строки (столбцы) в выбранном диапазоне, активном листе, выбранных листах или всей книге. Нажмите здесь, чтобы получить 60-дневную бесплатную пробную версию этого инструмента.
Удалять видимые значения только с помощью команды Перейти к специальной
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
С этой Перейти к специальному в Excel, вы можете быстро выбрать видимые значения, а затем удалить их с помощью Удалить ключ. Сделайте так:
1. Выберите диапазон, в котором вы хотите удалить видимые данные.
2. Нажмите Главная > Найти и выбрать > Перейти к специальному, см. снимок экрана:
3. В Перейти к специальному диалоговое окно, выберите Видимые ячейки только, см. снимок экрана:
4. Затем нажмите OK, в выбранном диапазоне выбраны все видимые строки. Затем нажмите Удалить Клавиша на клавиатуре, выбранные видимые значения будут удалены, а скрытые значения сохранятся. Смотрите скриншоты:
Внимание:
Таким образом, вы также можете удалить видимые значения столбцов.
Удалять видимые значения только с кодом VBA
Следующий код VBA также может помочь вам удалить видимые значения ячеек только в Excel.
1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модулии вставьте следующий код в окно модуля.
3, Затем нажмите F5 или, чтобы запустить код, и видимые значения ячеек были удалены, а скрытые значения остались.
4. Затем отобразится диалоговое окно, в котором вы можете выбрать диапазон для удаления только видимых ячеек.
Удалить видимые строки только с помощью Kutools for Excel
Два вышеуказанных метода могут удалить только видимые значения, но не все строки, если у вас есть Kutools for Excel, Его Удалить строки и столбцы Функция может помочь вам быстро удалить видимые целые строки или столбцы.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Выберите диапазон, в котором вы хотите применить этот инструмент. Нажмите Кутулс > Удалить > Удалить скрытые (видимые) строки и столбцы, см. снимок экрана:
3. В Удалить строки и столбцы диалоговое окно, выберите В выбранном диапазоне из Заглянуть, затем укажите Ряды и Видимые строки из Удалить тип и Подробный тип по отдельности. Нажмите Ok, и окно подсказки напомнит вам, сколько видимых строк было удалено, щелкните OK чтобы закрыть это. Смотрите скриншот:
1. Вы можете удалить видимые строки или столбцы из выбранный диапазон, выбранные листы, активный лист or все листы под Заглянуть опцию.
2. С помощью этой функции вы также можете удалить только скрытые строки или столбцы, пустые строки или столбцы.
Пожалуйста, нажмите Удалить строки и столбцы чтобы узнать об этой функции.
Прежде чем перейти к теме статьи, разберемся, с чем мы будем иметь дело. Посмотрим на иерархию объектов в Excel. Рабочая книга (Workbook) – это файл Excel, содержащий рабочие листы. Рабочий лист (Worksheet), в свою очередь, представляет собой электронную таблицу, основной тип документа, который используется приложением Excel для хранения и обработки данных. Рабочие листы - это таблицы, состоящие из ячеек, организованных в столбцы и строки и являются частью рабочей книги. Основным элементом электронной таблицы является ячейка. Именно в ячейках содержится информация – текст, значения, формулы. Каждая ячейка имеет свой адрес – номер строки и номер столбца, на пересечении которых находится ячейка. В этой статье более подробно остановимся на строках.
Строки в Excel и основные действия с ними
Количество строк в Excel 2003 – 65 536, а число строк в Excel 2007 и Excel 2010 – уже 1 048 576. Наиболее частыми действиями со строками являются копирование, вставка, добавление новых строк, удаление и скрытие. Без особого труда можно преобразовать Excel строку в столбец и наоборот. Такое преобразование называется транспонированием . Для преобразования строки в столбец необходимо скопировать данные строки, после чего нажать правую кнопку мыши и из контекстного меню выбрать пункт «Специальная вставка…». В появившемся диалоговом окне «Специальная вставка» поставить галочку в поле «транспонировать» и нажать кнопку ОК.
Все манипуляции со строками не представляют какой-либо особой сложности, но задача существенно усложняется, когда работать приходится с большими объемами данных и в особых условиях, когда к примеру нельзя воспользоваться стандартными средствами Excel, такими как сортировка. В таких случаях на помощь как всегда приходят макросы VBA. С помощью макросов VBA Excel строки легко поддаются обработке, можно например скрыть все пустые строки и или удалить строки содержащие заданный текст.
Далее рассмотрим инструмент, в котором реализовано программное скрытие и удаление пустых строк, а также скрытие и удаление любых строк, по заданным пользователем условиям, при этом все параметры для работы программы задаются не в редакторе программного кода, а в диалоговом окне, что очень удобно. Надстройка работает как с видимыми так и со скрытыми и отфильтрованными строками .
Надстройка для удаления и скрытия строк по условию
1) Скрывать либо удалять строки, подходящие под заданное условие;
2) скрывать либо удалять строки, находящиеся ниже или выше строк, подходящих под заданное условие;
3) скрывать либо удалять заданное количество строк для каждого действия;
4) скрывать либо удалять разное количество строк для каждого действия, если количество строк указано в отдельном столбце;
5) выбирать одно из нескольких условий для искомых значений (совпадает, не совпадает, содержит, не содержит, начинается, не начинается, заканчивается, не заканчивается);
6) находить пустые строки и строки, содержащие заданный текст (либо массив из текстовых значений);
7) осуществлять одновременный поиск различных текстовых выражений, внесенных в поле для ввода текста с использованием знака-разделителя. В качестве знака-разделителя используется (;) точка с запятой;
8) выбирать различные диапазоны для обработки данных;
9) ограничивать выбранные диапазоны номерами строк сверху и/или снизу для избежания повреждения шапок таблиц и строк с разного рода промежуточными итогами;
10) осуществлять поиск текста в строках как с учетом регистра, так и без.
Диалоговое окно надстройки
По изображению диалогового окна можно получить представление о том, какие возможности предоставляются надстройкой - это удаление/скрытие пустых строк и строк, подходящих под заданные условия.
Как скрыть или удалить строки по условию?
На выбор пользователя макрос может удалять/скрывать пустые строки, а может на выбор пользователя скрывать либо удалять строки, удовлетворяющие заданным условиям, например таким как содержание заданного текста, либо отсутствием такового, может учитывать регистр при поиске текста в строках, а может не учитывать. Для того, чтобы при удалении/скрытии в таблицах не пострадала шапка или промежуточные результаты, введено дополнительное ограничение по номерам строк, при помощи которого можно задавать диапазон для действия макроса. Если же диапазон не задан пользователем, то макрос работает со строками так называемого используемого диапазона, то есть со строки, в которой содержится первая заполненная информацией ячейка, до строки с последней заполненной ячейкой. Пользователю также предлагается ограничить обработку данных либо активным листом, либо всеми листами активной рабочей книги.
При помощи надстройки можно производить действия не только с самими строками, которые подходят под заданное условие, но и со строками, которые находятся выше или ниже этих строк. Кроме того, предусмотрена возможность задать количество строк для каждого действия.
Как скрывать или удалять разное количество строк?
Если в диалоговом окне надстройки установить флажок в поле "Разное количество строк", то числовое значение в поле "Для каждого действия, шт." меняется на номер столбца, в котором содержатся значения для каждого действия. В этом случае за каждое действие будет скрыто или удалено столько строк, сколько указано в соответствующей строке заданного столбца.
Как скрывать или удалять строки со служебными символами?
При необходимости удаления или скрытия строк со служебными символами, такими как звездочка или знак вопроса, в строке поиска необходимо перед искомым знаком поставить символ "~" тильда, это сигнализирует приложению Excel о том, что следующий за тильдой знак перестает выполнять свои служебные функции и становится обычным символом.
написал тут по работе модуль для удаления пустых строк в Excel.
Самый быстрый способ, который я до этого видел заключался в том, чтобы отсортировать строки функциями Excel и тогда пустые строки будут идти подряд. Но тогда может измениться порядок не пустых строк (они ведь тоже отсортируются).
Самый простой способ - просто удалять методом Range.Delete. Но если строк много, то метод работает ужасно медленно.
Мой способ лишен вышеперечисленных недостатков.
Отзывы о качестве кода категорически приветствуются. Заранее спасибо
Макрос удаления пустых строк в таблице
Имеется таблица. Если в столбце Н строки не содержат никаких значений (пустые) то такие строки.
Изменение кода удаления пустых строк
Нашёл на просторах такой скрипт для удаления пустых строк в таблице, но вот беда, как его изменить.
Организация удаления пустых строк начиная со 2-ой страницы в Word
Есть макрос, который организует удаление пустых строк: Sub bb() Dim n&, n1& n1 =.
А не быстрее ли сначала выделить ВСЕ пустые строки, а потом разом удалить:
самая затратная по времени операция - именно удаление плюс обновление экрана.
и, часто, связанный с этим пересчет формул.
кроме того, если для листа задана обработка событий change или calculate, они тоже будут вызываться.
поэтому стандартным подходом перед "массовым удалением" является отключение обновление экрана, отключение событий, переключение на ручной режим вычислений.
ничего из этого я в вашем коде не вижу.
кроме того, стандартным способом является также уменьшение собственно вызовов метода Delete
при этом пользуются тем, что этот метод работает и с несвязными диапазонами.
поэтому можно, к примеру, с помощью Union собрать достаточно большой набор строк для удаления и удалить их одной-единственной командой.
единственный, но очень важный момент: у метода Union имеется ограничение = 8192 несвязных диапазона.
в большинстве случаев до него дело не доходит.
но для очень больших таблиц этот момент надо учитывать.
Решение
В 43 раза быстрее, но не универсально (хотя можно ввести параметры):
И кстати - тут отключение обновления экрана только тормозит код. Проверено на листе с одним столбцом в 2300 строк.
Catstail, вы правы, получается в 5 раз быстрее Если отключить обновление экрана.
ого, а в справке этого нет. Спасибо, так бы и не знал!
Вообще, ikki, сделал все так, как вы написали, и получилось в 5 раз быстрее, чем мой способ. Спасибо большое!
Hugo121, отличная идея - сначала все загрузить в массив. Попробую так сделать. Но ваш способ, как я понял, только в первом столбце ищет пустые ячейки. А если надо удалять только целиком пустые строки, то как лучше сделать? Вместо If a(i, 1) <> Empty пройти по всей строке? Попробую вечером.
вот моя процедура заполнения листа бредом, для проверки (вдруг кому лень свою писать ):
Кстати, Ваш первый код у меня сперва вообще не отрабатывал, т.к. у меня работает менеджер буфера обмена, и он забивался. Пришлось отключать.
Добавлено через 16 минут
Ну я же сказал - не универсально. Но можно добавить параметры (что проверяем, куда выгружаем метки), или подкрутить по месту.
Да и в массив есть смысл брать только анализируемые столбцы, а не всё - будет быстрее.
Если проверяем на "целиком пустые", то можно например так:
Я еще поэкспериментировал и чего-то у меня тоже ScreenUpdation = False только замедляет макрос. Как такое может быть?
Да, в частном случае, по одному столбцу отлично работает
Почему замедляет screenupdating=false/true? Думаю потому, что выгрузить массив занимает столько же времени, как и показать экран. Его в любом случае нужно показать. А всё остальное - только трата времени (прочитать эти строки кода, среагировать, отключить/включить).
Хотя не теоретик, точно не знаю
Hugo121, Cогласен полностью.
sulfur Было бы не плохо файл в студию, чтобы понять как лучше обработать строки.
Да, в частном случае, по одному столбцу отлично работает
Sheets(1).Columns(1).SpecialCells(xlCellTypeBlanks).EntireRo w.Delete
Те, что сюда не попали, можно даже не проверять. Попробую написать алгоритм основанный на этой фишке и доложу о его скорости.
xmypka, проще уж запустить мою процедуру генерации тестового листа Реальный файл, на котором я работаю, будет примерно таким же.
Ну, думаю уже можно публиковать результаты моих исследований.
Я взял Range размером 90`000 рядов на 5 колонок. Заполнил его случайным образом так, чтобы 1-2% строк были пустыми. Кроме того, чтобы 4% ячеек были пустыми.
В таком случае лучше всего работал алгоритм Hugo121: загрузить ячейки в массив и вручную (без формул и встроенных функций) все найти. Затем вернуть массивы в ячейки и удалить помеченные ряды с помощью ColumnDifferences().
Почти не отстает от него алгоритм toiai, который пытается с помощью встроенного метода SpecialCells(xlCellTypeBlanks) выбрать для обработки только те ряды, в которых есть пустые ячейки. Таким образом отфильтровав некоторую часть рядов. Но беда в том, что метод SpecialCells(xlCellTypeBlanks) при большом количестве разделенных областей перестает выбирать пустые ячейки и просто возвращает тот Range для которого его применили. Но и если он срабатывает правильно - это все равно не сильно ускоряет работу (на моем тестовом примере).
Следующий по скорости идет простой обход всех рядов подряд и сбор через Union. А затем удаление. Это вариант комрадов Catstail и ikki. Он простой и надежный, но на моем тестовом примере работает в два раза медленнее, чем фаворит.
Хуже всех показал себя мой вариант, который даже стыдно сказать насколько медленнее На порядок - это как минимум.
На методе SpecialCells(xlCellTypeBlanks) мне удалось создать еще один вариант, который был даже на четверть быстрее фаворита и довольно прост в записи. Но, как я уже говорил, метод SpecialCells(xlCellTypeBlanks) при большом количестве пробелов возвращает борщ и не только не ускоряет, но и приводит к ошибочному результату.
Так что вот победитель в моей интерпретации:
PS:
Алгоритм предназначен для не очень разреженных таблиц.
Если в вашей таблице много пустых рядов (более 2%) - используйте его на свой страх и риск.
Выводы, которые я сделал в ходе исследований:
Для больших таблиц встроенные функции Excel не предназначены и использовать их надо на свой страх и риск. Это касается и Union, и ColumnDifferences и SpecialCells.
Работа с памятью намного быстрее, чем работа с ячейками.
Одно большое удаление работает намного быстрее, чем несколько маленьких.
Как удалить все скрытые строки или столбцы в Excel?
Если у вас есть большая книга с большим количеством скрытых строк и столбцов, и теперь скрытые данные недоступны, вы хотите удалить их все. Как можно было их быстро удалить?
Kutools for Excel's Delete Hidden (Visible) Rows & Columns utility can help you to delete multiple blank, hidden, visible rows or columns at once.
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
Удалите все скрытые строки и столбцы в книге с помощью функции проверки документа
Если вы используете Excel 2007, 2010 или Excel 2013, вам легко удалить ненужные скрытые строки или столбцы. Вы можете сделать это следующим образом:
1. Если вы используете Excel 2007, нажмите Офисы Кнопку в верхнем левом углу, а затем щелкните Подготовить > Осмотреть документ. Смотрите скриншот:
Если вы используете Excel 2010/2013, нажмите Файл > Инфо > Проверить наличие проблем > Осмотреть документ, смотрите скриншоты:
В Excel 2013:
В Excel 2010:
2, Затем Инспектор документов появится диалоговое окно, щелкните Инспектировать кнопку, и были проверены скрытые строки и столбцы во всей книге, щелкните Удалить все кнопку, см. снимок экрана:
3, Затем нажмите Закрыть кнопку, чтобы закрыть диалоговое окно. И все скрытые строки и столбцы в книге будут удалены.
Заметки: 1. Если есть формулы для управления данными в вашей книге, и формулы требуют данных из скрытых строк и столбцов, то удаление этой информации приведет к неверному результату.
2. С помощью этой функции вы можете удалить все скрытые строки и столбцы во всей книге.
Удалите все скрытые строки и столбцы на активном листе с кодом VBA
Если вы просто хотите удалить скрытые строки или столбцы на листе, следующий код VBA может вам помочь. Пожалуйста, сделайте следующее:
1. Удерживая нажатой ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модулии вставьте следующий код в Окно модуля.
Код VBA: удалить все скрытые строки и столбцы активного листа:
2, Затем нажмите F5 ключ для выполнения кода. И все скрытые строки и столбцы были удалены на активном листе.
Внимание: Если есть формулы для управления данными в вашей книге, и формулы требуют данных из скрытых строк и столбцов, то удаление этой информации приведет к неверному результату.
Удалите все скрытые строки или столбцы в выделении / листе / всей книге с помощью Kutools for Excel
Для Удалить скрытые (видимые) строки и столбцы полезности Kutools for Excel, вы можете быстро удалить скрытые строки или столбцы в выбранном диапазоне, на активном листе, на выбранных листах и на всех листах.
Если вы установили Kutools for Excel, пожалуйста, сделайте так:
1. Нажмите Кутулс > Удалить > Удалить скрытые (видимые) строки и столбцы, см. снимок экрана:
2. В Удалить скрытые (видимые) строки и столбцы диалоговом окне укажите область, в которой вы хотите применить операцию из Заглянуть раскрывающийся список. (Если вы выберете В выбранном диапазоне, вы должны сначала выбрать диапазон, который хотите использовать.) А затем отметьте Ряды or Колонны под Удалить тип, И выберите Скрытые строки из Подробный тип. Затем нажмите Ok, скрытые строки были удалены из выбранного диапазона, см. снимок экрана:
Чтобы узнать больше об этой функции, посетите Удалить строки и столбцы функции.
Демо: удалите все скрытые строки или столбцы в выделении / листе / всей книге с помощью Kutools for Excel
Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!
Как отфильтровать список и удалить остальные скрытые или видимые строки в Excel?
Для отфильтрованного списка вам может потребоваться удалить скрытые или видимые строки, чтобы сохранить только полезные данные. В этой статье мы покажем вам способы удаления оставшихся скрытых или видимых строк отфильтрованного списка в Excel.
Удалить скрытые строки на активном листе с кодом VBA
В этом разделе будет показан код VBA для удаления скрытых строк на активном листе. Пожалуйста, сделайте следующее.
1. Активируйте рабочий лист, который вам нужен для удаления скрытых строк, нажмите другой + F11 клавиши одновременно, чтобы открыть Microsoft Visual Basic для приложений окно.
2. В окне Microsoft Visual Basic для приложений щелкните Вставить > Модули. Затем скопируйте и вставьте приведенный ниже код VBA в окно модуля.
Код VBA: удалить скрытые строки
3. нажмите F5 ключ для запуска кода. Если на активном листе есть скрытые строки, после запуска кода появится диалоговое окно, в котором будет указано, сколько скрытых строк было удалено. Щелкните значок OK кнопку, чтобы удалить скрытые строки. Смотрите скриншот:
В противном случае после запуска кода вы получите следующее диалоговое окно.
Внимание: приведенный выше код VBA не только может удалять скрытые строки отфильтрованного списка, но также удалять скрытые строки, которые вы ранее скрывали вручную.
Удалить видимые строки отфильтрованного списка с выделением всех видимых ячеек.
Чтобы удалить видимые строки отфильтрованного списка, сделайте следующее.
1. Выберите все отфильтрованные строки и нажмите F5 , чтобы открыть Перейти к диалоговое окно, затем щелкните Особый кнопка. Смотрите скриншот:
2. в Перейти к специальному диалоговое окно, проверьте Только видимые ячейки вариант, а затем щелкните OK кнопку.
3. Теперь все видимые строки выделены, щелкните выделение правой кнопкой мыши и затем щелкните Удалить строки.
До сих пор все видимые строки удаляются из отфильтрованного списка.
Легко удаляйте скрытые или видимые строки отфильтрованного списка с помощью Kutools for Excel
Вышеупомянутые два метода могут не быть желанными решениями для многих пользователей Excel, здесь мы представляем вам удобный инструмент. Для Удалить скрытые (видимые) строки и столбцы полезности Kutools for Excel, вы можете легко удалить скрытые строки в выбранном диапазоне / листах, активном листе или всех листах в Excel.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Если вы хотите удалить только скрытые или видимые строки отфильтрованного списка, выберите отфильтрованный диапазон вручную, а затем нажмите Кутулс > Удалить > Удалить скрытые (видимые) строки и столбцы. Смотрите скриншот:
2. в Удалить скрытые (видимые) строки и столбец диалоговое окно, оставьте В выбранном диапазоне выбранный в Заглянуть раскрывающийся список (вы можете выбрать другие варианты по своему усмотрению), отметьте Ряды вариант в Удалить тип раздел, а в Подробный тип раздел, проверка Видимые строки or Скрытые строки вариант как вам нужно. И, наконец, нажмите кнопку OK кнопку.
3. Затем появится диалоговое окно, в котором указано, сколько строк было удалено. Щелкните значок OK кнопку.
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Читайте также: