Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 –  Djerry 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. –  Djerry 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). –  Djerry Sep 11 '12 at 9:29

4 Answers 4

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 –  Sean Cheshire 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

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 at 10:44

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 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 at 22:49

Your Answer

 
discard

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.