• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4204
  • Last Modified:

Paste into only visible cells in Excel 2013?


I have one Excel 2013 worksheet that contains contiguous rows of data - about 14k rows.

I have another worksheet with the relevant rows exposed and visible and I want to paste the 14k contiguous rows into the non-contiguous, but visible-only rows of the second sheet in a blank column.

Can this be done? Any variation of Find and Select > Visible Cells Only won't paste in properly, unless I'm still doing something wrong. I get a message that the two selections are different sizes, but they really aren't - not if we are only talking about the non-contiguous, but visible cells in sheet 2...

Any ideas how to do this?

Thank you

  • 5
  • 4
1 Solution
So to confirm what you want to do on a smaller scale: You want to copy rows 1 to 10 to the visible rows of a second sheet, so maybe rows 2 4 6 8 10 12 14 16 18 20 ?

Are you dealing with just one column?
Could you run some sort of vlookup maybe and insert that formula in the blank column?
Is that column blank for all rows, including the hidden ones?

One option, assuming your blank destination column is column B, and you want to copy sheet1!column A, would be to put the following formula in your visible columns:

=index(sheet1!A:A,countif($B$1:$b[row number above first visible row]))

Update the [row number above first visible row] parameter, and add 1 to the countif if you don't have a header row.

billium99Author Commented:
Thanks for posting.

So I have the modified formula:

=index('[Part-desc-check.xlsx]All-All Old'!$CD:$CD,countif(Sheet1!$AZ$1:$AZ41))

Where my source sheet is this All-All Old tab in the Part-desc-check.xslx workbook.

Then Sheet1 is the destination sheet and AZ is the column where I want to write the values. But I'm getting Too Few arguments message - like I don't have a criteria identified.

Also there is no reference or "key" field with which to do a VLOOKUP - I just know the values line up between these two fields based on the activities that got me to this point.

AZ41 is the last invisible cell, per your instructions and AZ42 is the first cell where I want this formula.

What am I doing wrong?

Thanks again

my bad, replace countif by counta
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

billium99Author Commented:
Ok so when I add this formula to a few cells in the destination sheetL

=INDEX('[Part-desc-check.xlsx]All-All Old'!$CD:$CD,COUNTA(Sheet1!$B$1:$B169))

 I see an immediate mismatch. For example, if I click on cell 170, the formula is pulling in 169, but it really should be pulling in B4. Because my source row is contiguous data and my destination row is non-contiguous, filtered data. So my filtered row shows rows 1, 42, 49, 170, etc.

Any ideas?


Which means you have data in that new column, we need to change the formula so it only counts the visible rows.

Let's try:
=INDEX('[Part-desc-check.xlsx]All-All Old'!$CD:$CD,SUBTOTAL(3,Sheet1!$B$1:$B169))

After you've got that working, you're going to have to do a VB paste values before removing the filter. The easiest for that is to run the attached macro:

Sub PasteValues_OnTheSpot()
Dim rgSel As range, rgArea As range

application.Calculation = xlCalculationManual
application.ScreenUpdating = False

On Error Resume Next

Set rgSel = selection.SpecialCells(xlCellTypeFormulas)

If Err <> 0 Then
    MsgBox "No formulas to convert to values."
    Exit Sub
End If

For Each rgArea In rgSel.Areas
    rgArea.Value = rgArea.Value
Next rgArea

application.Calculation = xlCalculationAutomatic
application.ScreenUpdating = True

End Sub

Open in new window

billium99Author Commented:
Hmm - I'm sorry if I'm not being clear. First, I do not have any values in the destination column.

Second, your new formula, when applied to the first cell in my filtered destination sheet, is an immediate mismatch.

This destination column is filtered and currently showing row 1 (label), then row 42, 49, 170, 446, 452, etc - it's all over the place.

The source sheet is using column CD, unfiltered. I need CD row 2 to populate AZ row 42. I need CD row 3 to populate AZ row 49. Etc:

Source Cell               Destination Cell
CD2                           AZ42
CD3                           AZ49
CD4                           AZ170
CD5                           AZ446
CD6                           AZ452

The current formula isn't doing this.
Could you load me a small sample of your file?

billium99Author Commented:

OK Sheet 1 has the filtered column as A and the destination column as B. Then Sheet 2 has unfiltered, contiguous data that I already know matches the values in the filtered Sheet1 column A.

So where Sheet 1 Filtered Column A row 2 is Value 1, unfiltered Sheet 2 Column A row 2 is the right match. But Value 3 in row 4 should have corresponding value Matching Value 3 in Sheet 2 row 3.

So somehow achieve the equivalent of "paste into visible only".

Thanks - please see attached

That's what I thought, check out the attached file and tell me if this works out for you. With option 1, you'll have to run a macro paste before you unfilter, with option 2, you won't have two.

CHeck it out,

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now