Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    copying hidden columns (2003)

    I have a sheet with hidden columns.
    I've noticed that if you select all of a list with hidden columns and copy it to a new spreadsheet, you can then access all the hidden columns.

    Is there any way round this?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: copying hidden columns (2003)

    Most ways can be gotten around if the user chooses to. Depending on your "needs" here are a couple of suggestions:

    You can prevent copying form the sheet to some degree with a macro

    You can put the hidden columns on another sheet and make that sheet "very hidden"

    If you elaborated on your goals we may be able to offer additional suggestions.

    Steve

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: copying hidden columns (2003)

    I don't know the context of your Question, but if you select the list (CTRL + Asterisk) and then choose, EDIT | Go To |and select Special, you can choose Visible Cells only and copy this to another sheet. This will exclude hidden columns!
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: copying hidden columns (2003)

    If you want the hidden data to remain hidden, put them in a separate worksheet and make that "very hidden":
    - Activate the Visual Basic Editor (Alt+F11)
    - If necessary, expand the workbook and Microsoft Excel Objects in the Project Explorer on the left hand side.
    - Select the node corresponding to the worksheet you want to hide.
    - Activate the Properties window (F4)
    - Set the Visible property to 2 - xlVeryHidden.
    The sheet can only be made visible again here, or using VBA code. It will not be listed in Format | Sheet | Unhide...

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copying hidden columns (2003)

    If you are trying to keep others from seeing those columns, the best way is to put those columns on another sheet and make that sheet xlVeryHidden.
    Legare Coleman

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: copying hidden columns (2003)

    The same way you would copy any range.

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: copying hidden columns (2003)

    <P ID="edit" class=small>(Edited by Rudi on 24-Dec-07 00:16. Added more content to my query!)</P>Is there a way to have a macro copy hidden columns data to a new sheet? How could one program this?

    Hans: In <post#=485,599>post 485,599</post#> you showed me how to select duplicates and invert them by changing the comparison symbol. This worked well in the appropriate post, but here I am thinking of a genuine inverting on the selection.

    VBA gives a visible cells argument: Range("A1").CurrentRegion.SpecialCells(xlCellTypeV isible).Select
    Is there a way to take the visible cells selection, invert it to select invisible cells and then copy that selection to the relevant sheet?
    TX
    Regards,
    Rudi

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: copying hidden columns (2003)

    The set of non-visible cells could consist of multiple areas. How would you want to copy them?

    BTW, is this for a real purpose, or just curiosity?

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: copying hidden columns (2003)

    Hi,

    OK, I will be straight up! The scenario I set up is not real, and I agree that you cannot copy multiple areas <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>, but I did not think of that when I raised the question. It is not for any real purpose now, but I have had the need for it several times in the past hence my persistence to find out how it can be done. I know that ASAP utilities provides such an inverting of selected cells and I am interested to know how to code it as such.

    Apologies for the second thread as I did not expect you to add further to this one.
    Regards,
    Rudi

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: copying hidden columns (2003)

    > as I did not expect you to add further to this one
    So why did you ask a question in this thread? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

Posting Permissions

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