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?

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

"Jonathan Cooper" wrote:

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?

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

"Art" wrote:

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

"Jonathan Cooper" wrote:

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?

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


"Jonathan Cooper" wrote:

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

"Art" wrote:

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

"Jonathan Cooper" wrote:

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?

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?

"Art" wrote:

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


"Jonathan Cooper" wrote:

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

"Art" wrote:

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

"Jonathan Cooper" wrote:

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?

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

"Jonathan Cooper" wrote:

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?

"Art" wrote:

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


"Jonathan Cooper" wrote:

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

"Art" wrote:

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

"Jonathan Cooper" wrote:

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?