10

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.

halfer
  • 19,824
  • 17
  • 99
  • 186
user2571510
  • 11,167
  • 39
  • 92
  • 138

3 Answers3

27

You can do it with AutoFit:

 Columns("A:B").EntireColumn.AutoFit
ZygD
  • 22,092
  • 39
  • 79
  • 102
InformatikBabo
  • 510
  • 2
  • 8
  • 19
  • Thanks ! Yes, that's what I just saw as well. :) How do I trigger the macro so that it runs after I pasted my data ? – user2571510 Jun 05 '14 at 11:25
  • Yes, no problem but what I mean is, is there a way to auto-run the macro after pasting using something like an onchange or onpaste event ? – user2571510 Jun 05 '14 at 11:27
  • 1
    @user2571510 You can make a button and always if you press the button the macro will start. Here is an example: http://www.mrexcel.com/tip068.shtml Is that what you need? :) – InformatikBabo Jun 05 '14 at 11:31
  • Thanks ! I know how to add a button but am looking for something to start the macro automatically when the table content changes, which is after I pasted my data. – user2571510 Jun 05 '14 at 11:34
  • 1
    Never mind. I figured it out - this can be done with the Worksheet_Change event. – user2571510 Jun 05 '14 at 11:37
  • Afterwards, you might want ActiveSheet.Range("A1").Select to deselect the area. – Dave Apr 08 '15 at 21:35
14

You can perform the following:

Columns("A").Autofit
Himanshu
  • 31,810
  • 31
  • 111
  • 133
Lê Tuấn Thành
  • 141
  • 1
  • 2
0

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.

Stephany
  • 1
  • Welcome to SO @Stephany. You are proposing a good approach and would help the questioner even more if you expand your answer with a workable example. – U3.1415926 Aug 24 '20 at 16:36