Auto fit column width automatically in Excel based on the user input

Question

In my spreadsheet I need to fit the column width automatically in Excel. If a user is entering a longest word that column should increase based on the word/integer automatically. I don’t want my user to increase the width of a column. For that I got a formula from Internet which is a method to make all columns, in all worksheets in the active workbook, auto fit if data is entered that exceeds the current column width.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) 

Dim actCol, curCol 

actCol = Target.Column 

If actCol > 52 Then 
    curCol = Chr(Int((actCol - 1) / 52) + 64) & _ 
    Chr(Int((actCol - 27) / 26) + 64) & _ 
    Chr(Int((actCol - 27) Mod 26) + 65) 
ElseIf actCol > 26 Then 
    curCol = Chr(Int((actCol - 1) / 26) + 64) & _ 
    Chr(Int((actCol - 1) Mod 26) + 65) 
Else 
    curCol = Chr(actCol + 64) 
End If 

Columns(curCol & ":" & curCol).AutoFit 

End Sub

With your workbook open, press ALT + F11 (Function Key F11)

Double click 'THIS WORKBOOK' in the Microsoft Excel VBA Project objects in the upper left quadrant.

Paste the event handler into the white editing area to the right (right click inside the area and 'Paste').

Close the VBE (red button w/white 'x' --- top right).

Save the workbook.

I used this formula and it is working partially correct. But still I’m facing issue when the inputs given are showing as #### if the integer exceeds more than 4 character. How to fix this issue?

Screenshot:

enter image description here

Note: Since that file contains some confidential information I can't share the file here.

Fratyx · Accepted Answer · 2015-01-08 07:20:31Z

1

After trying to understand the piece of code you found in internet I think it is a very complicated way to do that:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Target.EntireColumn.AutoFit
End Sub

That works for me. Maybe there occured an error in your code in trying to determine the column letters so could you try this one?

answered Jan 8, 2015 at 7:20

Fratyx

5,8271 gold badge16 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sathiya Kumar V M Over a year ago

Ok. I will try this code and let you know. Thanks for the answer.

Fratyx Over a year ago

Could you try to give more information about the behavior. Does it work in every case with not more than 4 digits? Does it shrink and grow? What happens with letters? You could put the VBA code in a newly created Excel file and post it if it also doesn't work in that one.

Collectives™ on Stack Overflow

Auto fit column width automatically in Excel based on the user input

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related