Autofit Method of Range Class Failed (Run Time Error 1004)

Question

This is just part of my code. the value from the textbox here already gets copied to the specific cell in the Bank Certification worksheet. I need to make sure that cell C5 is specifically fitted regardless of the length of the text i inputted in the textbox. I tried interchanging range with cells to no avail. This problem seems so simple but I don't know why it doesn't work...

Dim counterparty As String  
counterparty = Sheet1.txt1.Text

Range("C5").Value = counterparty 

Sheets("Bank Certification").Select

Range("C5").Select 

Selection.AutoFit

Could you try refitting the entire Column instead of the particular cell. `Columns("C:C").AutoFit` — izzymo, Feb 27 '15 at 07:04

score 11 · Accepted Answer · answered Feb 27 '15 at 07:08

11

Try

Dim counterparty As String
counterparty = Sheet1.txt1.Text

Range("C5").Value = counterparty

Sheets("Bank Certification").Select

Columns("C:C").Autofit

answered Feb 27 '15 at 07:08

izzymo

916
1
10
14

thanks.. this kinda works.. the error doesnt show... but right now, I'm thinking, is autofit applicable only to a specific cell? like C5! Anyway, thanks for your answer! – Benedict Solpico Feb 27 '15 at 07:18
2

Nope Autofit works only on Columns as one cell in a column cannot have a different width than the rest of the column. If that is what you require you can consider merging cells. – izzymo Feb 27 '15 at 07:19
Also, you can get rid of the `Sheets().select` line, as it doesn't buy you anything. If you need to be more specific: `Worksheets("Bank Certification").Columns("C:C").Autofit` should do it. – FreeMan Feb 27 '15 at 16:19

score 5 · Answer 2 · answered Sep 07 '17 at 22:20

Other answers correctly state that AutoFit must be used with a column, not just a cell. However, there are some nuances to using AutoFit that I didn't understand until I started experimenting.

Either of the first two statements below will use all values in column C to AutoFit the width of the column. That means if there is a value in some other cell in column C (for example C10) that is wider than the value in C5, it will fit the column to the widest cell in column C (for example C10).

Range("C5").EntireColumn.AutoFit     ' Will fit to widest cell in column
Range("C:C").AutoFit                 ' Will fit to widest cell in column

If you want to just fit the column on 1 cell (or a certain range of cells, but not the whole column or columns), use a statement like this:

Range("C5").Columns.AutoFit          ' Will fit column C to width of cell C5

And of course, it's always better form to write code like this when you can:

Fully qualify the range unless you're absolutely sure you'll only be working with one worksheet
Use Named Ranges or Range objects.

For example:

Workbooks("MyWorkbook.xlsm").Sheets("Sheet1").Range("MyData").Columns.AutoFit
'  or
Set AutoFitRange = Workbooks("MyWorkbook.xlsm").Sheets("Sheet1").Range("C5")
AutoFitRange.Columns.AutoFit

Autofit Method of Range Class Failed (Run Time Error 1004)

2 Answers2