Ни для кого не секрет, что Excel позволяет выделять только видимые строки. Например, если некоторые из них скрыты или к ним применен фильтр.
если кто-то не знает, как это сделать: выделяем диапазон - Alt+;(для английской раскладки);Alt+ж(для русской). Подробнее можно почитать в этой статье
Если после выделения только видимых ячеек их скопировать, то скопируются они как положено. Но при попытке вставить скопированное в отфильтрованный диапазон(либо содержащий скрытые строки) - то результат вставки будет не совсем такой, как Вы ожидали. Данные будут вставлены даже в скрытые строки. Либо как вариант получим ошибку "Данная команда не применима к несвязанному диапазону".
Чтобы данные вставлялись только в видимые ячейки, можно применить такой макрос:
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 |
Для полноты картины, данные макросы лучше назначить на горячие клавиши(в приведенных ниже кодах это делается автоматически при открытии книги с кодом). Для этого приведенные ниже коды необходимо просто скопировать в модуль ЭтаКнига(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 |
Теперь можно скопировать нужный диапазон нажатием клавиш
Если необходимо
rCell.Copy ActiveCell.Offset(li, le) |
на такую:
ActiveCell.Offset(li, le) = rCell.Value |
Tips_Macro_CopyPasteInHiddenRows.xls (46,5 КиБ, 15 469 скачиваний)
По многочисленным просьбам доработал код. Теперь возможно копировать любые диапазоны: со скрытыми строками, скрытыми столбцами и вставлять скопированные ячейки также в любые диапазоны: со скрытыми строками, скрытыми столбцами. Работает совершенно так же, как и предыдущий: нажатием клавиш
Если в копируемом диапазоне присутствуют формулы, то во избежание смещения ссылок можно копировать только значения ячеек - т.е. при вставке значений будут вставлены не формулы, а результат их вычисления. Или если необходимо сохранить форматы ячеек, в которые происходит вставка - будут скопированы и вставлены только значения ячеек. Для этого надо заменить строку в коде(в файле ниже):
rCell.Copy rResCell.Offset(lr, lc) |
на такую:
rResCell.Offset(lr, lc) = rCell.Value |
В файле ниже обе эти строки присутствуют, Вам надо лишь оставить ту, которая больше подходит под Ваши задачи.
Tips_Macro_CopyPasteInHiddenCells.xls (54,5 КиБ, 14 424 скачиваний)
Так же см.:
Excel удаляет вместо отфильтрованных строк - все?! Как избежать
А как изменить данный макрос, что бы он не копировал видимый диапазон, а вырезал его?
Пробовал заменить все "Copy" на "Cut", но увы.
Александр, увы, но в данном случае простой замены мало. Cut работает чуть иначе. Поэтому Вам надо в коде сначала оставить все как есть, а в конце переноса данных(после Next iCol) добавить что-то вроде rCopyRange.Delete
копирование и вставка значений несвязных ячеек (диапазонов)
'Выделяете нужные ячейки на листе1, запускаете макрос - выбираете КУДА(destination) копировать - жмете ОК.
Public Sub ValuesOfDisconnectedCells()
Dim n&, c As Range, r As Range
ReDim a(1 To Selection.Cells.Count)
For Each c In Selection
n = n + 1: a(n) = c.Value
Next
Set r = Application.InputBox("Select destination cells", Type:=8)
If r.Cells.Count = n Then
n = 0
For Each c In r.Cells
n = n + 1:c = a(n)
Next
End If
End Sub
Можно приспособить данный макрос к работе в защищенных листах? Область вставки не заблокирована.
Перед вставкой снимайте защиту(можно кодом -Selection.Parent.Unprotect "password" ). Иначе не получится, т.к. используется метод SpecialCells, который невозможно использовать при активной защите листа.
Спасибо!!!
Это просто пеееееееесня!
СПАСИБОЧКИ ВАМ, ДМИТРИЙ, БОЛЬШУЩЕЕ!!!!!! Два дня сидела и не могла понять - как сделать так, чтобы копировались только значения ячеек, а не формулы - все комментарии прочла, не понимала - а всё элементарно - НУЖНО ОСТАВИТЬ ТОЛЬКО ТУ СТРОЧКУ, КОТОРАЯ ВСТАВЛЯЕТ ЗНАЧЕНИЯ!!!! Господи, всего - то пару слов в статье, в самом конце - и решение проблемы вселенского масштаба!!!!!!!!!!!
Спасибо Вам, большое! Успехов Вам и процветания Вам и Вашим близким!
Храни Вас Бог!
Добрый день! Подскажите, как поменять данный макрос, чтобы скопированные ячейки вставлялись значениями?
nekamon, для этого достаточно дочитать внимательно до конца статью. Там этот случай как раз описан с указанием того, какую строку кода на что заменить.
Ураа!!! Спасибо вам большое!
Добрый день! А можно, пожалуйста, доработанный код тоже выложить на страницу? не могу скачать пример Эксель. Спасибо!