Довольно часто люди скрывают какие-либо данные в табличках.
Это хороший вариант оставить видимыми только те данные, которые важны (например, в отчете), а остальные (например, вспомогательный столбик для расчетов) просто скрыть.
Но если вам эти данные больше не нужны — можно их удалить. В случае, если их очень много, это займет довольно много времени вручную.
В данной статье мы рассмотрим быстрые способы удаления скрытых столбиков и строк.
Итак, начнём!
Удаляем скрытые столбики и строки с помощью встроенных функций 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