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

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

× 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

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

—
‘ Удаление скрытых строк во всем листе
Sub KillHiddenRows()
For Each x In ActiveSheet.Rows
If x.Hidden Then x.Delete
Next
End Sub

—
‘ Удаление используемых скрытых строк или строк с нулевой высотой
Sub KillUsedHiddenThinRows()
Dim x
For Each x In ActiveSheet.UsedRange.Rows
If x.Hidden Or x.Height = 0 Then x.EntireRow.Delete
Next
End Sub
—
ZVI

01.04.2008 09:10:59

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

Пользователь
Сообщений: 11312 Регистрация: 01.01.1970
01.04.2008 09:59:00

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

Совет: тут на форуме к ответу можно прикрепить файл.

01.04.2008 11:01:27

Спасибо за ответ, то проблема в том, что код работает, но пропускает строки, т.е. не все спрятанные строки удаляются.
попробуйте заменить ActiveSheet.UsedRange.Rows

на range(cells(1,1),Cells.SpecialCells (xlLastCell)).rows

01.04.2008 13:27:07

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

Пользователь
Сообщений: 54 Регистрация: 01.01.1970
29.10.2010 11:40:02

У меня часто появляется необходимость удалить в документе скрытые строки. В принципе макрос работает, но мне пришлось его запускать 3 раза подряд, т.к. после первых двух запусков в итоге были удалены не все скрытые строки. После третьего запуска все стало ОК. Все же почему не срабатывает с первого раза? Вариант с измененной строкой от ck’y так же не срабатывает с первого раза.

покупка

Как удалить все скрытые строки или столбцы в 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 дней. Загрузите и бесплатную пробную версию прямо сейчас!

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