Forum Discussion
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
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...).
18 Replies
- 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)
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
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 , 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-cf5f1d292c19
scroll down to this topic :-
Automatically open a workbook template or worksheet template when you create a new workbook or worksheet
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?
- 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.
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 SubI 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.
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
- If it had to automatically adapt its content.
Sub Column_width()
Sheets("Tabelle1").Activate
Columns("C:D").EntireColumn.AutoFit
End Sub
