Results 1 to 15 of 16

20070124, 12:21 #1
 Join Date
 Feb 2004
 Posts
 25
 Thanks
 0
 Thanked 0 Times in 0 Posts
Resizing column selection (Excel 2002)
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!

20070124, 12:46 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Resizing column selection (Excel 2002)
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.

20070124, 13:02 #3
 Join Date
 Feb 2004
 Posts
 25
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Resizing column selection (Excel 2002)
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 (28) 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 (28) are 10 each.

20070124, 13:05 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Resizing column selection (Excel 2002)
Have you actually tried the code I suggested?

20070124, 13:33 #5
 Join Date
 Feb 2004
 Posts
 25
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Resizing column selection (Excel 2002)
Yes, still doesn't work.

20070124, 13:46 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Resizing column selection (Excel 2002)
Can you post a sample workbook with the macro you now have? The data can be dummy data.

20070124, 14:06 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Resizing column selection (Excel 2002)
Does this do what you want?
<pre> Dim myCount As Single
Dim dWidth As Double
myCount = Selection.Columns.Count
dWidth = 55
If myCount < 6 Then dWidth = 15
With Selection
.Columns(1).ColumnWidth = 25
If myCount > 1 Then
.Range(.Columns(2), .Columns(myCount)) _
.ColumnWidth = dWidth / (myCount  1)
End If
End With</pre>
Steve

20070124, 14:24 #8
 Join Date
 Feb 2004
 Posts
 25
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Resizing column selection (Excel 2002)
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.

20070124, 14:25 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Resizing column selection (Excel 2002)
Could you please either try sdckapr's code, or read my previous reply again?

20070124, 14:34 #10
 Join Date
 Feb 2004
 Posts
 25
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Resizing column selection (Excel 2002)
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

20070124, 14:43 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Resizing column selection (Excel 2002)
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).

20070124, 14:54 #12
 Join Date
 Feb 2004
 Posts
 25
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Resizing column selection (Excel 2002)
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.

20070124, 15:14 #13
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Resizing column selection (Excel 2002)
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.

20070124, 17:07 #14
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Resizing column selection (Excel 2002)
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.
John ... I float in liquid gardens
UTC 7ąDS

20070124, 17:16 #15
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Resizing column selection (Excel 2002)
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