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

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

× The language used in menus and dialogs (Язык, используемый в меню и диалоговых окнах) in ASAP Utilities can be set to «Русский (Russian)».

Столбцы и строки › 11. Удалить все скрытые строки и/или столбцы.

С помощью этой утилиты можно удалить все скрытые строки и/или столбцы на текущем листе.

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

Примечание.
Если в скрытых строках или столбцах имеются формулы со ссылками на ячейки, необходимо сначала обновить формулы или заменить их с результатами вычислений. В противном случае при удалении строк и столбцов формулы дадут ошибки (#ССЫЛКА!).

Снимки экрана

Practical tricks on how this can help you

Запуск этой утилиты

• Нажмите кнопку ASAP Utilities › Столбцы и строки › 11. Удалить все скрытые строки и/или столбцы.
• Укажите сочетание клавиш: ASAP Utilities › Избранное и сочетания клавиш › Редактировать избранное и сочетания клавиш.

Additional keywords for this tool:
удалить, фильтр, отфильтрованный, быстро сохранить только отфильтрованные результаты, автофильтр, сохранить только отфильтрованные результаты, удалить неотображаемые строки

Как удалить все скрытые элементы в Excel

Довольно часто люди скрывают какие-либо данные в табличках.

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

Но если вам эти данные больше не нужны — можно их удалить. В случае, если их очень много, это займет довольно много времени вручную.

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

Удаляем скрытые столбики и строки с помощью встроенных функций Excel

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

Узнайте Excel как свои пять пальцев на курсе по таблицам от Skillbox

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

• Щелкните «Главная»;

• Далее — «Сведения»;

• Далее — «Проверка книги»;

• Жмем «Проверить документ»;

• И просто нажимаем «Проверить»;

• Далее найдите Далее найдите «Скрытые строки и столбцы»;

• И просто удалите их.

Готово! Таким образом, все скрытые элементы будут удалены. Вот результат проверки после удаления:

Я думаю вы уже столкнулись с этим, но все равно скажу: перед проверкой документа таким образом — сохраните его. Даже если вы этого не сделаете, Excel автоматически предложит сделать это:

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

Обязательно сделайте резервную копию файла перед тем, как будете использовать этот способ. Так как изменения, которые вы сделаете — невозможно откатить.

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

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

С помощью Visual Basic

В этом разделе статьи мы создадим несколько функций (для разных целей) с помощью Visual Basic.

Удаляем скрытые элементы из выделенного диапазона ячеек

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

Самый приемлемый вариант — сделать так, чтобы функция удаляла скрытые элементы из выделенного диапазона. А там уже можно выделить и весь лист (CTRL + A), если это необходимо.

Код для удаления скрытых строк Visual Basic:

Sub DeleteHiddenRows() Dim sht As Worksheet Dim LastRow Set sht = ActiveSheet LastRow = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row For i = LastRow To 1 Step -1 If Rows(i).Hidden = True Then Rows(i).EntireRow.Delete Next End Sub

Lastrow — этой переменной присваивается номер последней строки в выделенном диапазоне.

Далее он проверяет скрыта или нет эта строка и так далее.

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

Если скрыта — удаляется.

Код для удаления скрытых столбиков Visual Basic:

Sub DeleteHiddenColumns() Dim sht As Worksheet Dim LastCol as Integer Set sht = ActiveSheet LastCol = sht.UsedRange.Columns(sht.UsedRange.Columns.Count).Column For i = LastCol To 1 Step -1 If Columns(i).Hidden = True Then Columns(i).EntireColumn.Delete Next End Sub

Работает точно так же, только для столбиков.

А этот код работает сразу для столбиков и строк:

Sub DeleteHiddenRowsColumns() Dim sht As Worksheet Dim LastRow as Integer Dim LastCol as Integer Set sht = ActiveSheet LastRow = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row LastCol = sht.UsedRange.Columns(sht.UsedRange.Columns.Count).Column For i = LastRow To 1 Step -1 If Rows(i).Hidden = True Then Rows(i).EntireRow.Delete Next For i = LastCol To 1 Step -1 If Columns(i).Hidden = True Then Columns(i).EntireColumn.Delete Next End Sub

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

Тут работает 2 цикла — сначала проверяются и удаляются скрытые строки, а потом столбики.

Код нужно вставить в Visual Basic -> правой кнопкой на любой лист -> «Insert» -> «Module».

Удаляем скрытые элементы из определенного диапазона ячеек

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

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

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

Sub DeleteHiddenRowsColumns() Dim sht As Worksheet Dim Rng As Range Dim LastRow As Integer Dim RowCount As Integer Set sht = ActiveSheet Set Rng = Range("A2:B300") RowCount = Rng.Rows.Count LastRow = Rng.Rows(Rng.Rows.Count).Row ColCount = Rng.Columns.Count LastCol = Rng.Columns(Rng.Columns.Count).Column For i = LastRow To LastRow - RowCount Step -1 If Rows(i).Hidden = True Then Rows(i).EntireRow.Delete Next For j = LastCol To LastCol - ColCount Step -1 If Columns(j).Hidden = True Then Columns(j).EntireColumn.Delete Next End Sub

Эта функция удалит все скрытые элементы из A2:B300.

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

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

Надеюсь, эта статья оказалась полезной для вас!

Узнайте Excel как свои пять пальцев на курсе по таблицам от Skillbox

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

Всем доброго времени.

Имеются большие xls файлы по 10Мб.
После сортировки и фильтра остается несколько необходимых ячеек, все остальное скрывается.
Естественно при сохранении размер файла не уменьшается. А надо бы.

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

Вариант «скопировать все в другой лист» не подходит.

Пользователь
Сообщений: 14868 Регистрация: 21.12.2012
E-mail и реквизиты в профиле.
13.08.2010 22:37:25

1)Возможно, Вы имеете ввиду удалить скрытые строки?
2)Чем не устраивает «скопировать все в другой лист» и удалить исходный?

Я сам — дурнее всякого примера! .
Пользователь
Сообщений: 14868 Регистрация: 21.12.2012
E-mail и реквизиты в профиле.
13.08.2010 23:24:49

Public Sub DelHiddenRows()
Dim lR As Long, i As Long
lR = ActiveSheet.UsedRange.Rows.Count
For i = lR To 1 Step -1
If Rows(i).Hidden Then Rows(i).Delete
Next
End Sub

Я сам — дурнее всякого примера! .
Пользователь
Сообщений: 66 Регистрация: 01.01.1970
13.08.2010 23:37:21

1)Возможно, Вы имеете ввиду удалить скрытые строки?
2)Чем не устраивает «скопировать все в другой лист» и удалить исходный?

1. Кстати, а в excel есть разница между отфильтроваными и скрытыми?
Например у меня в файле первоначально зажаты несколько столбцов.
Затем я ставлю автофильтр и зажимаю еще несколько строк.
И то и ругое попадает под понятие «скрытые»?

2. Слишком много потом нужно ровнять : )

Пользователь
Сообщений: 66 Регистрация: 01.01.1970
13.08.2010 23:38:12

Public Sub DelHiddenRows()
Dim lR As Long, i As Long
lR = ActiveSheet.UsedRange.Rows.Count
For i = lR To 1 Step -1
If Rows(i).Hidden Then Rows(i).Delete
Next
End Sub

Я не «программист», поэтому не знаю, что делать с этим кодом : (

Пользователь
Сообщений: 8839 Регистрация: 11.01.2013
14.08.2010 00:19:41

> 1. Кстати, а в excel есть разница между отфильтроваными и скрытыми?
Нет. Хотя «отфильтрованными» обычно называют видимые данные.

Сергей,
небольшое уточнение к твоему коду. Если Usedrange начинается не с первой, а с n-ной строки, то последние (n-1) строк не будут просмотрены. См. ниже.

Если надо также удалять скрытые столбцы, код такой:

Public Sub DelHiddenRowsColumns()
Dim lR As Long, i As Long
lR = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row — 1
For i = lR To 1 Step -1
If Rows(i).Hidden Then Rows(i).Delete
Next

ActiveSheet.ShowAllData ‘в режиме фильтра нельзя удалять столбцы!

lR = ActiveSheet.UsedRange.Columns.Count + ActiveSheet.UsedRange.Column — 1
For i = lR To 1 Step -1
If Columns(i).Hidden Then Columns(i).Delete
Next
End Sub

Пользователь
Сообщений: 14868 Регистрация: 21.12.2012
E-mail и реквизиты в профиле.
14.08.2010 07:06:05

>
Сергей,
небольшое уточнение к твоему коду. Если Usedrange начинается не с первой, а с n-ной строки, то последние (n-1) строк не будут просмотрены.
Согласен:-)

Я сам — дурнее всякого примера! .
14.08.2010 21:50:15

> 1. Кстати, а в excel есть разница между отфильтроваными и скрытыми?
Нет. Хотя «отфильтрованными» обычно называют видимые данные.

Сергей,
небольшое уточнение к твоему коду. Если Usedrange начинается не с первой, а с n-ной строки, то последние (n-1) строк не будут просмотрены. См. ниже.

Если надо также удалять скрытые столбцы, код такой:

Public Sub DelHiddenRowsColumns()
Dim lR As Long, i As Long
lR = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row — 1
For i = lR To 1 Step -1
If Rows(i).Hidden Then Rows(i).Delete
Next

ActiveSheet.ShowAllData ‘в режиме фильтра нельзя удалять столбцы!

lR = ActiveSheet.UsedRange.Columns.Count + ActiveSheet.UsedRange.Column — 1
For i = lR To 1 Step -1
If Columns(i).Hidden Then Columns(i).Delete
Next
End Sub

А что в итоге делать мне?
Стандартными инструментами данную операцию проделать не получится?

Пользователь
Сообщений: 14868 Регистрация: 21.12.2012
E-mail и реквизиты в профиле.
14.08.2010 22:41:17

Грузите свой файл. Жмете alt-F11. Меню-Insert-Module. Туда копируете макрос Казанского. Далее сохраняем книгу. Переходим на нужный лист. Жмем alt-F8. Выбираем макрос DelHiddenRowsColumns — Выполнить. Смотрим что получилось.
Макросы должны быть включены.

Я сам — дурнее всякого примера! .
Пользователь
Сообщений: 4365 Регистрация: 23.12.2012
14.08.2010 23:42:55

> 1. Кстати, а в excel есть разница между отфильтроваными и скрытыми?
Нет. Хотя «отфильтрованными» обычно называют видимые данные.
На всякий случай уточню, что скрытые вручную и автофильтором строки или столбцы — не одно и то же с точки зрения Excel и VBA. Например, метод Find не видит того, что скрыто автофильтром, но видит скрытое вручную.

14.08.2010 23:45:10

Грузите свой файл. Жмете alt-F11. Меню-Insert-Module. Туда копируете макрос Казанского. Далее сохраняем книгу. Переходим на нужный лист. Жмем alt-F8. Выбираем макрос DelHiddenRowsColumns — Выполнить. Смотрим что получилось.
Макросы должны быть включены.

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

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

Еще раз спасибо.

Пользователь
Сообщений: 4365 Регистрация: 23.12.2012
15.08.2010 00:03:00

Вопрос: можно ли как-то удалить все скрытые ячейки, чтобы потом сохранить оставшиеся ячейки и тем самым добиться существенного уменьшения размера файла?Без VBA — напишите противоположное условие, чтобы отобразилось то, что нужно удалить. Затем удалите видимые, и отобразите все — в фильтре есть такая строчка.

Пользователь
Сообщений: 14868 Регистрация: 21.12.2012
E-mail и реквизиты в профиле.
15.08.2010 06:08:18

А с ВБА, чтоб быстрее:
Public Sub DelHiddenRows()
Dim lR As Long, i As Long
lR = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row — 1
Application.ScreenUpdating = False
For i = lR To 1 Step -1
If Rows(i).Hidden Then Rows(i).Delete
Next
Application.ScreenUpdating = True
End Sub
Но способ ZVI сработает просто моментально, так что подумайте.

Я сам — дурнее всякого примера! .
15.08.2010 12:40:10

А с ВБА, чтоб быстрее:
Public Sub DelHiddenRows()
Dim lR As Long, i As Long
lR = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row — 1
Application.ScreenUpdating = False
For i = lR To 1 Step -1
If Rows(i).Hidden Then Rows(i).Delete
Next
Application.ScreenUpdating = True
End Sub
Но способ ZVI сработает просто моментально, так что подумайте.

Да, с новым кодом гораздо быстрее получилось. А можете объяснить в двух словах как не для программиста: за счет чего скорость увеличились?

И возник еще один вопрос: в книге около 15 листов, надо зайти на каждый и применить обработку. А можно написать код, чтобы одним нажатием сразу всю книгу обработать?
А то приходится над каждым листом сидеть, а так можно было бы другими полезными делами заняться, пока вся книга обрабатывается.

покупка

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

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

Delete multiple hidden / blank / visible columns or rows at once:

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, нажмите Office Кнопку в верхнем левом углу, а затем щелкните Подготовить > Осмотреть документ. Смотрите скриншот:

Если вы используете Excel 2010/2013, нажмите Файл > Инфо > Проверить наличие проблем > Осмотреть документ, смотрите скриншоты:

В Excel 2013:

В Excel 2010:

2, Затем Инспектор документов появится диалоговое окно, щелкните Диагностика кнопку, и были проверены скрытые строки и столбцы во всей книге, щелкните Удалить все кнопку, см. снимок экрана:

3, Затем нажмите Закрыть кнопку, чтобы закрыть диалоговое окно. И все скрытые строки и столбцы в книге будут удалены.

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

2. С помощью этой функции вы можете удалить все скрытые строки и столбцы во всей книге.

Удалите все скрытые строки и столбцы на активном листе с кодом VBA

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

1. Удерживая нажатой ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модулии вставьте следующий код в Окно модуля.

Код VBA: удалить все скрытые строки и столбцы активного листа:

Sub deletehidden() For lp = 256 To 1 Step -1 If Columns(lp).EntireColumn.Hidden = True Then Columns(lp).EntireColumn.Delete Else Next For lp = 65536 To 1 Step -1 If Rows(lp).EntireRow.Hidden = True Then Rows(lp).EntireRow.Delete Else Next End Sub 

2, Затем нажмите F5 ключ для выполнения кода. И все скрытые строки и столбцы были удалены на активном листе.

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

Удалите все скрытые строки или столбцы в выбранном / рабочем листе / всей книге с помощью Kutools for Excel

Для Удалить скрытые (видимые) строки и столбцы полезности Kutools for Excel, вы можете быстро удалить скрытые строки или столбцы в выбранном диапазоне, на активном листе, на выбранных листах и ​​на всех листах.

Если вы установили Kutools for Excel, пожалуйста, сделайте так:

1. Нажмите Кутулс > Удалить > Удалить скрытые (видимые) строки и столбцы, см. снимок экрана:

2. В Удалить скрытые (видимые) строки и столбцы диалоговом окне укажите область, в которой вы хотите применить операцию из Заглянуть раскрывающийся список. (Если вы выберете В выбранном диапазоне, вы должны сначала выбрать диапазон, который хотите использовать.) А затем отметьте Ряды or Колонки под Удалить тип, И выберите Скрытые строки от Подробный тип. Затем нажмите Ok, скрытые строки были удалены из выбранного диапазона, см. снимок экрана:

Чтобы узнать больше об этой функции, посетите Удалить строки и столбцы функции.

Демонстрация: удалите все скрытые строки или столбцы в выделенном / рабочем листе / всей книге с помощью Kutools for Excel

Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!

Связанные статьи: