Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jul 2011
    Thanked 0 Times in 0 Posts

    Question Copy-paste visible cells only and back process. How?


    I have an Excel 2003 file with visible and hidden rows. I need to translate only the visible rows (in an external application which doesn't recognize hidden vs visibile rows). So far, I've managed to export the visible rows into another spreadsheet by doing this:

    1. Select all the data that needs to be copy/pasted.
    2. Press F5 function key for the Goto command
    3. Click Special
    4. Enable the Visible Cells Only option (this will omit hidden rows/columns from your selection).
    5. Click OK
    6. Press Ctrl C to copy the data
    7. Select the destination cell, and press Ctrl V to paste the data

    New issue: in the above way, the rows with hidden content are not preserved, so the line order in the new spreadsheet is different from the original. That is to say:

    Original Excel
    1. Foo content 1 (visible)
    2. Foo content 2 (hidden)
    3. Foo content 3 (visible)


    Excel to be translated
    1. Foo content 1 (visible)
    2. Foo content 3 (visible)

    Now, I want the output of the copy-paste be like this:

    1. Foo content 1 (visible)
    2. [blank]
    3. Foo content 3 (visible)

    that is, every row maintains its row number, so that - at the end of the translation - I can paste the whole column back into the original Excel file.

    How can I achieve this? Any VBA guru can give me a hand?

    Thanks in advance for your help. Sorry if the question has been already posted. I've browsed the forum, but most references only explain part 1 of my issue (how to copy-paste visible cells only), which I have already solved (see above).


  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts
    In the code, You could copy everything including the hidden rows, to the new sheet. Then you could have VBA look at the source sheet and go through line by line and erase the contents in the destination that should be blank.

    But copying the destination rows and pasting them back to the source is going to be cumbersome as well, as you must do it line-by-line, ignoring the blank rows, since if you copy/paste as a group, the blanks will overwrite the data in the original sheet.

    Why are some rows hidden? Instead of hiding rows, a better method may be to sort on the whatever criterion is used to hide the rows so that the visible rows are all contiguous. Then you can copy them all at once to a new sheet, edit them in the new sheet as desired, then put them back in the original in one fell swoop. [Though it seems to me, instead of copying and having 2 identical copies, I would move them, edit them, then move them back, so no one would have the ability to edit 2 separate sets of data, and you plan on overwriting the original set anyway...]


Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts