Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We have the following excel sheet:

Start situation

The cells A2 to A10 are filtered: option 3 and option 4 are filtered out. Now we want to paste the numbers in A12:A16 next to the visible cells (so in B2:B10). Any way how we could accomplish this?

share|improve this question
    
@DaveRook: should be clearer – Terry Sep 11 '12 at 9:17
    
In B2 just add the code =SUM(A12) and like wise for the others - or is this going to be a dynamic query, meaning the cell references you've used will actually change based upon the results of Column1? – Dave Sep 11 '12 at 9:22
    
No, we have thousands of numbers from another sheet that we want to past in an existing table that's filtered. It's gonna be a one time paste, but doing this manually will take a long time. – Terry Sep 11 '12 at 9:26
    
And what is A12:A16? Is this an example of the figures from the other spreadsheet? Does their value change based upon the filter in Column1? – Dave Sep 11 '12 at 9:28
    
Yes, I've created a simplified copy to test it and to use it on here. Those 5 values represent the data we have to copy from the other sheet (so in fact thousand of numbers compared to the 5 here). – Terry Sep 11 '12 at 9:29

Let Y and Z represent two scratch columns (i.e., beyond your data).

  1. Move your data from A12-A16 to B12-B16.
  2. In Y2, enter =ROW().  (In Excel 2010, =ROW might work and might even be required.)
  3. Drag down to Y10.
  4. With Y2-Y10 selected, type Alt+;.  (@Sean: Thanks for teaching me that trick!)
  5. Copy; then click in A12 and paste.
  6. Type =IFERROR(VLOOKUP(Y2,A$12:B$16,2,FALSE),"") into Z2 and drag down to Z10.  (This should now look like the data in B12-B16.)
  7. Turn off filtering.
  8. Copy Z2-Z10, and paste values into B2-B10.
  9. (Delete columns Y and Z.)
share|improve this answer
    
+1 for not using VBA. – benshepherd Jan 29 '14 at 10:44

I cannot find a way to paste and avoid hidden rows (I can copy just the visible items using ALT+; before copying) without resorting to VBA.

This macro will copy A12:16 to B2, and down, skipping hidden rows

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
share|improve this answer
1  
That (Alt)+; trick is neat, @Sean! Where is it documented? – Scott Sep 12 '12 at 16:41
1  
@Scott, there is a big list here: excelshortcuts.co.uk/excel-keyboard-shortcuts – SeanC Sep 12 '12 at 16:46
    
For idiots like me: Alt+Shift+, (don't forget to press shift :p ) – nixda Jul 28 '13 at 0:22

Copy the macro code from @SeanCheshire with little modifications so it will prompt user to select the copy area and paste area.

Copy area can select range, example A1:A10, Paste area just need to select 1 cell, example B11, if let said B12 to B14 is hidden, then it will paste to 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
share|improve this answer
    
it works nicely, thanks ! – Muflix Oct 24 '14 at 16:37

`Hi, I had trouble with the above, (Excel 2007); it kept copying the last value in the "rangeFrom" into the cells below the "rangeTo". Unpredictably. Sometimes it worked, sometimes it didn't. As a result I have a spreadsheet with approximately 100,000 rows of data which I now believe to be corrupted... D'OH!

So based on the above I have developed these Ideas with a couple of safety features:

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

I am new to VBA (more comfortable with Java, and novice at that too), so please point out the errors of my ways if necessary.

Cheers for the help!

Ben

share|improve this answer
    
Should have said, Thanks to cww and Sean Cheshire for providing original code. – Ben Thomas Nov 13 '14 at 22:49

After copying the cells in A12:A16, select B2:B10 (the cells in which to paste). Then go up to the ribbon Home -> Find & Select -> Visible Cells Only.

Your selection won't visibly change, but when you paste it will only paste in your desired cells.

Source: https://support.office.com/en-in/article/Copy-visible-cells-only-d8b76986-2d1e-4d59-a01e-0d8c68ca5379

share|improve this answer
    
(1) This doesn't work in Excel 2013.  What version did you use when you tested it?  (2) I suspect that you many have misunderstood the question; it is not about copying visible cells only, but rather pasting into visible cells only.  (3) You have left out parts of your answer. – Scott Jan 4 at 12:59
    
@Scott You're right, I misunderstood the question. I was looking for how to copy and come across this question, and figured my answer would be helpful. Should I delete it since it doesn't appear to apply to this situation? Or is it worth keeping around if I flesh out the answer better? – Brian J Jan 26 at 19:30
    
If you can turn it into a useful answer to this question that contributes concepts, techniques, and/or details not covered in the other answers, by all means do so. Otherwise, you might as well delete it. – Scott Jan 26 at 19:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .