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

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

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

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

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

Итак, начнём!

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

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

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

Пошаговая инструкция:

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

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

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

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

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

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

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

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

Я думаю вы уже столкнулись с этим, но все равно скажу: перед проверкой документа таким образом — сохраните его. Даже если вы этого не сделаете, 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 — этой переменной присваивается номер последней строки в выделенном диапазоне.

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

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

Код для удаления скрытых столбиков 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.

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