Хитрости »
Основные понятия (22)Здесь собраны статьи, в которых разъясняются базовые понятия работы в Excel и VBA, а так же проблемы, с которыми сталкивается большинство начинающих
Сводные таблицы и анализ данных (5) Раздел поможет изучить сводные таблицы и научиться их использовать "на полную"
Графики и диаграммы (4) Раздел поможет научиться создавать диаграммы и графики в Excel, в том числе нестандартные
Работа с VB проектом (10) С помощью статей раздела вы научитесь создавать процедуры программно и выполнять различные операции с объектами самого VBA
Power BI и Power Query (5) Здесь собраны статьи, раскрывающие различные возможности мощнейшего инструмента для визуализаций бизнесс-процессов Power BI и надстройки для Excel Power Query
Условное форматирование (5) Этот раздел поможет поближе познакомиться с Условным форматированием на примерах различных ситуаций
Списки и диапазоны (5) Статьи, посвященные работе не только с выпадающими списками, но и с диапазонами и хитростями их применения в рабочих файлах
Макросы(VBA процедуры) (59) Статьи раздела направлены на изучение VBA с детальным разбором кодов. Множество статей с примерами кодов под всевозможные ситуации с комментариями и пояснениями
Разное (34) Собраны статьи, которые не подходят ни под одну из представленных выше категорий или входят сразу в несколько. Но эти статье не менее полезные!

Как вставить скопированные ячейки только в видимые/отфильтрованные ячейки

 

В общем-то смысл статьи уже, думаю, понятен из названия. Просто чуть-чуть расширю.

Ни для кого не секрет, что Excel позволяет выделить только видимые строки(например, если некоторые из них скрыты или применен фильтр).

если кто-то не знает, как это сделать: выделяем диапазон - Alt+;(для английской раскладки);Alt+ж(для русской). Подробнее можно почитать здесь.

Так вот, если скопировать таким образом только видимые ячейки, то скопируются они как положено. Но при попытке вставить скопированное в диапазон отфильтрованный(либо содержащий скрытые строки) - то результат вставки будет не совсем такой, как Вы ожидали. Данные будут вставлены даже в скрытые строки.

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

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

Теперь можно скопировать нужный диапазон нажатием клавиш Ctrl+q, а вставить его в отфильтрованный - Ctrl+w.

Скачать пример

  Tips_Macro_CopyPasteInHiddenRows.xls (46,5 KiB, 5 716 скачиваний)


Копируем только видимые ячейки и вставляем только в видимые
По просьбам посетителей сайта решил доработать данную процедуру. Теперь возможно копировать любые диапазоны: со скрытыми строками, скрытыми столбцами и вставлять скопированные ячейки также в любые диапазоны: со скрытыми строками, скрытыми столбцами. Работает совершенно так же, как и предыдущий: нажатием клавиш Ctrl+q копируем нужный диапазон(со скрытыми/отфильтрованными строками и столбцами или не скрытыми), а вставляем сочетанием клавиш Ctrl+w. Вставка производится так же в скрытые/отфильтрованные строки и столбцы или без скрытых.
Если в копируемом диапазоне присутствуют формулы, то во избежание смещения ссылок можно копировать только значения ячеек - т.е. при вставке значений будут вставлены не формулы, а результат их вычисления. Или если необходимо сохранить форматы ячеек, в которые происходит вставка - будут скопированы и вставлены только значения ячеек. Для этого надо заменить строку в коде(в файле ниже):

на такую:

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

Скачать пример:

  Tips_Macro_CopyPasteInHiddenCells.xls (54,5 KiB, 4 024 скачиваний)


Так же см.:
Excel удаляет вместо отфильтрованных строк - все?! Как избежать


Статья помогла? Сделай твит, поделись ссылкой с друзьями!

Поиск по меткам

Access Multex Outlook Power BI Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Настройка Поиск данных Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx вебинар ссылки статьи тренинг
Обсуждение: оставлено 74 коммент.
  1. Дмитрий:

    Дмитрий, здравствуйте!
    У меня почему-то не работают ваши файлы.
    Приведу пример на последнем файле:
    Открываю его в Excel 2013; разрешаю редактирование-вылезает ошибка Run-time error '1004':
    Method 'OnKey' of object'_Application' failed
    Когда нажимаю debug вылезает окошко макроса и там подсвечена желтым часть строки:
    Application.OnKey "^q", "My_Copy"
    А если пробую нажимать в таблице ctrl+q, то вылезает окошко форматирования
    Подскажите, пожалуйста, как это исправить.

    • Дмитрий, когда скачиваете файл с интернета - он открывается в защищенном режиме, о чем у Вас появляется предупреждение. Нельзя обратиться к свойствам листов и книг, если книга открыта в защищенном режиме. А именно так по умолчанию открываются книги, скачанные откуда-то. Варианта по сути два:
      1. При открытии файла разрешить редактирование и перезапустить книгу.
      2. Добавить папку, в которую скачиваются вложения, в список доверенных(Файл -Параметры -Центр управления безопасностью -Параметры центра управления безопасностью -Надежные расположения).
      Есть еще вариант: в настройках макросов разрешить запуск макросов с уведомлением. Тогда сначала будет появляться запрос на редактирование, а самым последним запрос на разрешение выполнения содержимого.

      • Дмитрий:

        Дмитрий, спасибо большое!
        После пересохранения книги и добавления надежного расположения все заработало=)

  2. Дмитрий:

    Еще раз здравствуйте!
    А не подскажете, как копировать так, чтобы сохранялся формат и размеры ячеек в которые копируются данные?

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

      • Дмитрий:

        Прошу прощения за мою невнимательность, в памяти отложилось, что там было сказано только про формулы) Спасибо за ответ.

  3. Максим:

    Здравсвуйте. Подскажите как можно скорректировать Ваш код.
    У Вас в коде активируется активная ячейка активного листа
    Set rResCell = ActiveCell
    А можно ли, что бы активировалась выбранная пользователем ячейка в другой книге. И с нее уже шла вставка.
    Пытался написать макрос который бы активировал уже открытую книгу, но навыков в этом у меня не хватило.

    • Максим, в моем коде ничего не активируется :) Вы можете сочетанием клавиш(Ctrl+q) скопировать диапазон в одной книге, далее активировать нужную книгу, лист и ячейку в них и вставить, начиная с активной ячейки. Попробуйте.

  4. Максим:

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

  5. Максим:

    Большое спасибо за ссылку. Ваш макрос уже облегчил мне работу, надеюсь что смогу до конца разобраться )

  6. Михаил:

    Дмитрий, добрый день
    Подскажите, что я делаю не так?
    Выходит ошибка " Вставляемый диапазон не должен содержать больше одной области"
    Спасибо

    • Михаил, очевидно, не внимательно читали статью и копируете или вставляете диапазоны не при помощи указанных сочетаний(Ctrl+q и Ctrl+w), а стандартными средствами.

      • Георгий:

        такая же ошибка при попытке вставить скопированный диапазон со скрытыми ячейками в новое место
        пс. пользуюсь контроКУ +контролДаблЮ

        • Георгий:

          Вопрос решился, почему то скопировался не весь скрипт

        • Георгий, ну вот давайте хотя бы так: Вы статью внимательно читали? Код используете, наверное, первый, тот что в статье, а не тот, что во втором файле? И диапазон при этом копируете тоже отфильтрованный. А первый код не работает с такими диапазонами, о чем и говорит. Если хотите копировать любые диапазоны(со скрытыми ячейками) и в такие же любые вставлять - ну прочтите статью и увидите, что в конце статьи я приложил еще файл, в котором реализован как раз более универсальный код. Сам код при этом в статье ОТСУТСТВУЕТ, он только в файле.

  7. Фёдор:

    Дмитрий, Большое спасибо Вам за ваш макрос!

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Логин
Наши партнеры
Перейти
Перейти
Счетчики
Анализ сайта

Яндекс.Метрика
© 2016 Excel для всех  Войти