Excel / VBA: auto-adjust column width after pasting data

10
votes

I have a blank Excel table saved as a template into which I copy data from the clipboard (originating from a website).

Is there a way using VBA to auto-adjust the width of each column to its content after the data (text) has been pasted in ?

Edit: The data I paste will always be inserted starting from A1 and will always fill the same number of columns.

3
excelvbacolumn-width
Yes you can. Try recording a macro whilst doing it which will give you an example. - Gareth
Thanks. Just figured it out as well. :) How do I trigger the macro so that it runs after I pasted my data ? - user2571510

3 Answers

24
votes

You can do it with AutoFit:

 Columns("A:B").EntireColumn.AutoFit
14
votes

You can perform the following:

Columns("A").Autofit
0
votes

You can do it with a worksheet change event, but if your worksheet is changed in any way after the paste, it runs again. Not an issue unless you're trying to hide columns to focus on a subset of data because they will autofit themselves constantly.

I recommend using a button to paste the data and including the column width setting as part of the paste command.