SOLVED

Contributor

# 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 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

# Betreff: Column width

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.

# Betreff: Column width

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

# Betreff: Column width

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

# RE: Column width

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

# RE: Column width

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.

# Betreff: Column width

here a examble

column B, Write whatever you want the column will change automatically.

Nikolino
Ich weiss dass ich nichts weiss (Sokrates)

# Betreff: Column width

@Nikolino, 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?

# Betreff: Column width

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.

Thx

Nikolino

# Betreff: Column width

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

# Betreff: Column width

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

# Betreff: Column width

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

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

scroll down to this topic :-

# RE: Column width

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.

# RE: Column width

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

# Betreff: Column width

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

as per Amit

Thank you

# Betreff: Column width

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

best response confirmed by tomc72 (Contributor)
Solution

# Betreff: Column width

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

# Betreff: Column width

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

Tom W