Autofit Rows Using Macro

[dvent]

Hi Guys,

I'm trying to Autofit a number of Rows within a macro.

I need to autofit Rows 8 to 80 as the content of these rows will have been updated by lines earlier in the macro. Some rows will now contain more than 1 line of text whereas others will now be blank.

I initially tried to use the code:

Rows("8:80").EntireRow.AutoFit

but this didn't work properly. Some rows were not as expanded as they needed to be.

I then tried to write it like this:

Sub Macro2()
    Rows("8:8").EntireRow.AutoFit
    Rows("9:9").EntireRow.AutoFit
    Rows("10:10").EntireRow.AutoFit
    Rows("11:11").EntireRow.AutoFit
    Rows("12:12").EntireRow.AutoFit
End Sub

however this would take me ages to write out and would also be a pain to edit if i needed to change the rows etc.

Can anyone show me a solution to this problem and suggest why my first code did not work?

Thanks
dvent

[royUK]

Try this

    Rows("8:80").Rows.AutoFit

[dvent]

Try this

    Rows("8:80").Rows.AutoFit

With this I get:

Run Time Error '1004'
Method 'Rows' of object '_Global' failed.

[royUK]

Remove the second Rows

 Rows("8:80").AutoFit

[dvent]

cheers Roy!

[winoverme]

i have the same problem. my question is where should i put the code?

[HaHoBe]

Hi, winoverme,

welcome to the Forum.

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.


Regarding your question:

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose Tools | Macro | Macros
Select a macro in the list, and click the Run button

Ciao,
Holger