Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Пошаговое "раскрытие" скрытых строк сверху вниз при внесении данных в таблицу., "Расширение" таблицы путем отображения скрытых строк с заданным шагом
 
#1
08.04.2017 21:12:55

Здравствуйте Люди добрые! Пожалуйста помогите оптимизировать макрос. На форуме похожие темы, конечно, есть, но я ни один из примеров не смог применить. Автофильтр для моих целей я тоже не смог применить потому как он работает сразу со всей таблицей, а мне необходима раздельная обработка в четырех диапазонах.

В таблице есть 4 блока товаров. Каждый блок состоит из 30 строк. Для компактности внутри блока пустые строки скрыты, кроме первых двух и последней с итогом. Сконструировал простейший макрос. Работает так - при внесении данных в первую строку по столбцу "F" (Ячейка  Cells(5, 6)) "раскрывается" (через Hidden = True) вторая по счёту строка. И наоборот, если целевая ячейка пуста, то вторая по счёту строка скрывается. Весь макрос "сшит" из кусков по типу:

Код
If Cells(5,6).Value = 0 Then
     Rows(7).Hidden = True
   Else Rows(7).Hidden = False
End If

просто последовательно менял номера строк. "Хватило" меня только на один блок. А нужно чтобы все четыре блока так работали.  Понимаю, что конструкция примитивная и кроме того макрос привязан к модулю листа через Worksheet_SelectionChange, что серьезно "грузит" систему, стоит только на листе в любом месте ткнуть мышью как идет пересчет ячеек по всему листу.  Очень хочется получить цикл с меньшим количеством строк и по возможности привязать к другому менее требовательному к ресурсам "событию". Файлик с макросом прилагаю. Целевой столбец выделен желтым цветом.

Изменено: Excelman - 09.04.2017 10:44:37
 
 
 
#2
09.04.2017 00:38:12
Код
Private Sub Worksheet_Change(ByVal Target As Range)
    If Rows(Target.Row + 1).Hidden = True Then Rows(Target.Row + 1).Hidden = False
End Sub
 
 
 
#3
09.04.2017 06:32:00
VideoAlex, Спасибо большое, за помощь. Очень изящный кратенький макрос и по началу возникло ощущение, что Ваш вариант то, что надо. Однако при тестировании различных ситуаций редактирования таблицы выяснилось, что макрос работает как надо, только при условии построчного ввода данных и только в одну сторону (то есть только открывает, но не закрывает в обратную сторону). Например, если нужно сделать вставку данных с размером больше одной строки, т.е, диапазон строк превышающий 1 строку (например, сразу три товара с ценой характеристикой артикулами и т.д)   в ближайшую верхнюю "открытую" строку, то макрос открывает, только одну следующую по счету "скрытую" строку и часть вставленных данных оказывается в скрытых строках. В макросе, который я "настрогал" это свойство как-то само собой выполняется. Повторюсь, предложенный вариант  "не умеет" работать в обратную сторону, т.е. наоборот скрывать пустые строки, опять же, это в моём макросе выполняется  как то само собой. Соответственно просьба, VideoAlex, или другие знатоки пожалуйста помогите "допилить" макрос. Очень для меня Важный момент - соблюдение некоторого условия, при котором строка  должна скрываться. Строка может быть скрыта только, если в ней (в строке)  ячейки одновременно (то есть AND) по столбцам "C D E F G I J" будут пустые (по типу EMPTY,  =""  или как то так). При этом ячейка по столбцу "H" не должна проверяться на это условие. Заранее спасибо!
Изменено: Excelman - 09.04.2017 06:34:19
 
 
 
#4
09.04.2017 09:19:36
Excelman знает, что такое абзацы и как их применять?
 
 
 
#5
09.04.2017 09:35:42
Цитата
Excelman написал:
Для компактности внутри блока пустые строки скрыты, кроме первых двух и последней с итогом.
вот это ▲ противоречит вот этому ▼
Цитата
Excelman написал:
...если нужно сделать вставку ...например, сразу три товара...
Третий товар у Вас запишется в строку с итогом, правильно?
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
 
 
#6
09.04.2017 09:54:14
М.б. не стоит усложнять? Вот так - устроит?
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
 
 
#7
09.04.2017 10:05:27
Цитата
Михаил Лебедев написал:
Третий товар у Вас запишется в строку с итогом, правильно?
Нет, Это не так. Третий товар вписывается как следует, то есть в четвертую строку (скрытую) строку, которая после "приёма" из буфера обмена становится видимой. За предложенный вариант большое спасибо, но мне важно именно пошаговое "раскрытие" с поправкой на то что возможны ситуации при вставке из буфера обмена.
 
 
 
#8
09.04.2017 10:13:17
Цитата
vikttur написал:
Excelman знает, что такое абзацы и как их применять?
Постараюсь исправиться.
 
 
 
#9
09.04.2017 10:38:01
тогда вот так... Только не забудьте, что есть опасность вставить строк больше, чем есть в блоке, и стереть итоги
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
 
 
#10
09.04.2017 11:23:10
Михаил Лебедев, Я безмерно Вам благодарен!!! ОГРОМНЕЙШЕЕ СПАСИБО. Вы все таки использовали Автофильтр. Признаюсь даже не мог себе представить, что фильтр можно так экзотично настроить 8-0. Если не затруднит пожалуйста объясните логику формулы по столбцу K хотя бы в двух словах.

А здесь я честно что-то не продумал
Цитата
Михаил Лебедев написал:
тогда вот так... Только не забудьте, что есть опасность вставить строк больше, чем есть в блоке, и стереть итоги
Можно ли как то в Ваш код в модуле листа дополнить условием, что, если, например, на рабочем листе в ячейках H34 , H64, H94, H124 произойдет редактирование, то макрос выдаст ошибку и сообщение "Ошибка! Превышен лимит по срокам"? Если это слишком заморочено, то можно опустить мою просьбу.  
 
 
 
#11
09.04.2017 12:27:28
Цитата
Excelman написал:
если, например, на рабочем листе в ячейках H34 , H64, H94, H124 произойдет редактирование
В этих ячейках у Вас формулы - про какое редактирование разговор? )
 
 
 
#12
09.04.2017 17:42:53
Юрий М, провел эксперимент - скопировал и вставил больше строк чем лимитировано в блоках (более 30 строк от начала строки блока). Строка итогов полностью приходит в негодность (из-за замены значений ячеек) и избыточные строки переходят на следующий блок, что не допустимо в моём случае. Неприятным оказалось что "разрушение" необратимо, т.е. кнопка отмены (Ctrl+Z) не действует. Видимо "необратимость" связана с отработкой макроса, насколько я понял стандартным способом это не решить.  
Цитата
Юрий М написал:
В этих ячейках у Вас формулы - про какое редактирование разговор? )
Здесь я не уверен, что верно изложил проблему. Теперь хотел бы иначе поставить вопрос. То есть при любой попытке вставить скопированые строки в блок макрос должен выполнить алгоритм.
1. Проверить на избыточность количество вставляемых строк по условию КолСкрСтр < КолБуфСтр, где КолСкрСтр -количество доступных скрытых строк, КолБуфСтр -количество строк находящееся в буфере. а КолСкрСтр = № Строки вставки минус № последней скрытой строки в зависимости от блока (33,63,94,124). Если условие КолСкрСтр < КолБуфСтр верно, то пункт 2, 3, 4 если нет, то разрешается обычная вставка без ограничений.
2. разрешается вставку только в КолСкрСтр  и останавливал бы процесс вставки на последней скрытой строке в используемом блоке - 33, 63, 94 или 124.
3. запрещал вносить изменения в итоговые строки всех четырех блоков - диапазон С34:J34, диапазон С64:J64, диапазон С94:J94 и диапазон С1244:J124.
4. Сигнализировал бы об этом через сообщение "Ошибка! Превышен лимит по строкам"

По пункту 2 не знаю, возможно ли при этом сохранить работоспособность формул в ячейках H34 , H64, H94, H124, но необходимо чтобы они по прежнему отображали изменяемый итоговый результат. Очень надеюсь на помощь, хотя понимаю, что возможно для формата форума я слишком многого ожидаю.    
Изменено: Excelman - 09.04.2017 18:29:53
 
 
 
#13
09.04.2017 18:18:46
Цитата
Excelman написал:
Можно ли как то в Ваш код в модуле листа дополнить условием,
Думаю, что нет. Макрос начинает работать после вставки и не знает, что в ячейках было до вставки. Поэтому, даже если и определить, что "залезли" на ячейки с формулами, всё равно "откатку" не сделать.
Цитата
Excelman написал:
Если не затруднит пожалуйста объясните логику формулы по столбцу K хотя бы в двух словах
Числа я складываю, а строки - складываю кол-во символов. Если сумма =0, значит в яч. ничего нет. Надо, чтобы не было ничего в ячейках текущей и предыдущей строк.Тогда пишем 0. Иначе - 1. Предыдущая строка нужна, чтобы было, куда вставлять новые данные.
Изменено: Михаил Лебедев - 09.04.2017 18:20:37
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
 
 
#14
09.04.2017 18:23:06
Михаил Лебедев, Спасибо Вам большое!
 
 
 
#15
09.04.2017 18:32:42
А защита листа с запретом вставки строк не вариант?
Ещё такой вариант: создаём именованные диапазоны (по 30 строк) и на событие Change считаем количество строк в них: если превысило 30 - дикий вопль и отмена действия.
 
 
 
#16
09.04.2017 18:43:02
Цитата
Юрий М написал:
А защита листа с запретом вставки строк не вариант?
Здесь я не уверен,что одни и те же понятия мы имеем ввиду. В описании под вставкой строк я подразумеваю именно вставку значений из буфера обмена в скрытые строки, но не то действие, когда мы на листе через Правка вставить строку. В любом случае чаще всего придется пользоваться копированием строк из другого листа и делать вставку значений из буфера. Поэтому если защита листа запретит буфер это не вариант. А вот это конечно интересно
Цитата
Юрий М написал:
Ещё такой вариант: создаём именованные диапазоны (по 30 строк) и на событие Change считаем количество строк в них: если превысило 30 - поднимаем дикий вопль и отмена действия.
Самому реализовать такой код мне не по зубам. Могу только смиренно просить Вас о таком решении...  
 
 
 
#17
09.04.2017 23:03:34
Цитата
Excelman написал:
не то действие, когда мы на листе через Правка вставить строку
Именно про это я и говорил. Ведь если будет запрет на вставку строк, то количество ведь увеличить уже не удастся. Вот и получится, что вставку из буфера Вы сможете осуществлять только в 30 имеющихся строк.
 
 
 
#18
10.04.2017 08:40:52
Отличный вариант! Хорошо, когда есть не одна голова, а несколько! :)
Изменено: Михаил Лебедев - 10.04.2017 09:16:16 (Загрузил файл)
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
 
 
#19
10.04.2017 17:27:06
Юрий М, Михаил Лебедев, РАБОТАЕТ!!! Большое ВАМ ВСЕМ СПАСИБО!!! Только я не понимаю, как все это работает? Не могу успокоиться пока хотя бы примерно не разберусь.
Особенно Вот здесь я в полнейшем тупике:

Цитата
Юрий М написал:
Именно про это я и говорил. Ведь если будет запрет на вставку строк, то количество ведь увеличить уже не удастся. Вот и получится, что вставку из буфера Вы сможете осуществлять только в 30 имеющихся строк.
Я всегда считал, что, если поставить защиту на лист то никакие изменения на нём НЕВОЗМОЖНЫ. Да, строку вставить нельзя, но почему буфер работает??? Защита листа ведь и буфер должна блокировать или нет?  
Михаил Лебедев, начал "ковыряться" в Вашем коде. Нужна помощь для осмысления как всё это работает. Понял, что Вы внесли изменения в Макрос1 и Макрос2, а именно: 1.Отключение защиты, 2.Срабатывание автофильтра,  3.Включение защиты. Для меня не понятно значение нового Макрос3. Вроде похоже, что код макроса отвечает за настройку защиты (т.е. что разрешается и что запрещается) и операцию "копирование-вставка" из другой книги в проект. С другой стороны есть подозрение  что  этот макрос вообще оставлен по ошибке или Вы проводили с его помощью тест, по крайней мере, я не нашел в коде из какого места Макрос3 запускается автоматом. Также я не понимаю как Вы добились того, что защита пропускает редактирование по ячейкам в диапазонах [C5;H33] [С35; 63] [C65; H93] [C95; H123], и не позволяет проводить редактирования по итоговым строкам, где это всё "спрятано"?

 
Изменено: Excelman - 10.04.2017 17:38:47
 
 
 
#20
11.04.2017 15:09:54
Цитата
Excelman написал:
Я всегда считал, что, если поставить защиту на лист то никакие изменения на нём НЕВОЗМОЖНЫ.
Нет, это не так. Хотя раньше, в первых версиях ексель было почти что так. Вы сами поставьте защиту на лист, и увидите, сколько там есть всяких "...кроме..." :)
А кроме того - защита не будет защищать те ячейки, которые помечены как не требующие защиты. Посмотрите свойства ячейки ("Формат ячеек" в контекстном меню ячейки), последнюю вкладку "Защита".В указанных Вами ячейках  [C5;H33] [С35; 63] [C65; H93] [C95; H123] галочка защиты - снята. Это позволяет вводить данные только в те ячейки, в которые нужно.

Цитата
Excelman написал:
Для меня не понятно значение нового Макрос3
Вот так рождаются мифы и легенды, :) кто-то не убрал за собой мусор, а кто-то другой потом не может понять его сокровенный смысл (потому что его нету :) )
В общем, Вы правильно догадались в итоге. Раскрываю карты. Просто я, чтобы не вспоминать кодировку установки и снятия пароля, включил макрорекордер. А потом - забыл его выключить... а потом - забыл получившийся Макрос3 удалить, когда скопировал из него все, что было мне нужно :) Сделайте это за меня, удалите его, пожалуйста, Вы правильно поняли, он - уже не нужен.

А про вставку строк, что написал Юрий М - это вы, думаю, просто друг друга недопоняли. Вы под вставкой понимаете вставку данных в существующие строки, а он - реальную вставку новых строк Потому что Вы написали: "...если нужно сделать вставку данных с размером больше одной строки.."
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
 
 
#21
11.04.2017 16:52:24
Цитата
Михаил Лебедев написал:
Посмотрите свойства ячейки ("Формат ячеек" в контекстном меню ячейки), последнюю вкладку "Защита".В указанных Вами ячейках  [C5;H33] [С35; 63] [C65; H93] [C95; H123] галочка защиты - снята. Это позволяет вводить данные только в те ячейки, в которые нужно.
Простите, после такого.... мне даже как то неудобно про макросы, про VBA...  :oops: Я это к тому, что даже и не знал о таких очевидных вещах. Ну не приходилось мне применять защиту листа, за ненадобностью. Пару раз на работе попадались такие файлы (с защитой). Там щелкнул, тут щелкнул... редактирование не разрешалось и сделал поспешные выводы о "тотальном запрете" на редактирование. Заглядывать в свойство ячеек как то было невдомек. То есть, как я понимаю, Вы сначала установили защиту на лист и потом, что называется "руками", в нужных диапазонах через свойство ячеек отключили защиту?! А я по незнанию пытаюсь найти этот сокровенный макрос сокрытый от глаз :) а всё оказывается на поверхности!
СПАСИБО, что так обстоятельно всё мне "разжевали"! Всем спасибо, кто откликнулся на проблему. Всем добра!
Изменено: Excelman - 11.04.2017 16:55:00
 
 
 
Страницы: 1
Наверх