Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Autofit Row in VBA (2003)

    Hi All,

    I have written a macro to copy data from one sheet to another. The macro works fine except for one small detail. Some of the copied cells may have be more than 1 line of text. I know in the Excel UI that I'd use the Autofit function to get the row to resize. The VBA I'm using is as follows

    ActiveCell.Offset(0, 1).Select
    ActiveCell.Formula = "='Raw Data'!B" & irow
    'Rows("19:19").EntireRow.AutoFit
    Rows("""" & ActiveCell.Row & ":" & ActiveCell.Row & """").EntireRow.AutoFit

    The first 2 lines are just moving the active cell in the sheet I'm copying TO and putting together a formula for the data to be copied. No problem.

    The commented row is something that I recorded to find the VBA for Autofit.

    The last row is my attempt to do the same with my row (I tried a few other combinations also).

    When the macro ends, the row is 1 line of text high, even though the row/cell contains several lines of text.

    If I close the workbook and re-open it, the row in question is now of proper height.

    How can I get the row to Autofit as needed without having to close and re-open? (I tried putting an Application.Screenupdating = true statement in although I didn't think this would help. I was right.)

    TIA

    Fred

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

    Re: Autofit Row in VBA (2003)

    You're complicating things needlessly, you can replace the last line with

    ActiveCell.EntireRow.AutoFit

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Autofit Row in VBA (2003)

    Hi Hans,

    I only complicated it because that's what the macro recorder showed.

    I tried your suggestion and it worked fine - of course.

    But I'm just curious as to why my code line worked in a delayed fashion.

    Thanks.

    Fred

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

    Re: Autofit Row in VBA (2003)

    Sorry, I don't know.

Posting Permissions

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