Resizing column selection (Excel 2002)

Topic

New Reply

Hi, I’m trying to format cell selection so that every selection I make ends up as the same total (columns) width, so I can then copy the selection into Powerpoint and it remains exactly the same size – without losing the fonts. I don’t want to resize once I’m in Powerpoint as the font size will not be true.

So far I have this code in Excel, which I would thought would resize any amount of columns, so that the overall width of the collection is the same each time:

Dim myCount as Single.

myCount = selection.columns.count

If myCount < 6 Then

With Selection
.Columns(1).ColumnWidth = 25
Do Until NextNo = myCount + 1
NextNo = NextNo + 1
.Columns(NextNo).ColumnWidth = 40 / myCount
Loop
End With

Else

With Selection
.Columns(1).ColumnWidth = 25
Do Until NextNo = myCount + 1
NextNo = NextNo + 1
.Columns(NextNo).ColumnWidth = 80 / myCount
Loop
End With
End If

Therefore, I would have thought if there were 6 columns or less, the overall width of the selection would be 40, with the column(1) remaining at 25.

And … if there are more than 6 columns, the overall width of the selection should be 80, with column(1) remaining at 25.

However, this is not working. For example, if I select 8 columns, Column(1) ends up with a width of 25 (correct), but columns (2 – 8) end up with a width of 10. Obviously, this totals 95, and I wanted an overall width of 80.

Is there another way I could be doing this? Hope the above makes sense!

Reply | Quote

Replies

If you want the first column to have a width of 25, that leaves only 40 – 25 = 15 or 80 – 25 = 55 for the remaining (myCount – 1) columns, so the width for those is 15 / (myCount – 1) or 55 / (myCount – 1) depending on whether myCount is less than 6 or not.

Reply | Quote

sorry, forgot to say I’ve started the column count at column 2, after already resizing column (1) to 25, by doing the folowing:

NextNo = 1

So, presumably the remainder of the columns (if over 6 in total) should total a width of 80, but an 8 column selection is currently setting column (1) to 25 (correct) and the remainder columns (2-8) to 10 each (totalling 70) (incorrect). This will vary slightly depending on how many columns are selected, but columns (2 – end) never add up to a total of 80 exactly.

Do I need to convert to another measurement?

However, in a selection of 8 columns, column(1) is 25 (correct) and columns (2-8) are 10 each.

Reply | Quote

Have you actually tried the code I suggested?

Reply | Quote

Yes, still doesn’t work.

Reply | Quote

Can you post a sample workbook with the macro you now have? The data can be dummy data.

Reply | Quote

Hi, I’ve attached a Powerpoint document, showing what I want the Excel selection to look like once pasted into either Powerpoint or Word. I shall paste special either as a workbook object or picture.

Basically, I need Column (2) to and column (last) to line up beneath each other, in all tables (selections) If there are only 2 columns, then column 2 needs to be aligned at the end, as shown in the attached file.

I know the Powerpoint slide shows the tables as a word object at the moment, this is just for demo purposes only to show you what I want the end result to be. All data will be created in Excel to start with.

I’m ok formatting the table with colours/borders etc. It’s just the alignment of the columns I can’t get right.

I haven’t a clue where to start now I’ve figured out just what I want. Sorry, real novice to VBA so any help much appreciated.

Thanks.

Reply | Quote

Could you please either try sdckapr’s code, or read my previous reply again?

Reply | Quote

Tried the other code suggested and it makes no difference, but this is because I now reaslise I need columns (2) and (end) to line up anyway, with all columns in between to be spaced proportionately, so the code as it stands won’t work in any case (even though the measurements are still not accurate enough).

Workbook attached, but could you check out my ppt file also previously attached, so you can see what I mean about the columns lining up please.

Thanks for your continued help on this

Reply | Quote

I’m confused. What do you mean by “line up”? Columns in Excel always line up. There is no way to make them not line up.

You haven’t bothered reading my and sdckapr’s replies carefully. Instead of 40 / (myCount – 1) and 80 / (myCount – 1), you should use 15 / (myCount – 1) and 55 / (myCount – 1).

Reply | Quote

I have bothered, I’ve tried the codes and neither gets the result I want.

If you look at the powerpoint slide I attached you will see that columns (2) (in ALL tables) always line up, and the last column always lines up. The columns in between are proportionately spaced.

If I use the code suggested (or indeed mine at the moment), then column (2) in the first table shown does not line up with column (2) in the 2nd table shown.

Sorry if I haven’t explained myself very well, but I can assure you, with due respect, that I have bothered to read your replies and try out the code.

Reply | Quote

But the macro in your workbook still used the old, incorrect code.

In the second table, insert empty columns so that both tables contain the same number of columns.

Reply | Quote

It might also be helpful if the data on your posted Excel workbook had some relationship to the data in your Powerpoint sample – they do not appear to have any relationship at all.

Reply | Quote

Your request will have the first table twice as wide as the other 2 in your exapmple since it has 6 columns. Only tables with <6 columns will line up together (total 40). The ones with 6 or more will total 80.

Steve

Reply | Quote

Try the code below I think this does what you want
Regards Tom Duthie

Dim MyCount As Single
Dim NextNo As Single
Dim ColsToSize As Single
Dim r As Single

Selection.Columns(1).ColumnWidth = 25
NextNo = 2

MyCount = Selection.Columns.Count
ColsToSize = MyCount – 1

If MyCount < 6 Then
For r = NextNo To MyCount
Selection.Columns(NextNo).ColumnWidth = 15 / ColsToSize
NextNo = NextNo + 1
Next r

Else

For r = NextNo To MyCount
Selection.Columns(NextNo).ColumnWidth = 55 / ColsToSize
NextNo = NextNo + 1
Next r

End If

Reply | Quote

Does this do what you want?

  Dim myCount As Single
  Dim dWidth As Double
  myCount = Selection.Columns.Count
  dWidth = 55
  If myCount  1 Then
      .Range(.Columns(2), .Columns(myCount)) _
        .ColumnWidth = dWidth / (myCount - 1)
    End If
  End With

Steve

Reply | Quote