Aug 10 2020 05:56 AM
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
Aug 10 2020 06:06 AM
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.
Aug 10 2020 06:10 AM
Aug 10 2020 06:13 AM
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
Aug 10 2020 06:16 AM
Aug 10 2020 06:24 AM
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.
Aug 10 2020 06:26 AM
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)
Aug 10 2020 06:54 AM
Aug 10 2020 07:11 AM
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:
Thx
Nikolino
Aug 20 2020 02:19 AM
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
Aug 20 2020 03:32 AM
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
Aug 22 2020 12:59 AM - edited Aug 22 2020 01:01 AM
@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
scroll down to this topic :-
Aug 22 2020 08:47 AM
Aug 22 2020 09:49 AM
@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)
Aug 22 2020 09:51 AM
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
Aug 22 2020 10:09 AM
@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.
Aug 23 2020 04:03 AM
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...).
Aug 25 2020 01:04 AM
Aug 23 2020 04:03 AM
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...).