Trying to use VBA

%3CLINGO-SUB%20id%3D%22lingo-sub-1738371%22%20slang%3D%22en-US%22%3ETrying%20to%20use%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1738371%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20using%20excel%20for%20a%20winemaking%20log.%20I%20like%20my%20column%20width%20and%20row%20height%20to%20be%20autofit%20using%20VBA%20coding.%20The%20issue%20is%2C%20I%20have%20used%20at%20least%2015%20different%20ways%20of%20coding%2C%20that%20I've%20found%20online%20or%20done%20myself%2C%20and%20the%20corrections%20just%20aren't%20happening.%20Any%20thoughts%20or%20help%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1738371%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1738952%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20use%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1738952%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F735498%22%20target%3D%22_blank%22%3E%40FrameofLightDesigner%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EProblem%3A%3C%2FSTRONG%3E%20Excel%20cannot%20autofit%20merged%20cells.%20Column%20A%20has%20no%20cells%20that%20are%20not%20merged%20so%20Excel%20has%20no%20idea%20what%20size%20A%20should%20be.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EFix%3C%2FSTRONG%3E%3A%20Row%20122%20has%20no%20merged%20cells%20and%20no%20content.%20Place%20entries%20in%20row%20122%20for%20columns%20A%3AN%20that%20represent%20the%20column%20width%20you%20want%20for%20each%20column%20then%20use%20this%20VBA.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3ESub%20Auto_Size()%3CBR%20%2F%3E%20%20%20%20%5BA%3AN%5D.EntireColumn.AutoFit%3CBR%20%2F%3EEnd%20sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I'm using excel for a winemaking log. I like my column width and row height to be autofit using VBA coding. The issue is, I have used at least 15 different ways of coding, that I've found online or done myself, and the corrections just aren't happening. Any thoughts or help?

1 Reply

@FrameofLightDesigner ,

 

Problem: Excel cannot autofit merged cells. Column A has no cells that are not merged so Excel has no idea what size A should be.

 

Fix: Row 122 has no merged cells and no content. Place entries in row 122 for columns A:N that represent the column width you want for each column then use this VBA.

 

Sub Auto_Size()
[A:N].EntireColumn.AutoFit
End sub

 

If you want to set all columns to the same size use this instead.

Sub Auto_Size()
[A:N].ColumnWidth = 7
End sub