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

покупка

Когда вы устанавливаете скрытие некоторых строк и столбцов на листе и хотите скопировать только видимые ячейки в Excel, можете ли вы просто скопировать и вставить их напрямую в Excel? Нет, для этого вам следует знать несколько хитростей. Выполните следующие действия, чтобы узнать, как можно копировать только видимые ячейки в Excel.

Скопируйте только видимые строки в Excel с помощью специальной команды Перейти

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

1. Выберите видимые строки, которые вы хотите скопировать.

2. Нажмите Главная > Найти и выбрать > Перейти к специальному, см. снимок экрана:

3. В Перейти к специальному в диалоговом окне выберите Только видимые ячейки вариант под Выберите, см. снимок экрана:

4. Затем нажмите OK кнопку, и будут выделены только видимые ячейки.

5. Затем вы можете скопировать их и вставить в любое место без скрытых строк или столбцов.

Удалить только видимые строки в Excel с помощью Kutools for Excel

Если вам нужно удалить только видимые строки и оставить скрытые строки, вы можете применить описанные выше шаги, чтобы сначала выбрать видимые строки, после выбора видимых строк нажмите клавишу Delete, чтобы удалить их сразу.

Но здесь я также могу представить вам простой и мощный инструмент — Kutools for Excel, С его Удалить скрытые (видимые) строки и столбцы, вы можете быстро удалить скрытые или видимые строки или столбцы только на листе или всей книге.

После установки Kutools for Excel, пожалуйста, сделайте следующее 🙁 Бесплатная загрузка Kutools для Excel сейчас )

1. Выберите видимые строки, которые хотите удалить.

2. Затем нажмите Кутулс > Удалить > Удалить скрытые (видимые) строки и столбцы, см. снимок экрана:

3. В Удалить скрытые (видимые) строки и столбцы диалоговое окно, выберите В выбранном диапазоне из Заглянуть раскрывающийся список, затем выберите Ряды в Удалить тип , и выберите Видимые строки под Подробный тип раздел, см. снимок экрана:

4. Затем нажмите OK Кнопка, все видимые строки были немедленно удалены, но скрытые строки остались.

С помощью этой функции вы также можете удалить пустые строки / столбцы и скрытые строки / столбцы в диапазоне / на листе или в книге по мере необходимости.

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

По умолчанию Excel копирует как видимые, так и скрытые ячейки. Однако можно скопировать только видимые ячейки.

В этом примере строка 2 скрыта:

Если просто скопировать, а затем вставить этот диапазон, результат получается следующим:

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

1. Выделите диапазон A1:A4.
2. На вкладке Home (Главная) нажмите кнопку Find & Select (Найти и выделить) и кликните по Go To Special (Выделить группу ячеек).
3. Выберите опцию Visible cells only (Только видимые ячейки) и нажмите OK.
4. Excel выделит видимые ячейки. Теперь нажмите сочетание Ctrl+C, чтобы скопировать диапазон.
5. Выберите ячейку A6 и нажмите Ctrl+V, чтобы вставить диапазон.

Copy selection and paste into only the visible cells.

Range › 12. Copy selection and paste into only the visible cells.

This tool copies the visible cells in your current selection and then pastes these into only the visible cells in a given destination range.
If there are hidden rows or columns in either the source or target range, then those will be skipped. The data will then be pasted into the first next visible row or column.
This tool can be useful when pasting data into a filtered list.

Screenshots

Starting this tool

• Click ASAP Utilities › Range › 12. Copy selection and paste into only the visible cells.
• Specify a Keyboard Shortcut: ASAP Utilities › Favorites & Shortcut keys › Edit your favorite tools and shortcut keys.

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

Ни для кого не секрет, что 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

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) .
Для полноты картины, данные макросы лучше назначить на горячие клавиши(в приведенных ниже кодах это делается автоматически при открытии книги с кодом). Для этого приведенные ниже коды необходимо просто скопировать в модуль ЭтаКнига(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

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)

rCell.Copy ActiveCell.Offset(li, le)