Progress-servis55.ru

Новости из мира ПК
7 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Vba excel скрыть строки

Как скрыть пустые строки? Программное скрытие пустых строк макросом VBA

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

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

Скрыть строки вручную

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

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

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

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

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

Теперь речь пойдет о том как программно скрыть пустые строки макросом. Возьмем процедуру, при помощи которой мы уже удаляли пустые строки и внесем в её программный код небольшие изменения. Заменим команду Rows(r).Delete на Rows(r).H >

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

Напомню, что область действия макроса — используемый диапазон, то есть все строки, находящиеся между первой и последней заполненными ячейками.

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

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

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

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

Как скрыть строку в Excel

Здравствуйте! Подскажите, как правильно скрыть в Excel строку по условию. Если значение ячейки 0, то скрываем, в противном случае, берем следующую и проверяем. Что-то сделал, но оно не работает что-то. Дата — это именованный диапазон. На всякий случай прикладываю сам файл.

Вложения

Visual Basic
ttt.xls (36.5 Кб, 89 просмотров)
19.07.2013, 14:45

Скрыть строку в таблице Excel
Подскажите, как можно скрыть строку в таблице Excel с помощью свойства Range Hidden или каким-либо.

Как скрыть запущенный Excel от проводника?
Есть запущенный экземпляр application экселя, в нем работает «нечто» . Вопрос: что должно.

Как скрыть строку в таблице
Подскажите пожалуйста как скрыть строку в таблице. Мне нужно если Field=”” , то скрывать всю.

Как скрыть строку в DataGridView1 ?
Как скрыть строку в DataGridView1? vb.net У меня есть три вида строк. Данные о деревьях, о кустах.

19.07.2013, 15:42 [ТС]2

Вот так перебирает, но скрывает только первую строку:

Visual Basic
19.07.2013, 16:033

Ответить

Visual Basic
19.07.2013, 17:41 [ТС]4

Igor_Tr, все заработало вот так:

но дальше возникает вопрос, у меня в Лист3 есть ячейки:

=СЦЕПИТЬ(«Количество актов за месяц: «;A19)
=СЦЕПИТЬ(«Среднее количество актов в день (22 рабочих дня): «;ОКРУГЛ(A19/22;3))

Как узнать количество оставшихся нескрытых строк? Пробовал Offset, но там он берет предпоследнию из скрытых ячеек, а мне это не нужно.

Вложения

Visual Basic
name.xls (52.0 Кб, 76 просмотров)
19.07.2013, 19:375

Не обижайтесь, но я как-то не могу прочитать что Вы написали. Я, конечно, не академик, могу ошибаться.
Вот это лишнее, уберите cls = 0 And а что ниже — Вы молодец. Это я пропустил (почти всегда так, когда пишу в окне)
Я не знаю формулы русским. Надо лезть в Google. А по поводу не скрытых — можна через SpecialCells(xlCellTypeVisible), но перед эти определить диапазон, в котором работаете (UsedRange).

Добавлено через 1 час 14 минут
А зачем Вам эти игры с Visible и Hidden? Но если нужно, я бы так искал скрытые и видимые в диапазоне:

Vba excel скрыть строки

Вопросов: 33
Ответов: 245

Добавлено: 10.02.09 14:32
Номер ответа: 2
Автор ответа:
mc-black

Вопросов: 20
Ответов: 1860

Добавлено: 10.02.09 15:24
Больше всего подходит Автофильтр. Он для того только и служит, чтоб отображать строки по условию.

Михаил, идея верная. Только есть к чему придраться:
1. Почему мы это делаем в событии Worksheet_SelectionChange?
2. У тебя Target никак не используется и почему-то проверяется исключительно A1 и при этом скрывается другая — третья стрка.

Если макросом, то я бы сделал примерно так:

  1. Sub H >As Long ) ‘j — номер столбца для проверки
  2. Dim i As Long
  3. For i = 1 to 65536
  4. If Cells(i, j).Value = 0
  5. Rows(i).H >True
  6. Else
  7. Rows(i).H >False
  8. End If
  9. Next i
  10. End Sub

Ответить

Номер ответа: 3
Автор ответа:
mc-black

Вопросов: 20
Ответов: 1860

Добавлено: 10.02.09 15:28
А если надо скрывать также пустые строки, то можно еще так:

  1. Sub H >As Long ) ‘j — номер столбца для проверки
  2. Dim i As Long
  3. For i = 1 to 65536
  4. If Cells(i, j).Value = 0 Or Cells(i, j).Value = Empty
  5. Rows(i).H >True
  6. Else
  7. Rows(i).H >False
  8. End If
  9. Next i
  10. End Sub

Вызывать из другой подпрограммы или обработчика (пример для 1-го столбца):

VBA макрос для поиска скрытых строк и столбцов на листе Excel

Данные исходные коды 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»-«Выполнить».

Читать еще:  Excel количество строк по условию
Ссылка на основную публикацию
Adblock
detector