Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
    rFrom.Rows(i).Copy Destination:=rTo.Offset(Ofset)
    Ofset = Ofset + 1
Next i

End Sub
share|improve this answer
That (Alt)+; trick is neat, @Sean! Where is it documented? – Scott Sep 12 '12 at 16:41
@Scott, there is a big list here: – 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
    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



    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!


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.


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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.