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

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

Если после выделения только видимых ячеек их скопировать, то скопируются они как положено. Но при попытке вставить скопированное в отфильтрованный диапазон(либо содержащий скрытые строки) - результат вставки будет не совсем такой, как ожидалось. Данные будут вставлены даже в скрытые строки. Либо может появиться ошибка "Данная команда не применима к несвязанным диапазонам".
Решить стандартными функциями такую проблему нельзя, поэтому на помощь приходят "макросы"(коды, написанные на встроенном языке программирования Visual Basic for Application).

Копируем единый диапазон ячеек и вставляем только в видимые
Для решения проблемы нам по сути надо будет и копировать и вставлять ячейки кодом. Т.е. мы не будем использовать привычное копирование. Мы заменим его своей собственной процедурой копирования - макрос "My_Copy". А вставка будет макросом "My_Paste".
Итак, коды копирования и вставки:

Option Explicit
Dim rCopyRange As Range
'Этим макросом копируем данные
Sub My_Copy()
    If Selection.Count > 1 Then
        Set rCopyRange = Selection.SpecialCells(xlVisible)
    Else: Set rCopyRange = ActiveCell
    End If
End Sub
'Этим макросом вставляем данные, начиная с выделенной ячейки
Sub My_Paste()
    If rCopyRange Is Nothing Then Exit Sub
    If rCopyRange.Areas.Count > 1 Then MsgBox "Вставляемый диапазон не должен содержать более одной области!", vbCritical, "Неверный диапазон": Exit Sub
    Dim rCell As Range, li As Long, le As Long, lCount As Long, iCol As Integer, iCalculation As Integer
    Application.ScreenUpdating = False
    iCalculation = Application.Calculation: Application.Calculation = -4135
    For iCol = 1 To rCopyRange.Columns.Count
        li = 0: lCount = 0: le = iCol - 1
        For Each rCell In rCopyRange.Columns(iCol).Cells
            Do
                If ActiveCell.Offset(li, le).EntireColumn.Hidden = False And _
                   ActiveCell.Offset(li, le).EntireRow.Hidden = False Then
                    rCell.Copy ActiveCell.Offset(li, le)
                    lCount = lCount + 1
                End If
                li = li + 1
            Loop While lCount >= rCell.Row - rCopyRange.Cells(1).Row
        Next rCell
    Next iCol
    Application.ScreenUpdating = True: Application.Calculation = iCalculation
End Sub

Как использовать: Для начала надо убедиться, что разрешены макросы и при необходимости включить их: почему не работает макрос. Затем копируем код выше, из Excel переходим в редактор VBA(Alt+F11) -Insert -Module. Вставляем туда скопированный код. Теперь код можно вызывать нажатием клавиш Alt+F8 -выделяем имя макроса -Выполнить(Run).
Чтобы скопировать диапазон, надо выделить его и вызвать процедуру "My_Copy". Затем выделяем первую ячейку для вставки и вызываем код "My_Paste".
Но для большего удобства использования лучше назначить выполнение этих двух процедур("My_Copy" и "My_Paste") на горячие клавиши(в приведенных ниже кодах это делается автоматически при открытии книги с кодом). Для этого приведенные ниже коды необходимо просто скопировать в модуль ЭтаКнига(ThisWorkbook):

Option Explicit
'Отменяем назначение горячих клавиш перед закрытием книги
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnKey "^q": Application.OnKey "^w"
End Sub
'Назначаем горячие клавиши при открытии книги
Private Sub Workbook_Open()
    Application.OnKey "^q", "My_Copy": Application.OnKey "^w", "My_Paste"
End Sub

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

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

rCell.Copy ActiveCell.Offset(li, le)

на такую:

ActiveCell.Offset(li, le) = rCell.Value

Примечание: в коде выше копируется весь диапазон, даже если он содержит скрытые или отфильтрованные строки.

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

  Tips_Macro_CopyPasteInHiddenRows.xls (46,5 КиБ, 15 549 скачиваний)

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

rCell.Copy rResCell.Offset(lr, lc)

на такую:

rResCell.Offset(lr, lc) = rCell.Value

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

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

  Tips_Macro_CopyPasteInHiddenCells.xls (54,5 КиБ, 14 526 скачиваний)

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

Loading

100 комментариев

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

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

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

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

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

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

    1. Максим, так если Вы переделали под себя - что хотите от меня? Я не могу угадать что и как Вы там изменили и куда чего вставлять собрались. Могу лишь посоветовать эту статью: Как обратиться к диапазону из VBA. Там написано как обратиться к диапазону другой книги/листа и т.п. Возможно, поможет.

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

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

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

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

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

  9. Мужик, ты просто чёртов ГЕНИЙ ! Рахмет огромный, у меня всё работает (копировал макрос из файла примера). Слегка доработал (у меня и источник копирования и файл вставки - оба защищены, я в начале каждого макроса вставил снятие пароля и в конце - опять на пароль).

    Единственное, что не понимаю в твоём Коде - это вот это вот:
    'получение отсортированных адресов диапазона
    Function NoDups(rRng As Range) - для чего эта функция? И участвует ли она в макросах копирования и вставки? (насколько я понял - не участвует, но вдруг...)

    1. daur251282, спасибо за отзыв. Функция NoDups нужна, чтобы отсортировать адреса скопированных ячеек в нужном порядке. Актуально, если выделение происходило через Ctrl или снизу вверх - в этом случае они по умолчанию могут располагаться в произвольном порядке и вставка пройдет неверно.

    1. Дмитрий, спасибо за макрос, из всех найденных аналогичных он лудший.
      Есть предложение для повышения удобства использования.
      Зачем нужно копировать область средствами макроса? По Ctrl-C начиная с Excel 2007 уже копируются только отфильтрованные строки. Предлагаю их подхватить из буфера и вставлять по Ctrl-Q. Тогда может и обработка быстрее пойдет, на больших объемах-то думает долго.
      А использование Ctrl-W неудобно, это стандартное сочетание винды для закрытия окон

      1. ixet08, к сожалению у VBA нет нормального доступа к буферу обмена Excel. Поэтому приходится принудительно создавать новый объект, чтобы его обработать. При этом само по себе копирование не занимает больше времени, чем стандартное Ctrl+C. Тут проблема именно при вставке, т.к. идет определение скрытых или отображенных ячеек. И это не быстро. В своей надстройке MulTEx я значительно ускорил этот процесс, но там код уже совершенно иной.
        Сочетания клавиш можно сменить на свои - где это сделать в статье показано(искать в модуле ЭтаКнига). Т.к. у всех разные предпочтения к гор.клавишам я специально отдельно привел код где это можно сделать.

Добавить комментарий

This site uses Akismet to reduce spam. Learn how your comment data is processed.