[mlgrier3]

Is there a Macro that can be written so that every time Excel is opened, no matter what format it is; .xls, .csv, etc., that will autofit the coulmns? Thank you.

[Advertisements]

that would indeed be a handy macro..the problem is (and i may be wrong) that macros affect the document not the program...they are saved within the document...and therefore i dont think a macro would help in this situation...as you would have to add and run the macro to each document as you open it.

[dsenette]

that would indeed be a handy macro..the problem is (and i may be wrong) that macros affect the document not the program...they are saved within the document...and therefore i dont think a macro would help in this situation...as you would have to add and run the macro to each document as you open it.

[mlgrier3]

So would there be a way to specify the default column width in Excel, changing it from 8.43 to say 5?

[dsenette]

if there is...i've never been able to find it..doesn't mean it doesn't exist...but i have yet to find a way to do it

[Octagonal]

Hi

It is possible to do this in either all or only selected workbooks. I would assume that you would require the column width to be "autosized" size in specified workbooks only. This can be achieved by adding the following code into the ThisWorkbook object using the VBA editor. This will only affect the workbook that you insert the code into.

Private Sub Workbook_Open()

Dim i As Integer

For i = 1 To Sheets.Count
Sheets(i).Columns("A:AZ").EntireColumn.AutoFit
Next

End Sub

In the above example all sheets in the workbook are affected and I have selected the first 52 columns of each sheet to be autosized. If you require more help on this (eg. set column width or only selected sheets) let me know and I shall help, if you are confident enough have a play with the code and set it to suit your needs.

HTH
Octagonal

Edited by Octagonal, 20 January 2006 - 07:30 PM.

[mlgrier3]

Tried the code but it won't save in the file I am using. It is .csv file, and they won't hold the macros, is there a way that I can specify the macro to run from personal.xls, or from somewhere else, so that every time I open this file it will work?

[Octagonal]

Can you convert the file to Excel, or is it a dynamic file? if the file is static you can convert csv using the Text to columns feature.

Edited by Octagonal, 21 January 2006 - 12:55 AM.

[mlgrier3]

The file is automatically built from another program, so I'm pretty sure I can get it to build almost any kind of file that excel will open. I did get it to generate an .xls file but had all of the values in the first field with the commas between them. So that didn't seem to wrok for me as I need them seperated.

[Octagonal]

OK. Thats what I need to know. With all the data in the first column you can use the Text to column feature as I mentioned above. Try this.

Open a blank worksheet and in cell A1 type in something like 11,25,you,26,39,me (something similar to your data.
1. Then highlight the cell then select the Data menu and select Text to columns.
2. A dialog box will open where you set how the data is to be seperated. In this case choose the Delimited option and hit the Next button.
3. Uncheck the Tab box and check the Comma box the click the Next button.
4. Set the column data types then click Finish. You will now find that the data is in seperate columns now.

To do this with several rows of data, simply highlight the entire column that you wish to seperate when selecting the data that you wish to use in the first step. If you feel comfortable doing this, then try it on a copy of your spreadsheet first.

Let me know how you get on.

Octagonal