Вставить диапазон в отфильтрованную таблицу

Question

У нас есть следующий лист Excel:

Ячейки с A2 по A10 фильтруются: опция 3 и опция 4 отфильтровываются. Теперь мы хотим вставить числа в A12: A16 рядом с видимыми ячейками (так в B2: B10). В любом случае, как мы могли бы сделать это?

Answer 1

Пусть Y и Z представляют два столбца с нулевыми значениями (т. Е. За пределами ваших данных).

1. Переместите данные с A12-A16 на B12-B16.
2. В Y2 введите =ROW() .  (В Excel 2010 =ROW может работать и даже может потребоваться.)
3. Перетащите вниз до Y10.
4. Выбрав Y2-Y10, введите Alt+; ,  (@Sean: Спасибо, что научили меня этому трюку!)
5. Копировать; затем нажмите на A12 и вставьте.
6. Введите =IFERROR(VLOOKUP(Y2,A$12:B$16,2,FALSE),"") в Z2 и перетащите вниз до Z10.  (Теперь это должно выглядеть как данные в B12-B16.)
7. Отключить фильтрацию.
8. Скопируйте Z2-Z10 и вставьте значения в B2-B10.
9. (Удалить столбцы Y и Z.)

Answer 2

Я не могу найти способ вставить и избежать скрытых строк (я могу скопировать только видимые элементы, используя ALT+; перед копированием), не прибегая к VBA.

Этот макрос копирует A12:16 в B2 и вниз, пропуская скрытые строки

Sub Paste2VisRows()

Dim rFrom As Range, rTo As Range
Dim i As Long, Ofset As Long

Set rFrom = Range("A12:A16")
Set rTo = Range("B2")

For i = 1 To rFrom.Rows.Count
    Do Until Not rTo.Offset(Ofset).Rows.Hidden
        Ofset = Ofset + 1
    Loop
    rFrom.Rows(i).Copy Destination:=rTo.Offset(Ofset)
    Ofset = Ofset + 1
Next i

End Sub

Answer 3

Скопируйте код макроса из @SeanCheshire с небольшими изменениями, чтобы он предложил пользователю выбрать область копирования и область вставки.

В области копирования можно выбрать диапазон, например A1:A10, в области Вставить нужно просто выбрать 1 ячейку, например B11, если пусть указанные B12-B14 скрыты, то они будут вставлены в B11, B15, B16, ... B23

Sub Paste2VisRows()

Dim rFrom As Range, rTo As Range
Dim i As Long, Ofset As Long

Dim RngCopySelection As Range
Set RngCopySelection = Application.InputBox("Please select copy area", "Area Selection", , , , , , 8)

Dim RngPasteSelection As Range
Set RngPasteSelection = Application.InputBox("Please select paste area", "Area Selection", , , , , , 8)

Set rFrom = RngCopySelection
Set rTo = RngPasteSelection

For i = 1 To rFrom.Rows.Count
    Do Until Not rTo.Offset(Ofset).Rows.Hidden
        Ofset = Ofset + 1
    Loop
    rFrom.Rows(i).Copy Destination:=rTo.Offset(Ofset)
    Ofset = Ofset + 1
Next i

End Sub

Answer 4

«Привет, у меня были проблемы с вышесказанным, (Excel 2007); он продолжал копировать последнее значение из "rangeFrom" в ячейки ниже "rangeTo". Непредсказуемо. Иногда это работало, иногда нет. В результате у меня есть электронная таблица с примерно 100 000 строк данных, которые, по моему мнению, повреждены ... D'OH!

Исходя из вышеизложенного, я разработал эти идеи с парой функций безопасности:

Sub copyIntoFilteredRange()

Dim rTo As Range
Dim rFrom As Range
Dim j As Integer 
Dim k As Integer

Set rFrom = Application.InputBox("Select Copy Range", , , , , , , 8)
Set rTo = Application.InputBox("Select Paste Range", , , , , , , 8)

' The following If-Then segment ensures that ranges are the same size:
If rFrom.Rows.Count = rTo.Rows.SpecialCells(xlCellTypeVisible).Count Then


j = 1
' j will count until the correct number of cells is copied

Do While j < rFrom.Rows.Count

    For k = 1 To rTo.Rows.Count
    ' k will iterate through the whole of the rTo Range including hidden cells, and 
    ' copying will only happen as long as 1. j <= no. of cells in range and
    ' 2. The cell is not hidden.
        If rTo.Rows(k).Hidden = False Then
            rFrom.Rows(j).Copy Destination:=rTo.Rows(k)
            j = j + 1
        End If
    Next k

Loop


Else

    MsgBox "The sizes do not match"

End If


End Sub

Я новичок в VBA (более комфортно с Java, и новичком в этом тоже), поэтому, пожалуйста, укажите ошибки моего пути, если это необходимо.

Приветствия за помощь!

Бен

Answer 5

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

1. Заполните отдельный временный столбец
2. Удалить фильтр
3. Убедитесь, что только ячейки в строках с необходимыми данными заполнены
4. Нумеровать всю таблицу в другом столбце
5. Сортировка таблицы в порядке возрастания с использованием
6. Вставьте необходимые данные
7. Восстановить исходный порядок с помощью нумерованного столбца
8. Удалить временные столбцы

Заметки. * Использование Office 2007. * В моем понимании это единственный логичный способ.

Answer 6

Если вы используете Office 2013 и выше, есть очень элегантное решение: это Flash Fill (Ctrl +E) - (Лента Home- Редактирование - Fill -Flash Fill) Но Flash Fill сложно, не так просто, как Ctrl +V - потому что вы должны привести хотя бы один пример в пункт назначения, чтобы он работал!

Так как это работает.

1. Шаг 1: Скопируйте первую ячейку источника и
2. вставьте значение в ячейку в отфильтрованном месте назначения. Это "пример".
3. Выберите все исходные ячейки и нажмите Ctrl +C
4. наведите курсор на ячейку, недавно обновленную на месте назначения, затем
5. "Ctrl + E" будет мигать.
   (Только видимые / отфильтрованные ячейки будут заполнены из источника.)

Мы любим Flash Fill для данных, вставленных в отфильтрованное место назначения.