Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Column Widths and Row Heights (Excel 2K)

    Hi,

    In pasting data into Excel I often find the Column Widths and Row Heights need adjusting after this task.

    Can anyone suggest code for a simple macro which will allow me to work in the following way:

    1. I select the columns for which the width is to be adjusted.
    2. Run the macro which asks me to nominate a column which has the width I want to use (I might enter G for col G), and sets the widths identical to that column.

    Column widths and row heights do not have a facility in Excel to be directly copied like formatting, formulas, values, etc. You have to know a number to apply, rather than just say - like col G!

    Thanks in anticipation,

    Peter Moran
    Two heads are always better than one!

  2. #2
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Brussels, Brussel, Belgium
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Column Widths and Row Heights (Excel 2K)

    hi Peter,

    Sub AdjustColumnWidths()
    Dim SelectedColumns As Range
    Dim CurCol As Range
    Dim SelectedColumnWidth As Single
    Set SelectedColumns = Selection
    SelectedColumnWidth = _
    Application.InputBox _
    ( _
    prompt:= _
    "Select a cell in the column of which you want to use" & vbCrLf & _
    "the width to apply to the previously selected columns", _
    Title:="Apply Columnwidth - Select Column", Type:=8 _
    ).ColumnWidth
    For Each CurCol In SelectedColumns.Columns
    CurCol.ColumnWidth = SelectedColumnWidth
    Next
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Column Widths and Row Heights (Excel 2K)

    Hi Pieter,

    Just what the doctor ordered! Great stuff!

    Many Thanks!

    Peter Moran
    Two heads are always better than one

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Column Widths and Row Heights (Excel 2K)

    But Excel2000 does have a copy column widths feature.

    Select source range for copy, then at destination choose
    Edit-PasteSpecial-Column Widths

    OK, it doesn't have one for Rows though.
    zeddy

Posting Permissions

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