Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Lounger
    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!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  3. #3
    Lounger
    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 (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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Resizing column selection (Excel 2002)

    Have you actually tried the code I suggested?

  5. #5
    Lounger
    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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

  8. #8
    Lounger
    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.
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Resizing column selection (Excel 2002)

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

  10. #10
    Lounger
    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
    Attached Files Attached Files

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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).

  12. #12
    Lounger
    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.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  14. #14
    Uranium Lounger
    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

  15. #15
    WS Lounge VIP sdckapr's Avatar
    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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •