SOLVED

Column width

Brass Contributor

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 in order to accommodate the fourth digit. I need column A to reduce in size when that occurs, so that the displayed data does not shift to the right. Is it possible to control this? If so, how? I am guessing that the formula "If Row Number > 999 then Column 1 width = ?" would do it, but is is possible, please?

 

I would be grateful for suggestions, please.

 

Tom W

18 Replies

@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.

If it had to automatically adapt its content.

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

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 

another way Then just take over the widths from the given columns. Private Sub Worksheet_Activate() Sheets("Tabelle2").Columns("A").ColumnWidth = Sheets("Tabelle1").Range("A1") Sheets("Tabelle2").Columns("B").ColumnWidth = Sheets("Tabelle1").Range("B1") End Sub

Thank you so much for your help, but something may be being lost intranslation from english to german and vice versa. I nedd to adjust the width of Column A dependent on the width of the Row Number column.

@NikolinoDE 

@tomc72 

 

here a examble

column B, Write whatever you want the column will change automatically.
I hope that I could help you with this.

 

Nikolino
Ich weiss dass ich nichts weiss (Sokrates)

@NikolinoDE, I think @tomc72 is not aware of using macro subs and needs guidance on how to put your VBA code(s) in any of his workbook.. could you pls. guide him on that?

@tomc72 

Open an existing workbook or create a new, empty one.

The fastest way to get to the Microsoft Visual Basic Editor is via the shortcut

Press the key combination ALT F11.
Excel now shows the VBA editor or macro editor.
Select the MODULE command from the INSERT menu.
Excel displays a window in which you can type or copy the program code.
When you are ready. Close the VBA editor with the command FILE - CLOSE AND RETURN TO MICROSOFT EXCEL or the key combination ALT Q.

 

Some more infos:

https://support.microsoft.com/de-de/help/219905/how-to-dynamically-add-and-run-a-vba-macro-from-visu...

 

Thx

Nikolino

@NikolinoDE 

Many thanks Nicolino. Are you able to tell me the code that will keep the row-number column at a fixed (wider than the default) width, please?

 

Tom

@tomc72 

Wie ich Ihnen schon gesendet habe, wenn sie feste Spaltenbreite möchten, sollten Sie den ersten VBA Code in Ihr Makro einschreiben, wobei Sie die breite je nach bereich ändern.

 

Sub Column_width()	 
 Sheets("Tabelle1").Activate	 
 Range("A1:A999").EntireColumn.ColumnWidth = 15
Range("A1000:A4500").EntireColumn.ColumnWidth = 25 End Sub

 Weis auch nicht wie ich Ihnen weiterhelfen kann, eine Datei (ohne sensible daten) wäre von vorteil.

 

Danke,

Nikolino

 

@tomc72 , also if you want that in all of your workbooks, the column width remains a fixed default value of your choice, then MS suggests setting up a template.

 

On this link

 

https://support.microsoft.com/en-us/office/customize-how-excel-starts-6509b9af-2cc8-4fb6-9ef5-cf5f1d...

 

scroll down to this topic :-

Automatically open a workbook template or worksheet template when you create a new workbook or worksheet

I have a checkbook register in Excel. What I would like to do is to format the date column, so when I enter a date it will appear in the correct order. For example, 7/10/20 will appear after 7/5/20 but before 8/10/20.

@silverfox251 , hi, yours is a new problem, so it is suggested to start a new discussion thread for the same.

Go to Start a New Conversation as shown in image below. Also it is suggested to attach your example excel file when you post the problem. BTW, it looks like you just have to sort the register table by date, still people here would be better able to understand your issue if you attach your file (delete any confidential info of yours before uploading here)

 

amit_bhola_0-1598114950054.png

 

@amit_bhola 

Thanks Amit.

I know how to adjust all the lettered columns, it´s the column that holds the built in row-numbering that I want to control i.e. the left of column A.

 

Tom W

@silverfox251 

as per Amit

Thank you

@tomc72 , i don't think that is possible to control in Excel.

If you want, you may like to pose this problem as a new thread again so that it gets fresh attention of other experts here. Take care to use a more descriptive heading and describe your problem with help of an image like below, because frankly, it is a unique request and difficult to understand with only words. The non-helpful replies to you till now are signalling this too.

 

amit_bhola_1-1598116110794.png

 

 

best response confirmed by tomc72 (Brass Contributor)
Solution

@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...).

 

amit_bhola_0-1598180497864.png

 

@amit_bhola 

 

Thanks Amit... that´ll do nicely for me! Perfectly proves the KISS principal, eh!

 

Tom W

1 best response

Accepted Solutions
best response confirmed by tomc72 (Brass Contributor)
Solution

@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...).

 

amit_bhola_0-1598180497864.png

 

View solution in original post