Copy Excel column widths via object

Topic

New Reply

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

Reply | Quote

Replies

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?

Reply | Quote

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.

Reply | Quote

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).

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[/code]

Reply | Quote

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.

Reply | Quote

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).

Reply | Quote

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

Reply | Quote

No because it pastes from the clipboard which violates the “without going via the clipboard” in the OP.

Reply | Quote

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

Reply | Quote

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

I think my code implemented the suggestion even before it was made. 😉

Reply | Quote

Just do something like this:

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

Bob Flanagan

Reply | Quote

Bob’s one doesn’t work on a set of columns, only on a single column and so doesn’t work.

Reply | Quote

jscher’s code from 11/23 post (which I skipped over – sorry!) should address both issues.

Gary

Reply | Quote

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.”

Reply | Quote