Удалить все скрытые строки и/или столбцы.
× 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 SubLastrow — этой переменной присваивается номер последней строки в выделенном диапазоне.
Далее он проверяет скрыта или нет эта строка и так далее.
Рекомендуем курс 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 дней. Загрузите и бесплатную пробную версию прямо сейчас!
Связанные статьи: