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

Обновлено: 02.07.2024

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

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

И, если у вас много таких скрытых строк/столбцов, может быть проблематично найти и удалить эти скрытые строки и столбцы (в случае, если они вам не нужны).

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

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

Итак, приступим!

Это руководство охватывает:

Удалить все скрытые строки и столбцы в Excel

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

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

Ниже приведены шаги по удалению всех скрытых строк и столбцов из книги в Excel:

Вышеуказанные шаги удалят все скрытые строки и столбцы в книге.

Обратите внимание, что перед запуском Инспектора документов необходимо сохранить эту книгу. Если книга еще не сохранена, Excel сначала заставит вас сохранить ее. Кроме того, он покажет вам предупреждение с просьбой сохранить файл один раз, так как данные, измененные этими шагами, не могут быть восстановлены.

Если нет скрытых строк и столбцов, вы увидите зеленую галочку перед параметром «Скрытые» и «Строки и столбцы» (в диалоговом окне «Инспектор документов»).

Помимо скрытых строк и столбцов, диалоговое окно "Инспектор документов" также предоставляет много другой полезной информации, например количество комментариев, скрытых рабочих листов, встроенных документов и т. д.

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

Это отличный способ, если вы хотите удалить все скрытые столбцы и строки во всей книге.

Но если вы хотите удалить его только на определенном листе или в определенном диапазоне, вы не можете использовать эту опцию.

В этом случае вы можете использовать описанный ниже метод VBA.

Удалить скрытые строки и столбцы с помощью VBA

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

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

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

Из всего рабочего листа (используемый диапазон)

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

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

Ниже приведен код VBA, который удалит все скрытые строки в используемом диапазоне:

Приведенный выше код VBA сначала определяет номер последней строки в используемом диапазоне и присваивает этот номер строки переменной LastRow.

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

Если он скрыт, вся строка удаляется. И если он не скрыт, код оставляет эту строку как есть и перемещается к строке над ней. Этот цикл проверяет все строки и удаляет все скрытые строки, которые встречаются в процессе.

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

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

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

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

Это всего лишь комбинированный код для строк и столбцов, и он работает одинаково.

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

Обратите внимание, что этот код VBA необходимо поместить в обычный модуль в редакторе Visual Basic. Затем вы можете запустить код непосредственно из редактора VB, используя диалоговое окно макроса или добавив этот макрос на панель быстрого доступа.

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

Из определенного диапазона ячеек

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

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

Ниже код VBA, который это сделает:

В приведенном выше коде я указал диапазон A1:K200.

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

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

Вот как вы можете удалить скрытые строки и столбцы в Excel.

Если вы хотите сделать это для всей книги, вы можете использовать Инспектор документов. А если вам нужно больше контроля, вы можете использовать коды VBA, как показано выше.

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

Загрузить практическую рабочую тетрадь

Вы можете скачать учебную тетрадь отсюда.

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

1. Параметр «Проверить документ» для удаления скрытых строк в Excel

Опция «Проверить документ» — эффективный способ скрыть строки. Он удаляет скрытые строки из всей книги. Таким образом, этот метод неприменим, если мы хотим удалить строки только с одного рабочего листа. Для этого нам нужно использовать VBA, о чем мы поговорим в последней части этой статьи.

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

Здесь, чтобы скрыть выделенные строки, выберите строки.
Щелкните правой кнопкой мыши порядковый номер ячейки.
Затем нажмите «Скрыть».

Итак, мы видим, что выделенные строки не видны.

Теперь мы выполним несколько шагов, чтобы удалить эти скрытые строки.

Во-первых, мы добрались до параметра "Файл" на ленте.

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

Появится такое окно. В этом случае выберите Да.

Затем в окне инспектора документов выберите параметр «Проверить».

После этого в новом окне прокрутите вниз. Выберите параметр «Удалить все» для раздела «Скрытые строки и столбцы».

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

Примечание:

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

2. Добавление временного столбца для удаления скрытых строк

Другой подход – удалить скрытые строки, добавив временный столбец. Мы будем использовать фильтрацию, чтобы применить этот метод. Предположим, в следующем наборе данных мы просто хотим сохранить данные о продажах в Нью-Йорке и Бостоне. Мы удалим остальные строки. Просто следуйте простым шагам, чтобы выполнить это:

Сначала выберите весь набор данных (B4:G14).

Перейдите к параметру "Сортировка и фильтрация". В раскрывающемся списке выберите параметр Фильтр.

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

Теперь перейдите к раскрывающемуся меню фильтрации в заголовке "Местоположение". Просто выберите вариант Нью-Йорк и Бостон.

Затем нажмите OK.

Здесь мы получим данные о продажах только для Нью-Йорка и Бостона. Другие строки теперь скрыты.

Далее добавьте новый столбец. Назовите его временным. Введите значение 0 в ячейку H5.
Перетащите инструмент «Ручка заливки».

Мы получаем значение 0 для всех строк.

Снова выберите весь набор данных (B4:B15).

Затем перейдите в раскрывающееся меню "Сортировка и фильтрация". Выберите параметр Фильтр.

Здесь теперь видны все скрытые данные. Мы можем видеть 0 только в тех строках, которые хотим сохранить.

Мы снова выберем весь набор данных (B4:G15).

Перейдите к разделу "Сортировка и фильтрация".В раскрывающемся списке выберите параметр Фильтр.

Мы видим раскрывающиеся списки фильтрации в строках заголовков набора данных.

Перейдите к раскрывающемуся меню в столбце "Временные".
Здесь мы отменяем выбор опции 0.
Нажмите "ОК".

Выберите все строки. Щелкните правой кнопкой мыши и выберите параметр "Удалить строку".

Все строки без значения 0 теперь удалены.

Теперь перейдите к раскрывающемуся списку временных столбцов. Выберите вариант 0.
Нажмите "ОК".

Наконец, у нас есть только набор данных для городов Нью-Йорк и Бостон.

Похожие чтения:

3. Использование VBA для удаления скрытых строк в Excel

Использование VBA — это расширенный метод удаления скрытых строк. Мы можем сделать это для двух частных случаев. Один удаляет строки из одного рабочего листа. Другой способ — удалить строки из определенного диапазона набора данных.

3.1 Удаление скрытых строк со всего листа

Предположим, у нас есть следующий набор данных о продажах. В нашем экспериментальном наборе данных выделенные строки скрыты. Мы удалим эти строки с помощью VBA.

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

Следует отметить, что если у нас нет вкладки "Разработчик" в Excel, мы должны активировать вкладку "Разработчик", чтобы создать содержимое с поддержкой макросов.

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

Перейдите к опции «Файл» в верхнем левом углу нашего Excel.

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

После этого выберите вариант "Разработчик" и нажмите "ОК".

Наконец, мы можем увидеть вкладку "Разработчик" в нашем Excel.

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

Перейдите на вкладку "Разработчик". Выберите параметр Visual Basic.

Откроется новое окно.В окне выберите вкладку «Вставка». В раскрывающемся списке выберите параметр «Модуль». Мы получим новый модуль с именем Module-1.

Выберите вариант «Модуль-1». Откроется пустое окно. Вставьте следующий код в пустое окно.

Мы нажмем на опцию запуска, которую мы видим на изображении ниже. Мы также можем нажать F5, чтобы запустить код.

В результате мы видим, что удалили все скрытые строки.

3.2 Удаление скрытых строк из определенного диапазона

В этом примере мы будем использовать VBA для удаления скрытых строк из определенного диапазона. Мы продолжим работу с нашим предыдущим набором данных и для этого примера.

Предположим, наш диапазон равен (B4:G9). Таким образом, мы не будем удалять скрытые строки после строки номер 10. Вот почему мы используем два цвета для выделения скрытых строк.

Наконец, мы видим, что скрытые строки в диапазоне (B4:G9) удалены, тогда как скрытые строки после строки номер 10 все еще присутствуют.

Заключение

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

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

Отличный пример — удаление скрытых строк. В старых версиях Excel это было даже невозможно. К счастью, Microsoft решила добавить его в версии 2007 и новее. Тем не менее, как это работает, для многих остается загадкой.

Существует два способа удаления скрытых строк в Excel. Давайте рассмотрим их.

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

Функция инспектора документов доступна в Excel, Word, PowerPoint и Visio. Это лучший способ обнаружить и удалить любые скрытые данные, которые могут присутствовать в документе. Это полезно, когда вам нужно поделиться документами с другими людьми и вы хотите убедиться, что в документе нет сюрпризов.

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

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

Эту функцию можно найти в одном и том же месте в Excel 2013 и 2016. Интерфейс версии 2010 выглядит немного иначе, но путь к Инспектору документов тот же. Если вы используете Excel 2007, вы можете найти этот параметр, нажав кнопку Office и выбрав «Подготовить» > «Проверить документ».

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

Использование кода VBA для удаления скрытых строк

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

Sub deletehidden()
Для lp = 256 To 1 Шаг -1
Если Columns(lp).EntireColumn.Hidden = True Then Columns(lp).EntireColumn.Delete Else
Далее
For lp = 65536 To 1 Step -1
If Rows(lp).EntireRow.Hidden = True Then Rows(lp).EntireRow.Delete Else
Следующая
EntireRow. /p>

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

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

Использование стороннего программного обеспечения

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

Пакетное удаление флажков
Пакетное удаление кнопок выбора
Удалить пустые строки,
Пакетное удаление всех макросов

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

Последнее слово

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

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

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

Если первая строка (строка 1) или столбец (столбец A) не отображается на листе, отобразить ее будет немного сложно, поскольку нет простого способа выбрать эту строку или столбец. Вы можете выбрать весь рабочий лист, а затем отобразить строки или столбцы (вкладка «Главная», группа «Ячейки», кнопка «Формат», команда «Скрыть и показать»), но при этом отобразятся все скрытые строки и столбцы на вашем листе, чего вы, возможно, не хотите делать. Вместо этого вы можете использовать поле «Имя» или команду «Перейти», чтобы выбрать первую строку и столбец.

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

В поле имени рядом с строкой формул введите A1 и нажмите клавишу ВВОД.

На вкладке "Главная" в группе "Редактирование" нажмите "Найти и выбрать", а затем нажмите "Перейти". В поле Ссылка введите A1 и нажмите кнопку ОК.

На вкладке "Главная" в группе "Ячейки" нажмите "Формат".

Выполните одно из следующих действий:

В разделе "Видимость" нажмите "Скрыть и показать", а затем нажмите "Показать строки" или "Показать столбцы".

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

Совет. Высота строк по умолчанию – 15, а ширина столбцов – 8,43.

Если вы не видите первый столбец (столбец A) или строку (строка 1) на листе, возможно, они скрыты. Вот как это сделать. На этом рисунке столбец A и строка 1 скрыты.

Чтобы отобразить столбец A, щелкните правой кнопкой мыши заголовок или ярлык столбца B и выберите "Показать столбцы".

Чтобы отобразить строку 1, щелкните правой кнопкой мыши заголовок или метку строки 2 и выберите "Показать строки".

Совет. Если вы не видите кнопки "Показать столбцы" или "Показать строки", убедитесь, что вы щелкаете правой кнопкой мыши внутри метки столбца или строки.

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