Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy Excel column widths via object

    I suspect the answer is no, but is there a way to copy/paste column widths without going via the clipboard?

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    Copying and pasting always runs through the clipboard. However, getting values and setting other values usually can be done in code. What exactly are you trying to do?

  4. #3
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Well, one can do a copy via an object (i.e. not via the clipboard) using:

    range_object.Copy Destination:=another_range_object

    As well as copying the data, I also wish to copy the column widths to the destination range.

  5. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    Quote Originally Posted by Paul Lautman View Post
    Well, one can do a copy via an object (i.e. not via the clipboard) using:

    range_object.Copy Destination:=another_range_object
    You're right. I've never understood why copy/paste in Excel works differently within the application (more like "duplicate at destination") than when you copy from Excel and paste to Word. But I digress.

    You can add an additional line of code to set the column width:

    Code:
    range_object.Copy Destination:=another_range_object
    another_range_object.ColumnWidth = range_object.ColumnWidth
    Would that work for your actual procedure? It assumes a single column, which may be a very bad assumption.

    Edit: This should work with multiple columns assuming the range is a rectangle (other scenarios might be a mess).

    Code:
    range_object.Copy Destination:=another_range_object
    Dim intCount As Integer
    For intCount = 1 To range_object.Columns.Count
        another_range_object.Columns(intCount).ColumnWidth = range_object.Columns(intCount).ColumnWidth
    Next
    Last edited by jscher2000; 2011-11-23 at 11:41. Reason: Addressed multiple columns issue.

  6. #5
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'll try that.I already use another_range_object.value = range_object.value which does the equivalent of Paste Special Values on a whole range. It's just a pity that there is no such shorthsnd for column widths.

  7. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    Quote Originally Posted by Paul Lautman View Post
    It's just a pity that there is no such shorthsnd for column widths.
    I wouldn't rule it out: I only dabble in Excel VBA a few times a decade, and am far from an expert on Excel's object model (or on Excel).

  8. #7
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 28 Times in 27 Posts
    There is a Paste Special > Column Widths option in the user interface.
    Recording a macro of applying that, results in (just including the key line here):

    Code:
        Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
    (substitute your range instead of Selection)

    - would that work?

    Gary

  9. #8
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts
    No because it pastes from the clipboard which violates the "without going via the clipboard" in the OP.

  10. #9
    New Lounger
    Join Date
    Sep 2011
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Just do something like this:

    Columns(1).ColumnWidth = Columns(3).ColumnWidth

    Bob Flanagan
    Last edited by Medico; 2012-10-09 at 16:04.

  11. #10
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 28 Times in 27 Posts
    Quote Originally Posted by Paul Lautman View Post
    No because it pastes from the clipboard which violates the "without going via the clipboard" in the OP.
    Fair enough - just thought I'd throw that out there. Also to be fair, the OP's original method:

    Code:
    range_object.Copy Destination:=another_range_object
    does use the clipboard (as far as I can tell). From Excel's VBA Help topic:

    Destination - Optional - Variant - Specifies the new range to which the specified range will be copied. If this argument is omitted, Microsoft Excel copies the range to the Clipboard.
    - which doesn't explicitly say that the clipboard is used when the argument is included, but a simple test seems to indicate it does:
    Copy some ordinary text, and paste it anywhere (such as in a worksheet, or in a Word document). Then run the sample code from Excel's Help topic:

    Code:
    Worksheets("Sheet1").Range("A1:D4").Copy  destination:=Worksheets("Sheet2").Range("E5")
    - and then try to repeat pasting the previously-copied text - nothing happens. So it seems like the Range Copy method is using (and clearing) the clipboard in some way.

    In any case, Bob Flanagan's suggestion seems best.

    Gary

  12. #11
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    Quote Originally Posted by Gary Frieder View Post
    In any case, Bob Flanagan's suggestion seems best.
    I think my code implemented the suggestion even before it was made. ;-)

  13. #12
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Bob's one doesn't work on a set of columns, only on a single column and so doesn't work.

  14. #13
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 28 Times in 27 Posts
    jscher's code from 11/23 post (which I skipped over - sorry!) should address both issues.

    Gary

  15. #14
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I think I was aware of that. Indeed immediately following that post I posted:

    "I'll try that.I already use another_range_object.value = range_object.value which does the equivalent of Paste Special Values on a whole range. It's just a pity that there is no such shorthsnd for column widths."

Posting Permissions

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