Forum Discussion

tomc72's avatar
tomc72
Brass Contributor
Aug 10, 2020
Solved

Column width

I would like to know if I can use a formula to set a column width that depends on the width of the row number. For example: If the row number exceeds 999, then the row number column width increases i...
excel
Formulas and Functions
  • amit_bhola's avatar
    amit_bhola
    Aug 23, 2020

    tomc72 , as workaround, you can hide the headings by going to VIEW tab and unchecking the Headings under "Show"  as shown in below image.  Then on scrolling down, the columns won't shift to the right. But compromise is, you cannot see row numbers (1,2,3...)or column headers (A,B,C...).

     

     

NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Aug 10, 2020

tomc72 

Many roads lead to Rome ... with Excel there are many more roads to a solution.

With a simple formula:

=CELL("width",D1)

 

With VBA:

Sub Column_width()	 
 Sheets("Tabelle1").Activate	 
 Range("S:NH").EntireColumn.ColumnWidth = 15	 
End Sub

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

* Beware of scammers posting fake support numbers here.

  • tomc72's avatar
    tomc72
    Brass Contributor
    Aug 10, 2020

    I too am like Socrates!

    Not sure what you mean... I want the column width of Column A to reduce by the increased width of the Numbered Row Column when the Row number increases from 999 10 1000.

    Sorry, if I am not being clear.

    Tom

     

     

     

     

     

    NikolinoDE 

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Aug 10, 2020
    If it had to automatically adapt its content.

    Sub Column_width()
    Sheets("Tabelle1").Activate
    Columns("C:D").EntireColumn.AutoFit
    End Sub

Resources