Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Excel Macros


  • Please log in to reply

#1
mlgrier3

mlgrier3

    Member

  • Member
  • PipPip
  • 21 posts
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.
  • 0

Advertisements


#2
dsenette

dsenette

    Je suis Napoléon!

  • Administrator
  • 26,019 posts
  • MVP
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.
  • 0

#3
mlgrier3

mlgrier3

    Member

  • Topic Starter
  • Member
  • PipPip
  • 21 posts
So would there be a way to specify the default column width in Excel, changing it from 8.43 to say 5?
  • 0

#4
dsenette

dsenette

    Je suis Napoléon!

  • Administrator
  • 26,019 posts
  • MVP
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
  • 0

#5
Octagonal

Octagonal

    Member 2k

  • Member
  • PipPipPipPipPip
  • 2,528 posts
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.

  • 0

#6
mlgrier3

mlgrier3

    Member

  • Topic Starter
  • Member
  • PipPip
  • 21 posts
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?
  • 0

#7
Octagonal

Octagonal

    Member 2k

  • Member
  • PipPipPipPipPip
  • 2,528 posts
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.

  • 0

#8
mlgrier3

mlgrier3

    Member

  • Topic Starter
  • Member
  • PipPip
  • 21 posts
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.
  • 0

#9
Octagonal

Octagonal

    Member 2k

  • Member
  • PipPipPipPipPip
  • 2,528 posts
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
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP