macro / autofit

[Guest]

Here is what I would like to be able to do. This is really a
formatting/presentation issue:

Lets say I have A1:C5 selected, which represent headdings and data for jan,
feb and march.

I want to do an auto fit for each column, so that the column widths are big
enough to fit the numbers. No problem, format-->column-->autofit and I'm
done.

But once that is done (for example), the width of column A is 15, column B
is 7 and column C is 13. When you print the spreadsheet, it will look much
better if all the column widths are the same. So, since column A is the
largest, I would want all three columns sized to 15.

So, essentially I would like a macro that looks at the selection, does and
autofit on the column widths and then makes all three columns the same width
as the largest column.

Ideas?

 

[Guest]

Jonathan,

Try this:

Sub temp()
Dim w As Integer
Dim i As Integer
w = 0
Columns("A:C").AutoFit
For i = 1 To 3
If Columns(i).ColumnWidth > w Then w = Columns(i).ColumnWidth
Next i
Columns("A:C").ColumnWidth = w
End Sub

Art

 

[Guest]

Can you change it so that it works, regardless of of the specific columns or
number of columns i have in my selection?

 

[Guest]

Jonathan, you're making this harder!

Okay, how about this:

Put the following macro in the sheet you're working on:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
x = Target.Columns.Address
End Sub

Now, put this in a separate module:

Public x As String

Sub DoColumns()
Dim w As Integer
Dim i As Integer
Dim r As Range
w = 0
Columns(x).AutoFit
For Each r In Range(x)
If r.ColumnWidth > w Then w = r.ColumnWidth
Next r
Columns(x).ColumnWidth = w
End Sub

This will only work on adjacent columns in that sheet. Select your columns.
Then, using Tools/Macro/Macros, run the DoColumns macro.

With any luck this will work.

Art

 

[Guest]

your not the first person to say that to me! If I could just find time to
learn VBA, I could drive myself crazy instead!

My intent was to insert this macro into my personal workbook, and assign it
to a button on my shortcut bar, so that I can use it as a utility for various
other spreadsheets.

Will this work for that purpose?

 

[Guest]

Jonathan,

I haven't tried it, but it should work if you take care of the details. The
way I've done this requires a macro in each sheet of the workbook. I don't
know what happens if you add more sheets -- I would think that they would not
be created with the macro.

There's probably a way to do it without a macro in each sheet.

Forgive me for not trying this all out myself.

If you don't know VBA, you're missing out on a lot (as you know). I'd
highly recommend that you spend some time with it -- you will really, really
like it.

Art