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

Bill HendersonWeb MarketingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Bill HendersonWeb MarketingAuthor 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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Bill HendersonWeb MarketingAuthor 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

Bill HendersonWeb MarketingAuthor 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?

Bill HendersonWeb MarketingAuthor 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,


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.