Vba excel скрыть столбец

Пустые столбцы в таблицах Excel, также как и пустые строки, занимают полезное место как на экране монитора, так и на листе бумаги, при этом не несут смысловой нагрузки и мешают восприятию информации. Удалить такие столбцы не всегда представляется возможным, так как ячейки, находящиеся в столбцах могут участвовать в расчетах. При удалении на результат расчетов они конечно же не повлияют, но к появлению неправильных ссылок на ячейки привести очень даже могут. В таких случаях пустые столбцы можно просто скрыть и сделать это можно очень быстро.

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

Скрыть столбцы вручную

Простейший способ заключается в том, чтобы зрительно отыскать пустые столбцы и выделить их. При этом можно выделять несколько не смежных столбцов, удерживая клавишу Ctrl на клавиатуре. После того как столбцы выделены, необходимо навести курсор на выделенное поле, кликнуть по нему правой кнопкой мыши и выбрать пункт "Скрыть" из контекстного меню. Выделенные столбцы будут скрыты. Способ простой, но можно пропустить какой-нибудь столбец или наоборот можно ошибочно выделить и скрыть столбец со значениями.

Скрыть столбцы используя сортировку

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

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

Программное скрытие макросом VBA

Пустые столбцы в Excel можно скрыть и средствами VBA. При этом не нужно тратить время и усилия на поиски пустых столбцов, процедура, написанная на Visual Basic for Applications все это сделает за Вас. Ниже приведен листинг процедуры, скрывающей пустые столбцы в используемом диапазоне активного рабочего листа. Напомню, что используемым называется диапазон, начинающийся первой заполненной и заканчивающийся последней заполненной ячейкой.

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

Автоматическое скрытие с использованием надстройки

Более функциональным средством VBA является надстройка, позволяющая удалять и скрывать не только пустые столбцы, но и столбцы, отвечающие условиям пользователя, которые можно задавать в диалоговом окне.

Пользователь на свой выбор может удалять либо скрывать пустые столбцы. Может удалять либо скрывать столбцы, содержащие либо не содержащие заданный текст, при этом на свой выбор учитывать либо не учитывать регистр. Возможен одновременный поиск сразу нескольких текстовых выражений, разделенных знаком ; (точка с запятой). Пользователь также может на свое усмотрение расширять область действия макроса и ограничивать его, задавая номера первого и последнего столбца. Кроме того, надстройка освобождает пользователя от поиска нужного макроса, так как позволяет вызывать диалоговое окно прямо из панели инструментов Excel.

И еще одна надстройка, для скрытия и удаления строк и столбцов, в зависимости от значений ячеек и заданных условий. Работает более гибко, но несколько медленнее, чем первая, так как проверяет не столбцы, а ячейки. Позволяет удалять не только строки и столбцы, но и ячейки с заданными значениями и с заданным сдвигом.

Довольно часто появляется необходимость в Excel скрывать или отображать строки или столбцы. Особенно это актуально, когда на листе размещается очень много информации и часть из них является вспомогательной и требуется не всегда и тем самым загромождает пространство, ухудшая восприятие. Если это приходится делать часто, то делать это с помощью меню неудобно, особенно если приходится скрывать и отображать разные столбцы и строки.

Для существенного удобства можно написать простенький макрос, привязав его к кнопке и делать это одним щелком мыши.

Вот так выглядят простые примеры, с помощью которых Вы без труда сможете скрывать или отображать строки и столбцы с помощью VBA

Пример 1: Скрыть строку 2 в Excel

Sub HideString () ‘Это название макроса

End Sub

Пример 2: Скрыть несколько строк в Excel (строку 3-5)

Sub HideStrings ()

End Sub

Пример 3: Скрыть столбец 2 в Excel

Sub HideCollumn ()

Columns (2).H >

End Sub

Пример 4: Скрытие нескольких столбцов в Excel

Sub HideCollumns ()

Columns («E:F»).H >

End Sub

Пример 5: Скрытие строки по имени ячейки в Excel

Sub HideCell ()

Range («Возможности Excel»).EntireRow.H >

End Sub

Пример 6: Скрытие нескольких строк по адресам ячеек

Sub HideCell ()

Range («B3:D4»).EntireRow.H >

End Sub

Пример 7: Скрытие столбца по имени ячейки

Sub HideCell ()

Range («Возможности Excel»).EntireColumn.H >

End Sub

Пример 8: Скрытие нескольких столбцов по адресам ячеек

Sub HideCell ()

Range («C2:D5»).EntireColumn.H >

End Sub

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

Для того, чтобы отобразить строки и столбцы в Excel вы можете воспользоваться этими же макросами, но вместе True необходимо указать False

Например, макрос для того, чтобы отобразить строку 2 будет выглядеть следующим образом:

Sub ViewString ()

End Sub

Надеемся, что данная статья была полезна вам и ответила на вопрос: как скрыть или отобразить строки и столбцы в Excel с помощью VBA

Спасибо за внимание.

No related posts.

Похожие статьи

Что делать если условием таблицы является скрытие столбцов по условию: содержание в ячейках определенного значения? Нужен макрос? Можете помочь?

Например: Есть таблица со строками содержащими в заголовке название рыб (200 строк) и столбцами содержащими в заголовке название наживки для рыб(50 столбцов). В таблице есть диапазон содержащий в ячейках символ Х и символ Y. Используя стандартный фильтр выбираю в одном из столбцов «фильтровать по значению Х» Требуется: Выделив все ячейки оставшегося после фильтрации диапазона(например осталось только 20 строк названий рыб и все 50 столбцов названий наживки) скрыть СТОЛБЦЫ в которых ячейки диапазона не содержат хотя бы 1 символ Х (например в результате получиться 20 строк названий рыб и всего 5 СТОЛБЦОВ названий наживки)

Выдает ошибку на многоточие и макрос не срабатывает 🙁 как быть в этой ситуации.

Пример 4: Скрытие нескольких столбцов в Excel

Подскажите, пжл, что делаю не так .

В примере №7, выдаёт ошибку 400, пишет:

«Method ‘Range’of object’_Worksheet’failed»

Подскажите, пожалуйста, как сделать, чтобы в примере №7, макрос ссылался бы НЕ на ИМЯ ячейки, а на ЗНАЧЕНИЕ, которое есть в ячейке ?

Иными словами, если ячейки А1, А2, А3 — содержать значение «хотим скрыть эти строки», то макрос скрывает эти строки (т.е. — строки 1, 2 и 3).

Если в какой-либо ячейке — иное значение, то, соответственно, эта строка НЕ скрывается.

Зараенее спасибо за ответ.

Попробуйте вот этот готовый макрос для скрытия и отражения строк по условию

Спасибо за макрос!

Но адаптировать под себя — оказалось для меня слишком сложно ((.

Буду благодарен, если подскажите, как реализовать такую процедуру:

На ЗАЩИЩЁННОМ листе — две кнопки «Скрыть» и «Отобразить»

При нажатии кнопки «Скрыть» — происходит скрытие всех строк, одна из ячеек которых (скажем, все такие ячейки расположены в одном столбце «D») содержит значение «Счёт закрыт».

Лист при этом остаётся ЗАЩИЩЁННЫМ.

И, соответственно, при нажатии кнопки «Отобразить» — все скрытые строки — отображаются. Лист также остаётся защищённым.

А вот такой вопрос. Есть диапазон в 31 столбец, как скрыть столбцы выходящие за предел диапазона в зависимости от продолжительности месяца. то есть, если, к примеру февраль, то скрыть 3 или 2 последних столбца в диапазоне. Заранее спасибо за ответ !

Можно воспользоваться простеньким макрос скрытия столбцов по определенному условию (в данном случае, если в перовой строке есть цифра 1).

Данные исходные коды VBA-макросов умеют быстро находить все скрытые строки и столбцы на листе Excel. Также они выводят всю информацию о скрытых строках и столбцах. Номера строк отображаются числами, а номера столбцов преобразуются в буквы заголовков, что очень удобно.

Как найти все скрытые строки на листе Excel с помощью макроса

У нас иметься таблица с данными по заказам, но некоторые строки листа скрыты:

Необходимо узнать сколько и какие строки скрытии от визуального анализа таблицы заказов. По умолчанию Excel не располагает таким инструментом, который смог бы решить данную задачу в пару кликов мышкой. Вручную выполнить задачу можно, но сложно. Особенно если после отображения скрытых строк нам нужно снова их скрывать. Кроме того, лист может быть защищен от изменений паролем. Оптимальным решением данной задачи будет написание собственного макроса для отображения информации о скрытых строках.

Чтобы написать свой макрос откройте редактор кода макроса: «РАЗРАБОТЧИК»-«Код»-«Visual Basic» или нажмите комбинацию клавиш ALT+F11:

В редакторе вставьте новый модуль выбрав инструмент «Insert»-«Module» и введите в него этот код:

Sub HiddenLinesInfo()
Dim i As Long
Dim text As String
Dim pervoj As String
text = "В данном листе скрыты следующие строки: "
For i = 1 To ActiveSheet.Rows.Count
If ActiveSheet.Rows(i).H >True Then
If pervoj = "" Then
pervoj = i
End If
Else
If pervoj <> "" Then
text = text & vbNewLine & pervoj & ":" & i – 1
pervoj = ""
Else
If i > ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row Then
Exit For
End If
End If
End If
Next
MsgBox text
End Sub

Теперь если в таблице заказов необходимо получить информацию о скрытых строках, тогда выберите инструмент для запуска макроса: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«HiddenLinesInfo»-«Выполнить».

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

Описание исходного VBA-кода для поиска скрытых строк

В начале данного кода объявляются сразу 3 переменные:

1. i – счетчик циклов.
2. text – содержит текст для текущего сообщения информации о скрытых строках.
3. pervoj – номер первой скрытой строки в группе скрытых строк.

В начале тела кода макроса для переменной text присваиваем начало текста сообщения. После выполняется цикл, в котором проверяться по очереди все строки в пределах используемого диапазона листа. И определяется какие из них скрытые. Если текущая строка является скрытой, значит запускается проверка значения переменной pervoj. Если в переменную еще не было присвоено ни одно значение, тогда для нее присваивается номер первой скрытой строки в отдельной группе скрытых строк. Дальше проверяется и выполняется следующее условие. Если проверяемая строка не является скрытой и переменная pervoj уже содержит в своем значении номер первой скрытой сроки, тогда выполняется целый ряд следующих операций:

1. К тексту в переменной text дописывается (с новой строки) номер первой скрытой строки из текущей группы скрытых строк.
2. К тексту в переменной text дописывается двоеточие и номер последней строки из текущей группы строк. Данное значение взято из счетчика цикла текущее числовое значение в переменной i-1.
3. Удаляется значение из переменной pervoj. Таким образом создается возможность для запуска этих же операций для следующей группы скрытых строк.

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

Примечание. Последняя ячейка используемого диапазона листа – это последняя ячейка, которая имеет любое изменение: новое значение, новый формат отображения значений, измененный цвет заливки тип границ. И максимально отдаленная от первой ячейки листа A1.

В самом конце макроса вызывается сообщение в теле которого должно содержаться текстовое значение из переменной text.

Если исходная таблица не содержит ни одной скрытой строки, тогда после запуска макроса будет отображаться только первая строка текста из переменной text: «В данном листе скрыты следующие строки:»

Такое сообщение для данной ситуации является немного ошибочным. Если нужно сделать так, чтобы при отсутствии скрытых строк в таблице макрос отобразил другой текст сообщения, нужно задекларировать еще одну булевою переменную HidViz логического типа Boolean:

Dim HidViz As Boolean

В ней будет содержаться одно из двух традиционных логических значений True or Fasle (истина или ложь), в зависимости от того будет ли таблица содержать хотя-бы одну скрытую строку или нет. Далее после строки кода макроса:

дописываем инструкцию изменяющую логическое значение для переменной HidViz:

Дополнительно в конце кода перед строкой:

добавьте инструкции для изменения текста сообщения на альтернативный, в тому случаи если не найдено ни одной скрытой строки в исходной таблице:

If H > "На текущем листе нет ни одной скрытой строки!"

Полная версия измененного кода макроса выглядит так:

Sub HiddenLinesInfo()
Dim i As Long
Dim text As String
Dim pervoj As String
Dim H >As Boolean
text = "В данном листе скрыты следующие строки: "
For i = 1 To ActiveSheet.Rows.Count
If ActiveSheet.Rows(i).H >True Then
H >True
If pervoj = "" Then
pervoj = i
End If
Else
If pervoj <> "" Then
text = text & vbNewLine & pervoj & ":" & i – 1
pervoj = ""
Else
If i > ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row Then
Exit For
End If
End If
End If
Next
If H >False Then text = "На текущем листе нет ни одной скрытой строки!"
MsgBox text
End Sub

Результат действия измененного макроса в тому случаи если скрытые строки отсутствуют:

Поиск скрытых столбцов на листе Excel используя VBA-макрос

Но что если необходимо сделать так чтобы макрос показывал информацию не о скрытых строках, а и о скрытых столбцах таблицы:

В таком случае сделаем так:

1. Скопируйте выше описанный код макроса для отображения информации о скрытых строках в этот же модуль и переименуйте его на «HiddenColumnInfo».
2. Измените тексты сообщений, а именно измените по смыслу слова «строки» на «столбцы».
3. В коде измените все свойства где встречается Rows на Columns.
4. Измените строку для отображения текста сообщения с информацией о скрытых столбцах. Ведь заголовки столбцов у нас отображаются буквами, а не числами. Для этого воспользуемся строкой кода, которая умеет возвращать букву столбца VBA. Поэтому вместо строки:

pervoj = Split(Cells(1, i).Address, "$")(1)

a вместо строки для вывода сообщения:

text = text & vbNewLine & pervoj & ":" & i – 1

пишем модифицированный код генерации сообщений с буквами для заголовков столбцов:

text = text & vbNewLine & pervoj & ":" & Split(Cells(1, i – 1).Address, "$")(1)

Код макроса для получения информации о скрытых столбцах в таблице выглядит следующим образом:

Sub HiddenColumnInfo()
Dim i As Long
Dim text As String
Dim pervoj As String
Dim H >As Boolean
text = "В данном листе скрыты следующие столбцы: "
For i = 1 To ActiveSheet.Columns.Count
If ActiveSheet.Columns(i).H >True Then
H >True
If pervoj = "" Then
pervoj = Split(Cells(1, i).Address, "$" )(1)
End If
Else
If pervoj <> "" Then
text = text & vbNewLine & pervoj & ":" & Split(Cells(1, i – 1).Address, "$" )(1)
pervoj = ""
Else
If i > ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column Then
Exit For
End If
End If
End If
Next
If H >False Then text = "На текущем листе нет ни одного скрытого столбца!"
MsgBox text
End Sub

Пример работы макроса HiddenColumnInfo в действии:

Обратите внимание что теперь вместо числовых номеров, в сообщении отображаются буквы заголовков столбцов. Это очень удобно и правильно!

Таким образом мы создали новый макрос, который предназначен для работы со скрытыми столбцами. За основу использовали предыдущий код макроса.

В этот же модуль можно написать еще один 3-тий макрос, который будет вызывать поочередно эти два макроса описаны в данном примере. Одним словом, с помощью простейшего кода VBA выполним запуск двух макросов из другого макроса Excel:

Sub HiddenLineColumnInfo()
HiddenLinesInfo
HiddenColumnInfo
End Sub

Этот макрос сначала позволит нам получить всю информацию о скрытых строках, а потом и о скрытых столбцах таблицы в один клик мышкой. Теперь нам не нужно по отдельности запускать оба макроса, достаточно лишь запустить их из «РАЗРАБОТЧИК»-«Код»-«Макросы»-«HiddenLineColumnInfo»-«Выполнить».