Удалить скрытые строки в excel vba
Освоение Excel занимает много времени и усилий. Если вы не являетесь экспертом, освоение расширенных функций может быть сложным процессом. К сожалению, не все команды четко видны в интерфейсе.
Удаление скрытых строк является прекрасным примером. В более старых версиях Excel это было невозможно. К счастью, Microsoft решила добавить его в версии 2007 и новее. Тем не менее, как это работает, загадка для многих.
Есть два способа удаления скрытых строк в Excel. Давайте пройдемся по ним.
Использование функции проверки документа
Функция «Инспектор документов» доступна в Excel, Word, PowerPoint и Visio. Это лучший способ обнаружить и удалить любые скрытые данные, которые могут присутствовать в документе. Это полезно, когда вам нужно поделиться документами с другими людьми и хотите убедиться, что в документе нет сюрпризов.
В Excel удаление скрытых строк и столбцов является простой задачей. Вот что вам нужно сделать:
Эту функцию можно найти в том же месте в Excel 2013 и 2016. Интерфейс версии 2010 выглядит немного иначе, но путь к инспектору документов тот же. Если вы используете Excel 2007, вы можете найти эту опцию, нажав кнопку Office, а затем перейдите в раздел «Подготовка»> «Проверить документ».
Нет никакой разницы в самой функции, поэтому это обеспечит отсутствие скрытых строк и столбцов независимо от вашей версии Excel.
Использование кода VBA
Это очень удобный метод, если вам нужно только удалить скрытые строки и столбцы с одного листа вместо всей книги. Возможно, он не так эффективен, как функция проверки документов, но это очень простой и быстрый способ удаления строк из листа. Вот как это сделать:
- Откройте файл Excel и нажмите Alt + F11, чтобы открыть редактор VBA.
- Пойдите, чтобы Вставить> Модуль.
- Когда появится окно модуля, вставьте в него следующий код:
Sub deletehidden ()
Для lp = 256 К 1 Шаг -1
If Columns (lp) .EntireColumn.Hidden = True Затем столбцы (lp) .EntireColumn.Delete Else
следующий
Для lp = 65536 до 1 шага -1
If Rows (lp) .EntireRow.Hidden = True Тогда строк (lp) .EntireRow.Delete Else
следующий
End Sub
Это удалит все скрытые строки и столбцы с листа, над которым вы работаете. Если их не много, вы можете повторить этот процесс, чтобы в кратчайшие сроки очистить их от всей книги.
Использование стороннего программного обеспечения
Существует множество сторонних решений, которые могут помочь вам мгновенно удалить скрытые строки и столбцы. Они обычно служат расширением Excel, добавляя дополнительные параметры на панель инструментов. Помимо этого, они также предлагают дополнительные функции, которые могут быть трудно найти, такие как:
- Пакетные флажки удаления
- Пакетное удаление опционных кнопок
- Удалить пустые строки,
- Пакетное удаление всех макросов
Они могут оказаться очень полезными, если вы являетесь опытным пользователем Excel и ищете быстрые решения общих проблем, которые Microsoft еще предстоит решить должным образом.
Последнее слово
Тот факт, что Excel оснащен возможностью обнаруживать и удалять скрытые строки и столбцы, делает его более удобным для использования. Благодаря Document Inspector, вам не нужно беспокоиться о скрытой информации.
Если вам нравится программирование, выполнение кода VBA является более удобным решением, которое вы можете сделать, даже если у вас нет опыта программирования. Все, что вам нужно сделать, это вырезать и вставить правильные команды.
Наконец, вы можете выбрать стороннее решение, которое поможет вам максимально использовать возможности Excel. Они могут стать отличным обновлением программного обеспечения и значительно упростить его использование.
Если у вас есть большая книга с большим количеством скрытых строк и столбцов, и теперь скрытые данные недоступны, вы хотите удалить их все. Как можно было их быстро удалить?
Удалите все скрытые строки и столбцы в книге с помощью функции проверки документа
Если вы используете 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. Нажмите на Kutools > Удалить > Удалить скрытые (видимые) строки и столбцы, см. снимок экрана:
2. В Удалить скрытые (видимые) строки и столбцы диалоговом окне укажите область, в которой вы хотите применить операцию из Заглянуть раскрывающийся список. (Если вы выберете В выбранном диапазоне, вы должны сначала выбрать диапазон, который хотите использовать.) А затем отметьте Ряды or Колонны под Удалить тип, И выберите Скрытые строки от Подробный тип. Затем нажмите Ok, скрытые строки были удалены из выбранного диапазона, см. снимок экрана:
Чтобы узнать больше об этой функции, посетите Удалить строки и столбцы функции.
В Microsoft Office Excel можно быстро удалить скрытые, пустые строчки, которые портят внешний вид табличного массива. О том, как это сделать будет рассказано в данной статье.
Как деинсталлировать скрытые строки в Эксель
Существует несколько способов выполнения поставленной задачи, реализуемых с помощью стандартных инструментов программы. Далее будут рассмотрены самые распространенные из них.
Способ 1. Как удалять строчки в таблице по одной через контекстное меню
Обратите внимание! Рассмотренным методом также можно удалять скрытые столбцы.
Способ 2. Одиночная деинсталляция строк через опцию в ленте программы
В Excel есть стандартные инструменты для удаления ячеек табличного массива. Чтобы воспользоваться ими для удаления строчек, необходимо действовать так:
- Выделить любую ячейку в строке, которую нужно удалить.
- Перейти во вкладку «Главная» в верхней панели Excel.
- Найти кнопку «Удалить» и развернуть эту опцию, щелкнув по стрелочке справа.
- Выбрать вариант «Удалить строки с листа».
- Удостовериться, что выделенная ранее строчка была деинсталлирована.
Способ 3. Как удалить все скрытые строчки сразу
В Экселе также реализована возможность групповой деинсталляции выбранных элементов табличного массива. Такая возможность позволяет убрать пустые строчки, разбросанные по разным частям таблички. В общем виде процесс деинсталляции подразделяется на следующие этапы:
Важно! Рассмотренный выше способ групповой деинсталляции может применяться только для абсолютно пустых строк. В них не должна содержаться какая-либо информация, иначе использование метода приведет к нарушению структуры таблицы.
Способ 4. Применение сортировки
Актуальный метод, который выполняется по следующему алгоритму:
- Выделить шапку таблицы. Это область, в которой будет производиться сортировка данных.
- Во вкладке «Главная» развернуть подраздел «Сортировка и фильтр».
- В появившемся окне выбрать вариант «Настраиваемая сортировка», щелкнув по нему ЛКМ.
- В меню настраиваемой сортировки поставить галочку напротив параметра «Мои данные содержат заголовки».
- В столбце Порядок указать любой из вариантов сортировки: либо «От А до Я», либо «От Я до А».
- По окончании настройки сортировки нажать на «ОК» внизу окошка. После этого данные в табличном массиве будут отсортированы по указанному критерию.
- По схеме, рассмотренной в предыдущем разделе статьи, выделить все скрытые строки и удалить их.
Сортировка значений автоматически выставляет все пустые строчки в конец таблички.
Дополнительная информация! После сортировки информации в массиве скрытые элементы можно будет деинсталлировать, выделив их все и в контекстном меню нажать по пункту «Удалить».
Способ 5. Применение фильтрации
В таблицах Excel возможно отфильтровать заданный массив, оставив только нужную информацию в нем. Этим способом можно убрать любую строку из таблицы. Важно действовать по алгоритму:
- Левой клавишей манипулятора выделить шапку таблички.
- Перейти в раздел «Данные», располагающийся сверху главного меню программы.
- Нажать на кнопку «Фильтр». После этого в заголовке каждого столбца массива появятся стрелочки.
- Нажать ЛКМ по любой стрелке, чтобы развернуть список доступных фильтров.
- Снять галочки со значений в нужных строчках. Для деинсталляции пустой строки, потребуется указать ее порядковый номер в табличном массиве.
- Проверить результат. После нажатия на «ОК» изменения должны вступить в силу, а выбранные элементы удалиться.
Обратите внимание! Данные в составленном табличном массиве можно быстро отфильтровать по различным критериям. Например, по цвету ячеек, по дате, по названиям столбцов и т.д. Эта информация подробно изложена в окошке выбора фильтра.
Заключение
Таким образом, в Microsoft Office Excel деинсталлировать скрытые строчки в таблице достаточно просто. Для этого не обязательно быть опытным пользователем Эксель. Достаточно воспользоваться одним из вышеуказанных способов, которые работают независимо от версии программного обеспечения.
Переименование листов
В VBA Excel есть некоторые особенности в наименовании листов, так как у рабочего листа есть два свойства, связанных с именем: (Name) и Name. Откройте окно «Properties» в редакторе VBA, нажав клавишу «F4», и выделите любой лист в проводнике. Вы увидите, что в окне «Properties» свойству (Name) в скобках соответствует в проводнике уникальное имя листа без скобок, а свойству Name без скобок соответствует изменяемое имя листа в скобках. Оба имени в окне «Properties» можно редактировать.
С помощью кода VBA Excel можно редактировать только имя листа Name, отображаемое на ярлычке листа и в проводнике без скобок. Для этого используется свойство рабочего листа Worksheets.Name со следующим синтаксисом:
expression.Name
где expression – переменная, представляющая собой объект Worksheet. Смена имени осуществляется путем присвоения нового значения свойству Worksheets.Name.
Допустим, у нас есть лист с уникальным именем (Name) – Лист1, индексом – 1 и именем Name – МойЛист, которое необходимо заменить на имя – Реестр.
Как скрыть лист в Excel?
Процедура скрытия листа достаточно простая — в нижней панели щелкаем правой кнопкой мыши по названию листа, который хотим сделать невидимым, и в выпадающем меню выбираем команду Скрыть:
Если нужно спрятать сразу несколько листов, то удерживая клавишу Ctrl выделяем нужные листы с помощью мышки и повторяем вышеописанные действия (щелкаем правой кнопкой мыши по любому из выделенных листов и далее нажимаем Скрыть).
Группировка
В Excel 2007 и новее все эти радости находятся на вкладке Данные (Data) в группе Структура (Outline) :
Как сделать очень скрытый лист в Excel?
Параметры отображения любого листа в Excel мы можем настроить посредством Visual Basic. Переходим в редактор Visual Basic (на панели вкладок выбираем Разработчик -> Код -> Visual Basic или воспользуемся комбинацией клавиш Alt + F11):
В левой верхней части окна в списке выбираем нужный нам лист и чуть ниже подробно смотрим в блок с настройками (если он изначально не отображается, то уже в панели вкладок Visual Basic выбираем View -> Properties Window и блок появится).
За отображение листа в параметрах отвечает свойство Visible и как мы видим всего есть 3 варианта:
- xlSheetVisible. Лист открыт и отображается во вкладках;
- xlSheetHidden. Лист скрыт, но отображается в списке невидимых листов;
- xlSheetVeryHidden. Лист скрыт и нигде не отображается.
Таким образом, чтобы скрыть лист средствами Visual Basic указываем значение параметра xlSheetHidden, а чтобы показать устанавливаем xlSheetVisible.
Из названия последнего значения xlSheetVeryHidden и взялся термин очень скрытого листа, но в принципе также часто можно встретить употребление выражение супер скрытого. При этом, что очень важно, установить значение параметра xlSheetVeryHidden можно только через Visual Basic, что и не позволяет обычному пользователю обнаружить очень скрытые листы стандартным способом.
Вернемся к нашему примеру.
Так как мы в начале скрывали Лист1, то для него установлена видимость xlSheetHidden, а нас сейчас интересует как раз 3 вариант. Выбираем его и теперь в списке скрытых листов пусто (кнопка в данном случае не активна, хотя мы знаем, что очень скрытые листы в книге на самом деле есть):
Теперь пользователь не увидит такой супер скрытый лист в интерфейсе Excel, однако он все же сможет его обнаружить если зайдет в редактор Visual Basic.
Если же стоит задача найти такие листы и отобразить их, то повторяем аналогичные действия и в Visual Basic для подобных листов устанавливаем параметр xlSheetVisible, после чего все листы станут видимыми.
Через надстройку VBA-Excel
Скрыть строки вручную
Как показать скрытый лист в Excel?
Чтобы открыть скрытый лист обратно мы также в нижней панели правой кнопкой мыши щелкаем по любому из листов и выбираем команду Показать. В появившемся окне можно посмотреть все невидимые листы книги (даже если он всего один) и выбрать какой именно лист необходимо вывести на экран:
Со скрытием и отображением листов разобрались, давайте перейдем к чуть более сложной задаче, а именно научимся скрывать лист так, чтобы обычный пользователь не смог его отобразить.
С помощью свойства листов в редакторе кода
Для того чтобы скрыть лист необходимо:
Скрываем листы
Чтобы скрыть лист (ярлычки в самом низу окна Excel), кликните правой кнопкой мыши по ярлычку, который нужно скрыть, и в контекстном меню нажмите Скрыть
(Hide). Ярлычек исчезнет, но не удалится окончательно.
Нельзя скрыть все листы рабочей книги. Если Вы попытаетесь скрыть последний видимый лист, то получите вот такое предупреждение:
Книга должна содержать не менее одного видимого листа.
Чтобы скрыть, удалить или перенести выделенные листы, необходимо сначала вставить новый лист или сделать видимым скрытый лист.
Чтобы отобразить лист, кликните правой кнопкой мыши по любой видимой вкладке и выберите в появившемся меню Показать
(Unhide).
В появившемся диалоговом окне (Unhide) выберите лист, который хотите сделать видимым, и нажмите ОК.
Замечание:
За одно действие Вы можете сделать видимым только один лист.
Кроме этого, показать скрытые листы можно другим способом. Перейдите на вкладку Главная
(Home). В разделе Ячейки
(Cells) нажмите Формат
(Format). В группе команд Видимость
(Visibility) нажмите Скрыть и отобразить
(Hide & Unhide) и в открывшемся меню выберите Отобразить лист
(Unhide Sheet).
Появится диалоговое окно Вывод на экран скрытого листа
(Unhide). Выберите нужный лист и нажмите ОК, как было описано выше.
Скрываем рабочую книгу полностью
Чтобы полностью скрыть рабочую книгу Excel, перейдите на вкладку Вид
(View).
В разделе Окно
(Window) нажмите Скрыть
(Hide).
Окно Excel останется открытым, но область таблицы станет пустой. Чтобы отобразить рабочую книгу, нажмите Вид
> Окно
> Отобразить
(View > Window > Unhide).
Появится диалоговое окно Вывод на экран скрытого окна книги
(Unhide) со списком открытых в настоящий момент рабочих книг. Если Вы скрывали рабочие книги, которые в данный момент не открыты, то их в этом списке не будет.
Если Вы скроете рабочую книгу и сохраните файл, то в следующий раз, когда откроете этот файл, книга по-прежнему будет скрыта. Книга будет оставаться скрытой до тех пор, пока Вы сами не отобразите её.
Иногда возникают ситуации, когда надо скрыть лист со всем его содержимым от посторонних любопытных глаз. Очень часто это делается из контекстного меню листа (для счастливых обладателей 2007 Excel. Подробнее: ). Но при использовании данного метода необходимо защитить книгу, иначе отобразить скрытые листы сможет каждый (тем же правым щелчком мыши — Отобразить).
А что делать, если книгу не надо защищать? Ведь часто необходимо оставить пользователям возможность работы со структурой книги, например, добавлять листы. Как тогда скрыть лист так, чтобы его нельзя было отобразить? Есть достаточно простой способ через редактор VBA, при этом обладать навыком программирования вообще не нужно. Итак, определились какой лист надо скрыть.
- заходим в редактор VBA(Alt+F11)
- жмем сочетание клавиш Ctrl+R или идем в меню View—Project Explorer
(чтобы отобразить окно объектов проекта VBA) - жмем F4или через View—Properties Window
(чтобы отобразить окно свойств) - в левой части окна (в Project Explorer) ищем книгу, лист в которой надо скрыть и раскрываем её папку(на скрине это VBA Project (Книга1))
- раскрываем папку Microsoft Excel Objects и выделяем там лист с нужным именем
- в Окне свойств (Properties Window) находим свойство Visibleи назначаем ему значение xlSheetVeryHidden
Чтобы затем отобразить этот лист необходимо свойству Visible задать значение — xlSheetVisible.
При этом для отображения листа необходимо будет обязательно зайти в редактор VBA — простым методом отобразить не получится. И книгу защищать не надо. Что нам и было необходимо.
Для большей надежности можно защитить проект VBA, чтобы не было возможности отобразить лист из VBA, не указав пароль. На функционал это не повлияет совершенно.
В том же окне VBA(Alt+F11):
- Tools—VBAProject Properties-вкладка Protection
- вписывается/удаляется сам пароль в полях Passwordи Confirm password.
Скрыть все листы в книге, кроме листа с именем «Видимый» можно следующим макросом:
Sub Hide_All_Sheets() Dim wsSh As Object For Each wsSh In ActiveWorkbook.Sheets If wsSh.Name «Видимый» Then wsSh.Visible = xlSheetVeryHidden «отобразить – xlSheetVisible; сделать лист просто скрытым — xlSheetHidden Next wsSh
End Sub
Скрывать листы можно используя контекстное меню, вызванное правой кнопкой мышки по ярлычку листа. Как было описано в предыдущем уроке. Но листы можно скрывать так, чтобы их не было видно даже в списке скрытых листов. Причем без использования защиты структуры книги. Для реализации данной задачи нужно воспользоваться параметрами, которые доступны в режиме VBA-редактора (Alt+F11).
Полезный Лайфхак в Excel
Чтобы запретить вставку новых строк и столбцов в Excel без использования защиты листа нужно сделать так:
Вот и все теперь в лист нельзя вставить строку или столбец. Если нужно запретить вставлять только строки, тогда вводим любое значение в самую последнюю строку (например, A1048576). А если запретить вставлять только столбцы тогда вводим любое значение в последнем столбце (например, XFD1).
Надежное скрытие листов, которых нельзя отобразить стандартными средствами (опцией Показать). Полезные лайфхаки для работы с листами и ячейками.
Любой лист книги можно скрыть, чтобы удалить его из представления. Можно также скрыть окно книги, чтобы удалить его из рабочей области. Данные скрытых листов и книг не видны, но на них по-прежнему могут указывать ссылки из других листов и книг. При необходимости скрытые листы и книги можно отобразить.
По умолчанию на панели задач отображаются все книги, которая открывается, но можно скрыть или отобразить их на панели задач при необходимости.
Примечание:
Снимки экрана в этой статье получены в Excel 2016. Если вы используете другую версию, интерфейс может немного отличаться, но функции будут такими же.
Скрытие и отображение окон книг на панели задач Windows
Excel 2013 реализована в Единый интерфейс документа, где каждая книга откроется в отдельном окне. Указанные ниже действия применяются только к Excel 2007 и 2010, для Windows.
Как найти все скрытые строки на листе Excel с помощью макроса
У нас иметься таблица с данными по заказам, но некоторые строки листа скрыты:
Необходимо узнать сколько и какие строки скрытии от визуального анализа таблицы заказов. По умолчанию Excel не располагает таким инструментом, который смог бы решить данную задачу в пару кликов мышкой. Вручную выполнить задачу можно, но сложно. Особенно если после отображения скрытых строк нам нужно снова их скрывать. Кроме того, лист может быть защищен от изменений паролем. Оптимальным решением данной задачи будет написание собственного макроса для отображения информации о скрытых строках.
Чтобы написать свой макрос откройте редактор кода макроса: «РАЗРАБОТЧИК»-«Код»-«Visual Basic» или нажмите комбинацию клавиш ALT+F11:
В редакторе вставьте новый модуль выбрав инструмент «Insert»-«Module» и введите в него этот код:
Sub HiddenLinesInfo()
Dim i As Long
Dim text As String
Dim pervoj As String
text = "В данном листе скрыты следующие строки: "
For i = 1 To ActiveSheet.Rows.Count
If ActiveSheet.Rows(i).Hidden = True Then
If pervoj = "" Then
pervoj = i
End If
Else
If pervoj <> "" Then
text = text & vbNewLine & pervoj & ":" & i - 1
pervoj = ""
Else
If i > ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row Then
Exit For
End If
End If
End If
Next
MsgBox text
End Sub
Теперь если в таблице заказов необходимо получить информацию о скрытых строках, тогда выберите инструмент для запуска макроса: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«HiddenLinesInfo»-«Выполнить».
Скрыть столбцы используя сортировку
Если пустых столбцов много и искать их зрительно проблематично, то можно использовать сортировку для того чтобы сгруппировать и после этого скрыть их, выделив сразу весь диапазон либо просто вывести за пределы печати. Минусом такого способа является то, что очередность столбцов изменяется.
Таблица в верхнем левом углу
В первую очередь рассмотрим все доступные варианты поиска номера последней заполненной строки для таблиц, расположенных в верхнем левом углу рабочего листа. Такие таблицы обычно представляют собой простые базы данных в Excel, или, как их еще называют, наборы записей.
Вариант 1
Основная формула для поиска последней строки в такой таблице, не требующая соблюдения каких-либо условий:
PosStr = Cells(1, 1).CurrentRegion.Rows.Count
Вариант 3
В первом столбце таблицы не должно быть пропусков, а также в таблице должно быть не менее двух заполненных строк, включая строку заголовков:
PosStr = Cells(1, 1).End(xlDown).Row
Читайте также: