Страницы: 1
RSS
ClearContents и скрытые строки
 
Всем доброго дня.

Столкнулся с неожиданной проблемой (раньше как-то не замечал)...

Задача - очистить ячейки и границы в заданном диапазоне, делаю соответственно через Range.ClearContents и Range.Borders.
Вопрос в том, что если на листе установлен автофильтр, то эти методы не срабатывают для скрытых строк, Для скрытых вручную срабатывают, а для скрытых фильтром нет.

Есть ли какие-то красивые решения этой проблемы, кроме перебора?
Снятие фильтров и отображение строк, само собой, не рассматривается.

Спасибо.
 
Файл-пример будет?
Согласие есть продукт при полном непротивлении сторон
 
мне помогло вот это
Код
.SpecialCells(xlCellTypeConstants).ClearContents
но если будут формулы то уже работать не будет
 
Файл-пример можно сделать за одну минуту, например, вот такой
Но вопрос же не в конкретном файле, а в особенности работы метода.
Изменено: AShatov - 17.10.2017 16:33:48
 
Цитата
Watcher_1 написал: .SpecialCells(xlCellTypeConstants).ClearContents
Да, через SpecialCells неплохой вариант. В принципе всё содержимое можно почистить.

А если мне ещё и границы ячеек нужно очистить - тут есть какое-то решение?
 
здравствуйте
Цитата
AShatov написал:
вопрос же не в конкретном файле, а в особенности работы метода
ошибаетесь — не всегда. Бывает куча других проблем типа локализации, подгруженых баз или особенностей самого файла. С примером всё станет намного яснее.
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Ну я файл приложил, попробуйте  :)  
 
Цитата
AShatov написал:
А если мне ещё и границы ячеек нужно очистить
Код
Columns(2).Clear
Согласие есть продукт при полном непротивлении сторон
 
Цитата
AShatov написал:
А если мне ещё и границы ячеек нужно очистить
вместо .ClearContents попробуйте .BorderLine=0 .Borders.LineStyle = False
О границах…
Изменено: Jack Famous - 17.10.2017 15:45:55
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Цитата
Jack Famous написал:
вместо .ClearContents попробуйте .Borders.LineStyle = False
Я же писал - не работает для скрытых))
Цитата
AShatov написал:
Задача - очистить ячейки и границы в заданном диапазоне, делаю соответственно через Range.ClearContents и Range.Borders.
Вопрос в том, что если на листе установлен автофильтр, то эти методы не срабатывают для скрытых строк
 
Цитата
AShatov написал:
Да, через SpecialCells неплохой вариант. В принципе всё содержимое можно почистить.
а я имел ввиду вот это)) если у вас .SpecialCells(xlCellTypeConstants).ClearContents справляется со скрытыми значениями, то и .SpecialCells(xlCellTypeConstants).Borders.LineStyle = False должен убрать границы. Поправьте, если не так
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Да, так будет работать.
Ну вернее сам не пробовал, но уверен, что будет))

Но тут возникает другой вопрос - а что если данных в этих ячейках нет, а форматирование (ну или границы) нужно убрать? Тогда они не попадут под отбор SpecialCells(xlCellTypeConstants) и останутся не очищенными.

Вот... пока никаких разумных решений, кроме тупого перебора циклом, в голову не приходит...
 
Цитата
AShatov написал:
Снятие фильтров и отображение строк, само собой, не рассматривается.
если бы не это условие, то именно так я бы и делал. Как вариант, можно "запомнить" все фильтры, спокойно поработать, не создавая себе лишних проблем и, в конце, "включить" все фильтры обратно. Но в плане кода по "запоминанию", к сожалению, не помогу — не сталкивался пока что…
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Цитата
Jack Famous написал:  можно "запомнить" все фильтры ....
этот код будет сложнее чем 'тупой перебор'.  
Согласие есть продукт при полном непротивлении сторон
 
Sanja, неожиданно… А если таблица "умная" - тоже?
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Цитата
Jack Famous написал: Sanja , неожиданно...
Перебор и очистка ячеек
Код
Dim cl As Range
With ActiveSheet
    For Each cl In Intersect(.UsedRange, .Columns(2))
        cl.Clear
    Next
End With
Перебор фильтров автофильтра (что является упрощенным, частным случаем фильтрации). Пример из встроенной Справки.
Скрытый текст

Думаю, для умной таблицы разницы не будет
Согласие есть продукт при полном непротивлении сторон
 
Цитата
Sanja написал:
Перебор фильтров автофильтра
страшно как  8-0 ))) спасибо — учимся потихоньку)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Дело в целесообразности. Возможно, при ОЧЕНЬ большом кол-ве строк в таблице будет быстрее перебрать фильтры, запомнить их значения, снять их, очистить столбец и снова применить фильтр, чем 'тупо перебрать' эти 100500 миллионов строк
Согласие есть продукт при полном непротивлении сторон
 
Sanja, разумеется, как и всегда - дело в объёме. Я тут что подумал (чисто теоретически, т.к. закодировать не смогу): есть макрос на "волшебный автофильтр" с разными вариациями. Там ещё фильтр нужно над полем писать и макрос по событиям изменения листа фильтровал/не фильтровал таблицу. Так вот, есть ли вариант как-то "добраться" к "голым" данным о наличии/отсутствии фильтра без этого монстра из справки? Я имею в виду, что, возможно, в свойствах умной таблицы есть записи о применённых фильтрах, которые как раз и можно было бы "запомнить". К сожалению, это пока только умозрительно.
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Цитата
Остапа понесло...
Jack Famous, тема о чем?
 
vikttur, ну, если честно, то название темы мне не очень. Скорее «Операции со скрытыми ячейками с ограничениями» или более точно «Очистка скрытых ячеек с сохранением фильтра и без перебора циклом». Мне казалось, что мы по этому поводу и говорим.
Прошу прощения. Буду просто следить за темой, т.к. по коду у меня пока ничего.
Изменено: Jack Famous - 17.10.2017 19:06:14
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
В общем, хороших универсальных идей никто не предложил, тогда резюмирую.

Методы
Код
Range.Clear
Range.ClearContents
Range.Value
Range.Formula
Range.Borders
Range.Copy
и ряд других, отвечающих за редактирование содержимого и форматирование ячеек, НЕ действуют на ячейки, скрытые фильтром, если таковые попадаются в обрабатываемом диапазоне.

С одной стороны всё в полном соответствии с работой с отфильтрованным листом «вручную» (при нажатии, к примеру, Del очищаются только видимые ячейки, а скрытые фильтром игнорируются).
Но при работе из-под VBA такое поведение выглядит неочевидным или даже нелогичным. Ведь обращение идёт к Range целиком, а не к Range.SpecialCells(xlCellTypeVisible), т.е. нигде не указано, что обрабатываемая область должна чем-то ограничиваться. По крайней мере для меня эта особенность стала неприятной неожиданностью и привела к ошибке в программе, теперь придётся иметь её в виду при разработке.

Что делать, если всё же нужно работать с целым диапазоном, а не только с видимой его частью:
  1. Снимать фильтры, сохраняя предварительно критерии фильтрации в отдельном массиве/структуре, обрабатывать диапазон и ставить фильтры обратно.
  2. Перебирать диапазон по ячейкам.

Всем спасибо.
Изменено: AShatov - 23.10.2017 12:02:51
Страницы: 1
Наверх