Cole um intervalo em uma tabela filtrada

Question

Temos a seguinte planilha do excel:

As células A2 a A10 são filtradas: a opção 3 e a opção 4 são filtradas. Agora queremos colar os números em A12: A16 ao lado das células visíveis (então em B2: B10). De alguma forma, como poderíamos conseguir isso?

    

Answer 1

Deixe Y e Z representar duas colunas de risco (isto é, além dos seus dados).

1. Mova seus dados de A12-A16 para B12-B16.
2. Em Y2, insira =ROW() . (No Excel 2010, =ROW pode funcionar e pode até ser necessário).
3. Arraste para baixo até Y10.
4. Com Y2-Y10 selecionado, digite Alt + ; . (@Sean: Obrigado por me ensinar esse truque!)
5. Copiar; depois clique em A12 e cole.
6. Digite =IFERROR(VLOOKUP(Y2,A$12:B$16,2,FALSE),"") em Z2 e arraste para baixo para Z10. (Isso deve agora parecer com os dados em B12-B16.)
7. Desativar a filtragem.
8. Copie Z2-Z10 e cole os valores em B2-B10.
9. (Exclua as colunas Y e Z.)

Answer 2

Não consigo encontrar uma maneira de colar e evitar linhas ocultas (posso copiar apenas os itens visíveis usando ALT + ; antes de copiar) sem recorrer ao VBA.

Esta macro irá copiar A12: 16 para B2 e para baixo, ignorando as linhas ocultas

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

Copie o código de macro de @SeanCheshire com pequenas modificações para que o usuário solicite que selecione a área de cópia e a área de colagem.

A área de cópia pode selecionar o intervalo, por exemplo, A1: A10, Área de colagem só precisa selecionar 1 célula, exemplo B11, se deixar dito B12 para B14 está oculto, então ele vai colar para 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

'Oi, eu tive problemas com o acima, (Excel 2007); Ele continuou copiando o último valor no "rangeFrom" nas células abaixo do "rangeTo". Imprevisível. Às vezes funcionava, outras vezes não. Como resultado, tenho uma planilha com aproximadamente 100.000 linhas de dados que agora acredito estarem corrompidas ... D'OH!

Portanto, com base no que foi exposto, desenvolvi essas ideias com alguns recursos de segurança:

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

Sou novo no VBA (mais confortável com Java e novato nisso também), então, por favor, aponte os erros dos meus caminhos, se necessário.

Felicidades pela ajuda!

Ben

    

Answer 5

A ideia é classificar as linhas necessárias na parte superior da tabela, colar os dados e recorrer novamente ao pedido original.

1. Preencha uma coluna temporária separada com as
2. Remover filtro
3. Certifique-se de que apenas as células nas linhas com os dados necessários estão preenchidas com as
4. Numerar toda a tabela em outra coluna
5. Classifique a tabela em ordem crescente usando as
6. Cole os dados necessários
7. Restaurar o pedido original usando a coluna numerada
8. Excluir colunas temporárias

Notas. * Usando o Office 2007. * No meu entendimento, esta é a única maneira lógica.

    

Answer 6

Se você usa o Office 2013 e acima, há uma solução muito elegante: É o Flash Fill (ctrl + E) - (Edição Inicial da Faixa de Opções - Preenchimento - Preenchimento Flash) Mas o Flash Fill é complicado, não tão simples quanto Ctrl + V, porque você deve dar pelo menos um exemplo no destino para fazê-lo funcionar!

Então, como funciona.

1. etapa 1: copie a primeira célula da origem e
2. cole o valor na célula no destino filtrado. Este é o "exemplo".
3. Selecione todas as células de origem e ctrl + C
4. colocar o cursor na célula atualizada recentemente no destino e, em seguida,
5. "ctrl + e" piscará.
   (Apenas as células visíveis / filtradas serão preenchidas a partir da fonte.)

Adoramos o Flash Fill para dados colados no destino filtrado.